I have a table named as BOQ for Site and it contains 2 columns named as Item, column type is Enum and it has values as fruits and Quantity, column type is Number. So everyday I open the form and select any Item values and I will enter the quantity for that Item Value and I will save it. And I have view type as Table for the Detail view, Now everytime it will create a new row for same item value, now what I will do is, I will create two new virtual column, One of the Combined Item for Same Item Value and One for Combined Quantity for that Item Value, So what I do is I will create another table view for the Combined Item and for Combined Quantity. So what should be formulaโs for Combied Item and Combined Quantity
For Combined Item, I have used this formula
CONCATENATE(
SELECT(
BOQ for Site[Item],
([Item] = [_THISROW].[Item])
),
", "
)
and for combined quantity, I have used this formula
SUM(
SELECT(
BOQ for Site[Quantity],
([Item] = [_THISROW].[Item])
)
)
But I am getting like this, I want it in another way
Combined Description | Combined Quantity
Apple | 1
Orange, Orange | 1,2
Banana, Banana, Banana | 0,3,2
I want like this, as shown below
Combined Description | Combined Quantity
Apple | 1
Orange | 3
Banana | 5
Solved! Go to Solution.
Thank you. Your details are neat , crisp details and with necessary screenshots.
Please create a slice called say "Combined_View" on the table with a filter expression something like
[Key column]=MAXROW("Table Name", "_Rownumber", [Item]=[_THISROW].[Item])
Please replace columns names and table names with the actual ones you have.
Then please base your view on this slice.
When you create a slice as above, you will not need the [Combined_Item] column and its expression. You can simply use the [Item] column name in the view based on slice.
Team @Suvrutt_Gurjar @Koichi_Tsuji @dbaum @Marc_Dillon @SkrOYC @jyothis_m @GFormMLH @AleksiAlkio @jaichith , can you help me with this
Your description of configuration and intention of such addition is not clear. You may need to share relevant screenshots and details in a concise, preferably bulleted points so that the description is clearer.
But in general based on the understanding of the description so far,
1. Using virtual columns to do this computation for two columns for all the items types is not a recommended idea as it will consume substantial app sync time.
2) it sounds that you could use a "group By " property for the combined quantity by aggregating sum of quantity column. This "Group by" aggregation in the view is done by AppSheet internally and does not consume a large sync time at all.
Following is the sample of aggregating [Quantity] by ]Product IDs]
Backend Data
App View
I am very familiar with this, but I want to use this format some other place, So I need to create two virtual columns to combine Item and for combined Quantity
For Combined Description
I have changed the formula
ANY(
SELECT(
BOQ for Site[Item],
([Item] = [_THISROW].[Item])
)
)
Coming to Combined Quantity I am getting what I wanted, but for Combined Item Name I am getting another extra row, which is making combined quantity to form another row
These are the entries I made in the App
Item | Quantity
Mango | 10
Mango | 5
Banana | 10
Banana | 20
Kiwi | 50
Kiwi | 60
What I am getting in the View for Combined Item and Combined Quantity
Combined Item | Combined Quantity
Mango | 15
Mango | 15
Banana | 30
Banana | 30
Kiwi | 110
Kiwi | 110
What I wanted for Combined Item and Combined Quantity
Combined Item | Combined Quantity
Mango | 15
Banana | 30
Kiwi | 110
Thank you. Your details are neat , crisp details and with necessary screenshots.
Please create a slice called say "Combined_View" on the table with a filter expression something like
[Key column]=MAXROW("Table Name", "_Rownumber", [Item]=[_THISROW].[Item])
Please replace columns names and table names with the actual ones you have.
Then please base your view on this slice.
When you create a slice as above, you will not need the [Combined_Item] column and its expression. You can simply use the [Item] column name in the view based on slice.
Thank you @Suvrutt_Gurjar , it worked
Okay, great. Thank you for the update.
User | Count |
---|---|
17 | |
12 | |
5 | |
5 | |
5 |