How to Combine Item and its Quantity

[Nani]

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

[Nani]

Team @Suvrutt_Gurjar @Koichi_Tsuji @dbaum @Marc_Dillon @SkrOYC @jyothis_m @GFormMLH @AleksiAlkio @jaichith , can you help me with this

[Suvrutt_Gurjar]

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]

[Nani]

Backend DataApp View

Hi @Suvrutt_Gurjar 

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

[Suvrutt_Gurjar · Accepted 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.

[Nani]

Thank you @Suvrutt_Gurjar , it worked

[Suvrutt_Gurjar]

Okay, great. Thank you for the update.