General question regarding VC and Query formula in sheets

[Hussein_Osseily]

Good day Community, 

I am trying to understand the following if you may help me with these questions:

In a sheet where the data in it are pulled via =QUERY("select, sum, group") formula: 

1- Is it advised to generate a Unique ID Column via a sheet formula
=DEC2HEX(RANDBETWEEN(0, 4294967295), 8) (due to the grouping , selecting the ID from the source table is not an option), Or is it better to creat a VC with a UNIDQUEID() Expression and use it as a key ? 

2- Is it advised to apply an action on that sheet being pulled via the =QUERY("select, sum, group") formula ?

3- Is it advised (safe) to add VCs to that particular table pulled by =QUERY()?

4- is there an expression/nested expressions to acheive the same result in a slice as below ? 
Example: 

=QUERY( A:I,
"SELECT B, D, E, F, G,
SUM(H), SUM(I)
WHERE B IS NOT NULL
GROUP BY B, D, E, F, G")

Thanks in advance !

[Marc_Dillon · Accepted solution]

I use QUERY in spreadsheets a lot, and semi-often load these sheets into apps, so I can hopefully answer some of these with my experience.

1. A QUERY()'d table will ALWAYS need to be read-only in the app. In such a case, I find it completely acceptable to use _RowNumber as the key. This is probably the ONLY time that I would ever suggest this. **Unless you need to reference these records in another table**. In that case, I believe you'd need to pull an id from your base data, as there'd be no way to ensure that records would get the same id every time.

2. Apply what action? As mentioned above, such a table has to be read-only, so you really can't apply very many actions.

3. Yes this should be fine.

4. Appsheet slices can't perform aggregations across groupings. So if those are necessary for you, probably better to stick to the QUERY(). Or you could calculate the aggregations in a VC, but that could get expensive. However if you're just needing to remove duplicates, the typical way to do so is to use something like:

[key-column] = MINROW( same-table , _RowNumber , AND( [_THISROW].[b]=[b] , [_THISROW].[D]=[D] ,......

[Hussein_Osseily]

Dear @Marc_Dillon thanks for your clarifying the points above it was very helpful and coming from your own experience made it even better. 

The query tables i use are only to sum and group and stuff like that so it will be a read only table and as you suggested will be using the _row number as a key in such a case only

And the action that I might apply on a query table is to move the records to another table with the expression UNIQUE() as an ID 

Thanks agagin for your response much appreciated.