How to deal with Lists in Appsheet?

[Vaibhav_Traders]

Hello everyone!

I need your help while dealing with lists! Suppose I have four lists,

• List1 - List of Unique HSN values (101, 105, 900).
• List2 - List of all HSN values (101, 101, 101, 105, 105, 900).
• List3 - List of all Amounts corresponds to List2 (30, 40, 40, 20, 10, 30).
• List4 - List of Sum of Amounts from List3 corresponds to List1 (110, 30, 30).

List2 and List3 are the two columns of the same table. List1 is the list of unique values of List2. Now how can I get List4 if I have List1, List2 and List3? Also I need to get List4 in the same order as List1.

Thanks in anticipation! 

[AleksiAlkio]

How are these two lists List1 and List2 generated?

[Vaibhav_Traders]

List2 is the column of a child table named as [HSN Code]. So I get the List2 in a parent table using the app formula [Related Items Details][HSN Code].

I generated List1 using the app formula UNIQUE([Related Items Details][HSN Code]).

[AleksiAlkio]

Not an easy case 🙂 Would it be okay to show them in one list only.. something like 101:110 , 105:30 , 900:30?
EDIT: or what ever format you would like to use

[Vaibhav_Traders]

Yes Sir, that would be fine too. I can edit it afterwards!

[AleksiAlkio · Accepted solution]

Then add a virtual column to your child table where it concatenated HSN code and the number where the HSN code is the first part and the qty you need to sum from child records that has the same code and parent ID.

When that is done, you can read it like SORT(UNIQUE(RelatedChilds][Code&Qty])),FALSE)

[TeeSee1]

If I understand you correctly, try the following..

Create a VC (i.e. [HSN Code Total]) in the child table with

SUM(
 SELECT(
  CHILD[quantity],
  AND(
   [HSN Code]=[_THISROW].[HSN Code],
   [parent_id]=[_THISROW].[parent_id]
  )
 )
)

you should be able to generate a LIST 4 on the PARENT table with

SELECT(
 CHILD[HSN Code Total],
 [_RowNumber] = MAX(
  SELECT(
   CHILD[_RowNumber],
   AND(
    [HSN Code]=[_THISROW-1].[HSN Code],
    [parent_id]=[_THISROW]
   )
  )
 )
)

 Something like this... (here [Item] corresponds to your [HSN Code], [ITEM_SUM] is [HSN Code Total], [SUM_UNIQITEM] is LIST 4)

This (Steve's code) is the technique used.

[AleksiAlkio]

The challenge here is.. when using SELECT() expression you can't be sure if the order of the result is always consistent. That's why I mentioned it's not an easy case to solve it directly 🙂

[Suvrutt_Gurjar]

A good suggestion @TeeSee1 

However , if I may add. a SELECT() in a virtual column , especially one with another MAX() based expression within it will definitely impact sync time, especially as the table size grows. I believe it is a good practice to use system generated reverse references whenever possible as mentioned in Aleksi's post.

[Vaibhav_Traders]

Thank you @AleksiAlkio , @Suvrutt_Gurjar , @TeeSee1 for your valuable contributions! The method suggested by @AleksiAlkio is working perfectly for us.

Thank you once again!