Join Multiple Data Sheets into a report to control attachments visible to different vendors

I have a master project datasheet where each row represents a project. I have built various reports for vendors involved to make sure the appropriate vendor only sees the appropriate rows and data fields. Each row has multiple vendors involved.

I would like to add similar control for attachments, but ensure only the appropriate attachments are shared in each vendors reports (Confidentiality among Competitor concerns).

The two thoughts I have had to do this are:

a. Load the attachment to a field vs the row. But there doesn't appear to be an attachment field option. And control visibility by access to the distinct attachment fields in reports.

b. Create different data sheets with a common join key field. In trying this I have built secondary vendor sheets with the join key for the attachment. If I create a report with both data sheets its a Union not Join function and then creates duplicate rows for each project. So I tried using join/collect function to pull the values from a master sheet. However this only allows each report to only see attachments of one sheet. There are many cases where Id like to share attachments across multiple sheets.

Is there a way to do what I am attempting?

Dave

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭

    @Dave_S

    I'd go with a hybrid of option A. However, I'd store the attachments on a Google Drive with permissions set on the folder that anyone with the link can view it. Then I'd have a column for that vendor's attachments on your master sheet. When you need to add the link you go to your drive folder, grab the URL to the file, and then CTRL-K within the cell in SmartSheet to add a website URL link. Then you can build a report for the client which only has access to the columns you give it which are the columns that pertain to their work.

  • Thanks for the suggestion I'll keep it in mind. The challenge I have with that is whitelisting vendors to our corporate Google Drive instance is not a simple process internally. Whitelisting to Smartsheet is much easier.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Dave_S

    I hope you're well and safe!

    To add to Mike's excellent advice/answer.

    • Another option could be to have multiple rows in one sheet (one per vendor) with the correct attachments on each.

    Would that work/help?

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Thanks, I am still bouncing around ideas. But I think I may just have to settle to it isn't possible to do what I was hoping within the Smartsheet and my corporate environment.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Dave_S

    Happy to help!

    Yes, sometimes (rarely) Smartsheet can't do exactly what you need. 😉

    Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!