Generate PDF with data from multiple sheets?
I'm trying to create a project management system in Smartsheet, that has the ability to generate Invoice/Estimate PDFs. I already know how to generate PDFs with a single row, but is it possible to generate a PDF with data from multiple sheets?
I would need to get data from the parent "Projects" sheet (for client name, project info), the connected "Deliverables" sheet (the individual service items), and the "Rate Card" sheet (to get the pricing).
Can this be done with document generation feature?
Answers
-
Hi @JPJPJP
You can create PDFs with data from multiple sheets, but you must combine the data on a single sheet with formulas.
Project Sheet > PDF Sheet
Example:
[Project Name]=INDEX({Project Name}, MATCH([Project ID]@row, {Project ID}, 0))
[Client Name] =INDEX({Client Name}, MATCH([Project ID]@row, {Project ID}, 0))
Deliverable Sheet> PDF Sheet
You will typically have multiple deliverables or service items. In that case, you must create separate columns for those and use formulas like the following to get the data from the deliverable Sheet.
Example:
[Deliverable Name 1] =IFERROR(INDEX(COLLECT({Deliverable Name}, {Deliverable Sheet Project ID}, [Project ID]@row), 1), "")
[Deliverable Name 2] = =IFERROR(INDEX(COLLECT({Deliverable Name}, {Deliverable Sheet Project ID}, [Project ID]@row), 2), "")
PDF Data Sheet
Project Sheet
Deliverable Sheet (Get Rate Data from Rate Sheet)
Rate Sheet
-
Thanks so much for this detailed reply! I'll give this a try. But question - I noticed you put all the deliverables as individual columns (up to 3), but that would mean with this solution there's a limit on how many deliverables a user can submit? We have projects that have deliverables as big as 50 deliverables sometimes. So I don't think putting them as individual columns works since we can't have restrictions on the number of deliverables?
-
Hi @JPJPJP
A fillable PDF invoice typically provides space for up to 10 items and requires their total entry.
However, configuring a PDF with numerous fields becomes highly time-consuming* when managing an invoice for 50 deliverables, each containing details such as name, description, assignee, rate, and amount. I recommend creating a separate 'Deliverable Report' or 'Deliverable Details' PDF using Smartsheet's standard print feature in a situation like yours.(*It is possible to put more than 50 deliverables information in individual columns in a row as the maximum allowed column number of Smartsheet is 400. )
The revised demo solution would then pull the total amount from the deliverable sheet into the Projects Sheet and utilize the generate PDF feature to create an invoice. Additionally, it would produce a separate 'Deliverable Report' from either the deliverable sheet itself or a report derived from the sheet.
Project Sheets < Total Amount from Deliverable Sheet
Deliverable Sheet < Rate from Rate Sheet, Project Name from Projects Sheet
Deliverable Report created from a Report
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives