Hi ... can somebody help/explain why i have d...

expressions
(Geoff Ringent) #1

Hi … can somebody help/explain why i have data type issues with the following:

I have two tables (separate tabs) in a google sheet.

first tab (tblDay) has [PK_DayId] (date) as the key

second tab (tblFare) has it’s own PK together with the primary key from tblDay, serving as the foreign key.

tblFare holds many records that relate to one record in tblDay

tblDay has a virtual column ‘Related tblFares’ generated by the system on declaring the relationship between the two tables.

In the primary table (tblDay) i have a virtual column [NumRides] (of type NUMBER ) that is computed by the COUNT of rows in the ‘Related tblFares’… … all good so far

“=COUNT(([Related tblFares], [PK_DayId] = [_THISROW].[PK_DayId]))”

I need to use this value (which is of type NUMBER) in a column in the child table (tblFare) when computing a default sequential number (Initial value) for each new row added.

"=SELECT(tblDay[NumRides],[PK_DayId]=[_THISROW].[FK_DayId]) "

HOWEVER … the above SELECT statement gives the following error:

####################################### “The expression is valid but its result type ‘List’ is not one of the expected types: Number” #######################################

WHY does the SELECT statement not return a NUMBER … as that is the data type it is selecting??

If i am misusing the SELECT function here, can someone please explain HOW i can retrieve the value from the column NumRides in the parent table to use here in setting the initial value of a column in the child table??

I have a related question also … i have a third ‘system table’ in the spreadsheet where i would like to store CONSTANTS … or GLOBAL variables … so i do not have to ‘hard code’ values in the design. I have the same issue when trying to retrieve them as describe above … the value is returned as type LIST ??

Thanks for any assistance in advance

Geoff

(Fernando López) #2

Try this: ANY( SELECT…)

(Geoff Ringent) #3

@Fernando_Lopez Thank you Fernando!! … so simple as it turns out! Can you point me to documentation/references on this language so that i can explore my own solutions?? :slight_smile:

(Fernando López) #4

https://help.appsheet.com/expressions help.appsheet.com - EXPRESSIONS | AppSheet Help Center EXPRESSIONS | AppSheet Help Center help.appsheet.com