Hello, I am need of assistance with getting a summary export of my template. I need to total all records by the Location with Amount Sums . I have the totals that total the entire table as well as by location with the rows but I am stuck at what should be the simplest (I think my brain is just fried at this point).. I keep ending up with individual rows instead of summaries and a menagerie of other things.
Parent Table: Invoice, Child Table: Work Orders
Here is a link to my template: https://docs.google.com/document/d/124RJRjcpxo3OCwLEcNxDNDUcoo4E6DS1rO9ABr1ACw8/edit?usp=sharing
What I would like:
Location | Amount Hauled | Time | Cost |
pond 1 | 725 | 37.5 | $1,200 |
pond 2 | 575 | 88.5 | $1,500 |
pond 3 | 448 | 77 | $1,600 |
Blank | 0 | 22 | $800 |
Thank you in advance!
Solved! Go to Solution.
<<Start: ORDERBY(
FILTER(
"Work Orders",
AND(
([_THISROW].[Invoice ID] = [Invoice ID]),
(
[_ROWNUMBER]
= MIN(
SELECT(
Work Orders[_ROWNUMBER],
AND(
([_THISROW].[Invoice ID] = [Invoice ID]),
(("x" & [_THISROW-1].[Location]) = ("x" & [Location]))
)
)
)
)
)
),
ISBLANK([_Location]),
FALSE,
[Loction],
FALSE
)>>
<<[Location]>>
<<SUM(
SELECT(
Work Orders[Amount Hauled],
AND(
([_THISROW].[Invoice ID] = [Invoice ID]),
(("x" & [_THISROW-1].[Location]) = ("x" & [Location]))
)
)
)>>
<<SUM(
SELECT(
Work Orders[Time],
AND(
([_THISROW].[Invoice ID] = [Invoice ID]),
(("x" & [_THISROW-1].[Location]) = ("x" & [Location]))
)
)
)>>
<<SUM(
SELECT(
Work Orders[Cost],
AND(
([_THISROW].[Invoice ID] = [Invoice ID]),
(("x" & [_THISROW-1].[Location]) = ("x" & [Location]))
)
)
)>>
<<End>>
The above is untested and will require adaptation to the specifics of your app.
Newlines and indentation are for clarity only and must be removed for use.
In ([_THISROW].[Invoice ID] = [Invoice ID]), replace the first occurrence of Invoice ID with the name of the key column of the Invoices table, and the second occurrence of Invoice ID with the name of the Ref column of the Work Orders table that refers to the work order's invoice.
<<Start: ORDERBY(
FILTER(
"Work Orders",
AND(
([_THISROW].[Invoice ID] = [Invoice ID]),
(
[_ROWNUMBER]
= MIN(
SELECT(
Work Orders[_ROWNUMBER],
AND(
([_THISROW].[Invoice ID] = [Invoice ID]),
(("x" & [_THISROW-1].[Location]) = ("x" & [Location]))
)
)
)
)
)
),
ISBLANK([_Location]),
FALSE,
[Loction],
FALSE
)>>
<<[Location]>>
<<SUM(
SELECT(
Work Orders[Amount Hauled],
AND(
([_THISROW].[Invoice ID] = [Invoice ID]),
(("x" & [_THISROW-1].[Location]) = ("x" & [Location]))
)
)
)>>
<<SUM(
SELECT(
Work Orders[Time],
AND(
([_THISROW].[Invoice ID] = [Invoice ID]),
(("x" & [_THISROW-1].[Location]) = ("x" & [Location]))
)
)
)>>
<<SUM(
SELECT(
Work Orders[Cost],
AND(
([_THISROW].[Invoice ID] = [Invoice ID]),
(("x" & [_THISROW-1].[Location]) = ("x" & [Location]))
)
)
)>>
<<End>>
The above is untested and will require adaptation to the specifics of your app.
Newlines and indentation are for clarity only and must be removed for use.
In ([_THISROW].[Invoice ID] = [Invoice ID]), replace the first occurrence of Invoice ID with the name of the key column of the Invoices table, and the second occurrence of Invoice ID with the name of the Ref column of the Work Orders table that refers to the work order's invoice.
I remember when I discovered this from you, the dynamic "Group-by like" thing, it is one of the biggest game changers for me
A post well worth citing: https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/PAGE-BREAKS-IN-PDFS/m-p/460458/highlight/true#M...
Keeping this in my wheelhouse for sure! Many Many thanks
@Steve This worked perfectly! I cannot thank you enough again. Before I click Accept as Solution and close this, I can't figure out, what is the purpose of the "x" in the expression you wrote for me?
@ZOCO_Unlimited wrote:
I can't figure out, what is the purpose of the "x" in the expression you wrote for me?
("x" & [column]) produces a value of type Text consisting of x followed by the textual representation of whatever the column column contains. If [column] is a blank value, the result will be x rather than a blank value.
If your app is configured for legacy data processing, a blank value used in a comparison expression may have some magic behavior. Preventing a blank value by adding a leading x prevents the magic behavior.
User | Count |
---|---|
43 | |
26 | |
24 | |
14 | |
12 |