Workflow - using multiple tables in a set of forms

[tcanelli]

See attached. Wondering if it’s possible to pull multiple table data into a google doc template?

What I tried (in the attached) didn’t work. Workflow triggered but I didn’t get the data I wanted.

[tcanelli]

To give a little more info - when we prepare an Incident Action Plan there are 4 or 5 standard forms and all include some common fields: incident name, operational period, prepared by, etc. - that would all come from one table.

The example above ICS 203 - has that header info, but then the staffing portion would need to come from another table - which is a slice of who we currently have checked in.

Thanks

[LeventK]

@tcanelli
Provided you have some common info between the tables, you can easily use an appropriate SELECT expression to fetch data from another table.

[tcanelli]

Do I need a ref between the tables? They really don’t have common info. one contains all the personnel and the other is incident related info.

[LeventK]

@tcanelli
If you don’t require to filter that table, than you can select all data. You don’t specifically need a ref between these tables but it will help for sure.

[tcanelli]

ok last question for today - i don’t need to filter the incident info table, but for the personnel table do I need a filter if I’m already using a slice that is filtered? for each position listed on the 203 form (first post above) I just want to show the name of who is filling that role. I don’t think I did it correctly above. I want to display the _ComputedName for the [Activation Position] that equals - whatever that field is on form.

[LeventK]

@tcanelli
You can simply do that with a LOOKUP expression actually but you need to filter the table I’m afraid

[tcanelli]

this?

<<LOOKUP(“Incident Manager”, “MACC Display view”, “Activation Position”,
“_ComputedName”)>>

[tcanelli]

I think I’m closer - BUT now - when I filled out the form to trigger the workflow the pdf I received actually pulled every row from the sheet instead of just the current row. How do I fix this?

I also don’t really understand the start and end expressions in this particular workflow since I’m using more than one table. sigh…

[Phil]

Can you first clarify whether this template is:

1. In a workflow rule
2. In a Report that specifies “For each row in table”
3. In a Report that specifies “For entire table”

Knowing this is important because it determines the initial condition when the outermost <<Start>> expression is evaluated.

In general, it is hard to display nested information from two or more records in a form as you appear to be attempting to do.

As it currently stands, your template is saying:

1. Repeat the row containing “Incident Name” and “Operational Period” once for every record returned by the Start expression <<Start: IAP test[AP Number]>>. That is why your output repeats that row for each row returned by the <<Start>> expression.

2. Display the row containing “Incident Command(s) and Command Staff:” and “Operations Section” exactly once.

3. Display the row starting with “IC/UCs” exactly once. Within that single row, the <<Start: SELECT …>> expression is saying to retrieve the set of records specified by the <<Start>> expression and display values from each record. In the template in your screen shot, I do not see which fields, if any, you are displaying in the cell. I only see the <<Start>> and <<End>> within that cell. Maybe the entire contents of the cell are not shown in the screen shot.

4. Displaying each of the following rows exactly once.

When you are trying to display information from “nested” tables, it is a lot easier to display information from the “outer table” and “Intermediate tables” (if any) in list format. Then you can display the information from the “inner most table” in table format. For example, see topic “Customer Template in Table Format” in this article https://help.appsheet.com/en/articles/2697047-sample-email-templates. In this example, we only use table format to display data from the inner most “Order Details” table.