Daily Reporting to Document Generation

HFerrari13

Good Afternoon,
Our construction firm utilizes a standard Gantt chart sheet to track our project's daily reports. Under each category of work we have milestones, i.e. Carpentry :: Framing :: Drywall etc. Under each milestone we add a child for that days work, i.e. Drywall :: Day 1 - Mobilized :: Day 2 - Started Hanging Sheets. I've attached a snip of our standard sheet.

I'm trying to build a system that can take that information daily and generate an elegant pdf that can be signed to verify the report is accurate. Document Generation seems to be my best bet but might not have the flexibility to include all the rows that happened that day. A sample of what I'm trying to accomplish:

Is there any way to summarize an unknown amount of rows for each day into a fillable pdf through Smartsheet? Thanks in advance for your help!

Brian_Richardson

Fillable PDFs can only take the data from a single row. From your question and screenshot, it looks like you might just need a total of hours from the children? If so then that's straightforward, you can use =SUM(CHILDREN([Total Hours]@row)) on the parent row in a cell to get the sum of hours from all children.

If instead you want your PDF to show your child rows, that's much harder. Fillable PDFs can only populate from a single row of data. One way to work around this is to build additional columns for use on the parent row, one for each possible child row. You'll have to pre-set how many child rows you want to show.

Then, use the approach in this question to setup sequential numbering for each child:

https://community.smartsheet.com/discussion/124878/child-auto-numbering-per-parent-row

Once you have sequential numbering, in the columns that you have to capture the child information, you can use those sequential numbers. For example, if you want to have 5 rows in your PDF for up to 5 child tasks, with each row having the child task name and hours, you'll need 10 columns in your sheet.

Child 1 Name

Child 1 Hours

Child 2 Name

Child 2 Hours

etc.

In Child 1 Hours, you can use a formula like this, set it as a Column Formula: =if(Parent@row="",SUMIFS([Total Hours]:[Total Hours},Parent:Parent,Item@row,[Item Number]:[Item Number],1))

In Child 1 Name use =if(Parent@row="",JOIN(COLLECT(Item:Item,Parent:Parent,Item@row,[Item Number]:[Item Number],1),""))

In Child 2 Hours use the same formula but with 2 as the Item Number like =if(Parent@row="",SUMIFS([Total Hours]:[Total Hours},Parent:Parent,Item@row,[Item Number]:[Item Number],2))

and so on.

You should end up with your parent rows having multiple "child" columns filled out with the names and hours from the children below.

Then finally, set up your PDF format to bring in Child 1 Name and Hours into one row, Child 2 onto a next row, etc.

Matt Lynn-PCG

@HFerrari13 Brian's info from a technical standpoint above is great. I would add a simple response to your question, specifically the part that says ".. elegant pdf." Smartsheet's 'current' document generator isn't designed to be elegant and simply won't do that level of output, at this time. I think we'll see an improved doc editing/builder in the future. For now it is strictly designed to do basic text population mainly for things like docusign. I've done several advanced documents and "it works" but they're far from elegant.

Your use case above is interesting as I'm currently developing a construction project/dashboard rollup system for daily reports, current weather, photos etc. It isn't a doc, but will be a project/portfolio rollup of the live/latest info. Point… I appreciate your screenshot.