How can you get a parent approval request to show child row information?

shondaconnor0413 · 02/14/24

I have a sheet I am using to obtain approvals from both the resource as well as the manager for expected time that the resource will be needed for a project (see below.) Is there a way to do this with the automation?

Parent & Child Row:

Automation:

I have tried using the Message Includes "Includes all fields" as well and neither provides a singular email that shows all items in Parent & Child Row screenshot.

jmyzk_cloudsmart_jp · 02/16/24

Hi @shondaconnor0413

I created a demo solution to add an "Assign Summary" field as the image below in the parent row used to send an approval request.

The "Assing Summary" field consists of distinct "Assigned To Name" (capitalized) and "Resource Summary" of the "Assigned To."

First, the following formula summarizes the resource with a format like "Planning: 8 h/w 20% 02/19/24~10/31/24" in the children rows of the [Resource Summary] helper column.

=IF([RS Summ]@row, "", IF(NOT(ISBLANK([Assigned To]@row)), Area@row + ": " + [Weekly Effort]@row + " h/w " + [% Allocation]@row * 100 + "% " + [Forecasted Start Date]@row + "~" + [Forecasted End Date]@row, ""))

The [RS Summ] determines if the row is a parent and the "Resource's Manager" is not blank with the following formula.

=IF(AND(COUNT(CHILDREN()) > 0, NOT(ISBLANK([Resource's Manager]@row))), 1, 0)

Then, the following formula gets distinct "Assigned To Name" (capitalized) and "Resource Summary" of the [Assigned To] helper column.

The "INDEX(DISTINCT(CHILDREN([Assigned To]@row)), 1)) " part gets alphabetically the first distinct "Assigned To" name of the children row.

The "JOIN(COLLECT(CHILDREN([Resource Summary]@row), CHILDREN([Assigned To]@row), INDEX(DISTINCT(CHILDREN([Assigned To]@row)), 1)), CHAR(10))" part gets the JOIN the collection the "Resource Summary" whose "Assigned To" value is equal to the previously gotten first distinct "Assigned To" value.

In other words, the COLLECT function collects children's values of the "Resource Summary" column with the "Assigned To" value of the same row equal to the above first distinct "Assigned To."

The following formula like the "+ IF([Distinct Assigned]@row >= 2 " checks the number of distinct "Assigned To", and if the number is equal or greater than the number behind the ">=", adds the same structure "Assigned Summary" values.

=IF([RS Summ]@row,

UPPER(INDEX(DISTINCT(CHILDREN([Assigned To]@row)), 1)) + ":" + CHAR(10) + JOIN(COLLECT(CHILDREN([Resource Summary]@row), CHILDREN([Assigned To]@row), INDEX(DISTINCT(CHILDREN([Assigned To]@row)), 1)), CHAR(10))

+ IF([Distinct Assigned]@row >= 2, CHAR(10) + CHAR(10) + UPPER(INDEX(DISTINCT(CHILDREN([Assigned To]@row)), 2)) + ":" + CHAR(10) + JOIN(COLLECT(CHILDREN([Resource Summary]@row), CHILDREN([Assigned To]@row), INDEX(DISTINCT(CHILDREN([Assigned To]@row)), 2)), CHAR(10)), "")

+ IF([Distinct Assigned]@row >= 3, CHAR(10) + CHAR(10) + UPPER(INDEX(DISTINCT(CHILDREN([Assigned To]@row)), 3)) + ":" + CHAR(10) + JOIN(COLLECT(CHILDREN([Resource Summary]@row), CHILDREN([Assigned To]@row), INDEX(DISTINCT(CHILDREN([Assigned To]@row)), 3)), CHAR(10)), "") + IF([Distinct Assigned]@row >= 4, CHAR(10) + CHAR(10) + UPPER(INDEX(DISTINCT(CHILDREN([Assigned To]@row)), 4)) + ":" + CHAR(10) + JOIN(COLLECT(CHILDREN([Resource Summary]@row), CHILDREN([Assigned To]@row), INDEX(DISTINCT(CHILDREN([Assigned To]@row)), 4)), CHAR(10)), "")

+ IF([Distinct Assigned]@row >= 5, CHAR(10) + CHAR(10) + UPPER(INDEX(DISTINCT(CHILDREN([Assigned To]@row)), 5)) + ":" + CHAR(10) + JOIN(COLLECT(CHILDREN([Resource Summary]@row), CHILDREN([Assigned To]@row), INDEX(DISTINCT(CHILDREN([Assigned To]@row)), 5)), CHAR(10)), ""), "")

The above formula checks up to 5 distinct "Assigned To" values; if there are more than 5, it ignores them. So, If you need more than 5, you can add the same structure formula.

The published demo sheet below is editable at the "Send" and "Resource's Manager" columns, so you can change the approver values to see if the desired approval request will be sent to you.

https://app.smartsheet.com/b/publish?EQBCT=fa96655d8337461487c7a054b7761509

shondaconnor0413 · 02/16/24

Thank you! This worked. :)

I did add in 2 other columns (as my Forecasted Start & End Dates were pulling in times as well.) I added SD & ED columns and used this for the SD ="Start Date: " + DATEONLY([Forecasted Start Date]@row) and for ED ="End Date: " + DATEONLY([Forecasted End Date]@row) so that it would pull in the dates only and then altered your formula to point to these columns instead of the Forecasted Start Date and Forecasted End Date columns.

The email (that I sent to myself via Smartsheet automation) looked like this: (of course I used my name as the resource and the resource manager for the below :) )

jmyzk_cloudsmart_jp · 02/17/24

Hi @shondaconnor0413

According to @Andrée Starå, a cell character limit is 4000 characters. And so is the max length of a formula.

https://community.smartsheet.com/discussion/74653/cell-character-limitation#:~:text=It's%204000%20characters.,I%20hope%20that%20helps!

My original "Assigned Summary" formula has 1122 characters, excluding space for five summaries. So, with the 4000 characters limit, the maximum number of distinct "Assigned To" would be 17. 4000/1122*5=17.8

So, if you need up to 15 distinct "Assigned To", adding ten more of this pattern formula parts should work. (You need to change n from 6 to 15)

+ IF([Distinct Assigned]@row >= n, CHAR(10) + CHAR(10) + UPPER(INDEX(DISTINCT(CHILDREN([Assigned To]@row)), n)) + ":" + CHAR(10) + JOIN(COLLECT(CHILDREN([Resource Summary]@row), CHILDREN([Assigned To]@row), INDEX(DISTINCT(CHILDREN([Assigned To]@row)), n)), CHAR(10)), ""), "")

As shown in the image below, a more scalable way is to use multiple helper columns, RS1, RS2 ..., and RS 20.

The formula for these are;

RS1

=IF([Distinct Assigned]@row >= 1, CHAR(10) + CHAR(10) + UPPER(INDEX(DISTINCT(CHILDREN([Assigned To]@row)), 1)) + ":" + CHAR(10) + JOIN(COLLECT(CHILDREN([Resource Summary]@row), CHILDREN([Assigned To]@row), INDEX(DISTINCT(CHILDREN([Assigned To]@row)), 1)), CHAR(10)))

RS2

=IF([Distinct Assigned]@row >= 2, CHAR(10) + CHAR(10) + UPPER(INDEX(DISTINCT(CHILDREN([Assigned To]@row)), 2)) + ":" + CHAR(10) + JOIN(COLLECT(CHILDREN([Resource Summary]@row), CHILDREN([Assigned To]@row), INDEX(DISTINCT(CHILDREN([Assigned To]@row)), 2)), CHAR(10)))

RS3 - RS20: The same pattern changing the number.

Then, the "Assigned Summary" formula becomes simple.

=IF([RS Summ]@row, JOIN([RS1]@row:[RS20]@row, CHAR(10) + CHAR(10)))

Since Smartsheet does not have the "for-loop" available in programming languages, we must cope with brute formula repetition and helper columns, avoiding limitations. So, I developed a couple of tools to cope with such needs. (Images at the bottom)

But, since you do not have access to such tools, I shared the demo sheet with you to save you from the repetitious task. Please feel free to make a copy of the sheet.

https://app.smartsheet.com/b/publish?EQBCT=16ce9b124c124b339d048420fe112f7d

The approval request example

Formula tools example:

https://publish.smartsheet.com/5c2c16de0a5547068e2317f10e2cd01b

https://publish.smartsheet.com/081c5072827d46588ddb6a2db22331af

jmyzk_cloudsmart_jp · 02/16/24

Hi @shondaconnor0413

I created a demo solution to add an "Assign Summary" field as the image below in the parent row used to send an approval request.

The "Assing Summary" field consists of distinct "Assigned To Name" (capitalized) and "Resource Summary" of the "Assigned To."