How to generate one document for two form formatting and applies auto number only in dark blue row?

KryF

Hi, I am currently working on a two form formatting to generate a purchase requisition form. As you can see, there is one row in dark blue referring to the purchase requisition details, and another two rows referring to the breakdown of the items to purchase.

1) I would like to generate the 3 rows into one document and attached to the dark blue row. However, so far, the constraint that I am facing is that generation of documents is by each row.

2) I am applying auto numbering. However, I would want to only capture the auto numbering only when dark blue rows are available. Currently, the auto numbering is also applied to each row and not able to be selective. Is there any formula or automation i can use?

3) How to sum up the item cost to show a grand total of a particular purchase based on every order details submitted? Any formula that can help?

Thank you.

Genevieve P.

Hi @KryF

1) Currently Document Builder can only generate documents on a row-by-row basis, and will be unable to select data across three rows at once for one document as you've found. Please submit your request and feedback to our Product team through this form, here!

2) Auto-Number System Columns will number each individual row. You can use a formula to generate unique numbers per blue row. We would need to reference something that will only ever appear in a blue row, and not the other rows. It looks like your "Submission" column will only be populated on blue rows, is that correct?

If so, you could put this formula into a helper column:

=IF(Submission@row <> "", COUNTIFS(Submission$1:Submission@row, <>""))

Keep in mind that since we're using a direct row reference ($1), we won't be able to make this a Column Formula. You'll want to drag-fill this down your column. As long as two rows contain a formula, then rows inserted above or below will automatically inherit this formula as well.

3) The easiest way to do this would actually be to create Hierarchy in your sheet. You can indent the two or three or four rows that are associated with a top blue row. (See: Hierarchy: Indent or Outdent Rows)

Then you can simply put a SUM(CHILDREN formula into the Grand Total column. You can say that if the current row does NOT have a Parent row (or there are no Ancestors so it's a blue row), then the formula should SUM the child rows in the Total Price column.

Try something like this after you've indented your white rows:

=IF(COUNT(ANCESTORS([Purchase Requisition]@row)) = 0, SUM(CHILDREN([Total Price]@row)), "")

Let me know if you need further help with any of the above information!

Cheers,

Genevieve