Unable to prepare a report for sum quantity of each item

SSC
Participant II

Hello,
i am unable to make it work since last two days.
My Dispatch Table contains fields [Unique ID], [Party Name] , [Site] , [Item], [Quantity].
One Party Name can have multiple sites, and on each site multiple items are dispatched in quantity.

i am trying to create an excel report that shows me the total sum of item quantity dispatched on each site for each party name.

Example:
Party Name Site Item Quantity
Party1 Site1 Item1 10
Party1 Site1 Item2 100
Party1 Site1 Item1 50
Party1 Site2 Item2 100
Party1 Site2 Item1 20
Party1 Site2 Item2 200
Party2 Site1 Item1 10
Party2 Site2 Item1 100

I wish to have the following Result in excel file:
Party Name Site Item Total Quantity
Party1 Site1 Item1 60
Party1 Site1 Item2 100
Party1 Site2 Item1 20
Party1 Site2 Item2 300
Party2 Site1 Item1 10
Party2 Site2 Item1 100

i am not getting what start expression should i use. i used the following start expression but its not working
<<Start: select(Dispatch[Unique ID], true)>>
i am confused what expression will sum up the item quantity as per site and party names. i tried different expressions of unique() too but havent got any result.

0 13 1,445
13 REPLIES 13

Steve
Participant V

See if this gets you anywhere:

<<Start: ORDERBY(FILTER("Dispatch", ([Unique ID] = MINROW("Dispatch", "_ROWNUMBER", ([Party Name] = [_THISROW-1].[Party Name])))), [Party Name])>>
<<Start: ORDERBY(FILTER("Dispatch", ([Unique ID] = MINROW("Dispatch", "_ROWNUMBER", AND(([Party Name] = [_THISROW-2].[Party Name]), ([Site] = [_THISROW-1].[Site]))))), [Site])>>
<<Start: ORDERBY(FILTER("Dispatch", ([Unique ID] = MINROW("Dispatch", "_ROWNUMBER", AND(([Party Name] = [_THISROW-2].[Party Name]), ([Site] = [_THISROW-2].[Site]), ([Item] = [_THISROW-1].[Item]))))), [Item])>>
<<[Party Name]>>
<<[Site]>>
<<[Item]>>
<<SUM(SELECT(Dispatch[Quantity], AND(([Party Name] = [_THISROW-1].[Party Name]), ([Site] = [_THISROW-1].[Site]), ([Item] = [_THISROW-1].[Item]))))>>
<<End>>
<<End>>
<<End>>

Thanks for the Reply Steve @Steve . I used your start expressions and got the following error in audit log.

โ€œErrorsโ€: โ€œError: Report โ€˜Testโ€™ action โ€˜testโ€™ Body template. Expression โ€˜ORDERBY(FILTER(โ€œdispatchโ€, ([_THISROW] = MINROW(โ€œdispatchโ€, โ€œ_ROWNUMBERโ€, ([Party Name] = [_THISROW-1].[Party Name])))), [Party Name])โ€™ is invalid due to: Unable to find column โ€˜_THISROWโ€™, did you mean โ€˜Timeโ€™?. Error: Report โ€˜Testโ€™ action โ€˜testโ€™ Body template. Start expression โ€˜ORDERBY(FILTER(โ€œdispatchโ€, ([_THISROW] = MINROW(โ€œdispatchโ€, โ€œ_ROWNUMBERโ€, ([Party Name] = [_THISROW-1].[Party Name])))), [Party Name])โ€™ should generate a List of Ref values. Please verify that it generates a List and that the contents of the List are Ref values. Ref values should come from the โ€˜Keyโ€™ column of the referenced table. Error: The document body is emptyโ€,

But why is [_thisrow-1] and [_thisrow-2] needed?
I Have a Party Name table in which there are details of party names. then that party name column is referenced in dispatch table in column party name. so is [_thisrow-1] used for reference of Parent record i.e Party Name record in party name table?

Whoops! My mistake! Iโ€™ve updated my post above: [_THISROW] should be [Unique ID] instead.

See this post:

Very Thank you steve @Steve. i ran those expressions. but i am getting repeated number of rows. Attaching screenshot. i am not getting single row party wise item wise total. Should i modify something in expressions?

Iโ€™m not going to do all the work for you.

Iโ€™ve given you the expressions to get the data in the order you want. Itโ€™s up to you to format it as you want.

@Steve I am not asking you to do any work for meโ€ฆ All i am saying is the expressions you gave me are not working. I tried manipulating it to get the desired result and today is already the third day that inspite of so many attempts, i am unable to produce such reportโ€ฆ appsheet is new to me and i am still in learning processโ€ฆ i have read a lot of posts in which you and aleksi have shared a lot to guide people in the right direction. So i thaught inspite of the report not getting generated the third day, lets post it on the forum for guidance as how to generate this kind of report. Anywaysโ€ฆ thanks for the helpโ€ฆ I appreciate itโ€ฆ

@Steve I have tried understanding your expressions and tried manipulating them in a lot of ways. i read your post in which u explain [_thisrow-n] to dereference above querries and tried relating that to the expression you posted above. You wrote the first expression as:
<<Start: ORDERBY(FILTER(โ€œDispatchโ€, ([Unique ID] = MINROW(โ€œDispatchโ€, โ€œ_ROWNUMBERโ€, ([Party Name] = [_THISROW-1].[Party Name])))), [Party Name])>>
You used [_thisrow-1], but this is already the outermost querry. i am unable to get why u used [_thisrow-1] here. i have tried using the same expressions using select, but havent got anywhere with the result. i am understanding your expressions in the following way:
Outermost Expression (Say Querry1):
<<Start: ORDERBY(FILTER(โ€œDispatchโ€, ([Unique ID] = MINROW(โ€œDispatchโ€, โ€œ_ROWNUMBERโ€, ([Party Name] = [_THISROW-1].[Party Name])))), [Party Name])>>
This selects minumum row number of party name and gets party name

Querry2:
<<Start: ORDERBY(FILTER(โ€œDispatchโ€, ([Unique ID] = MINROW(โ€œDispatchโ€, โ€œ_ROWNUMBERโ€, AND(([Party Name] = [_THISROW-2].[Party Name]), ([Site] = [_THISROW-1].[Site]))))), [Site])>>
This selects site for party name selected above

Querry3:
<<Start: ORDERBY(FILTER(โ€œDispatchโ€, ([Unique ID] = MINROW(โ€œDispatchโ€, โ€œ_ROWNUMBERโ€, AND(([Party Name] = [_THISROW-2].[Party Name]), ([Site] = [_THISROW-2].[Site]), ([Item] = [_THISROW-1].[Item]))))), [Item])>>
This selects item for Party Name and Site selected above.

I feel the expressions and logic correct, but the result is repeating itself in a loop. And the same Party name, site and item are repeating itself. Any suggestions?

Hello @Steve. I solved it using select expression instead of filter in a single querry as it was repeating due to nested expressions.
<<Start: select(Dispatch[Unique ID],([Unique ID] = MINROW(โ€œDispatchโ€, โ€œ_ROWNUMBERโ€, and(([Party Name] = [_THISROW-1].[Party Name]),[Site]=[_THISROW-1].[Site],[Item]=[_thisrow-1].[item]))))>>
<<[Party Name]>>
<<[Site]>>
<<[Item]>>
<<SUM(SELECT(Dispatch[Quantity], AND(([Party Name] = [_THISROW-1].[Party Name]), ([Site] = [_THISROW-1].[Site]), ([Item] = [_THISROW-1].[Item]))))>>
<>

Thanks @Steve. But i am still not able to get why [_thisrow-1] is used when this itself is the outermost and single querry. Does it mean that [_thisrow-1] is referring to the origin row (since [_thisrow] is origin row)?

Actually, itโ€™s not in the outermost query: MINROW() is itself a query, so [_THISROW-1] used in the MINROW() subexpression refers to the row being examined by the enclosing FILTER() expression.

The query functions are FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT():

Okayโ€ฆ Now i got itโ€ฆ
I was considering it as a single querry. Thanks steve @Steve
You are a star in appsheet

@Steve Sorry for bothering you again but i am again stuck in similar report.
I am trying to get unique party names and looping sites, items and quantity under the respective party names. I have tried this so far without any successโ€ฆ

<<Start: select(Dispatch[Unique ID],([Unique ID] = MINROW(โ€œDispatchโ€, โ€œ_ROWNUMBERโ€, ([Party Name] = [_THISROW-1].[Party Name]))))>>
<<[Party Name]>>

<<Start: select(Dispatch[Unique ID],([Unique ID] = MINROW(โ€œDispatchโ€, โ€œ_ROWNUMBERโ€, and(([Party Name] = [_THISROW-2].[Party Name]),[Site]=[_THISROW-1].[Site],[Item]=[_thisrow-1].[item]))))>>
<<[Site]>>
<<[Item]>>
<<SUM(SELECT(Dispatch[Quantity], AND(([Party Name] = [_THISROW-1].[Party Name]), ([Site] = [_THISROW-1].[Site]), ([Item] = [_THISROW-1].[Item]))))>>
<>

I feel [_thisrow-2].[party name] is not a correct way to reference the value from another start expression above. But i cant think anything other than thatโ€ฆ is it possible to reference a value from a different start expression? If not, is there any other alternative to this situation?

@Steve How about this data to type โ€œListโ€ in virtual table?
Party1 10
Party1 100
Party1 50
Party1 100
Party1 20
Party1 200
party3 100
Party2 10
Party2 100
party3 100

to list virtual table?
party1 480
party2 110
party3 200

thank you before

Top Labels in this Space