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

Geoff_Ringent · 08-16-2018 05:00 PM

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_Lopez1

Try this: ANY( SELECT…)

Geoff_Ringent

@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??

Fernando_Lopez1

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

Cabelo

Hello everyone,

I am having a similar issue with an expression, I assume that because of the SELECT as well. I use the following expression a lot in the app and it wors well:
SUM(SELECT(Cash Income[Amount in USD], ([Currency]=[_THISROW].[Currency])))
But I have been trying to create an expression with a bunch of them and it gives me the result as a “LIST” as well.

And this is what it looks like when I set the Type as “LIST” which is not what I want, I want “Decimal”. So there is clearly something on my expression that give sit the “LIST” value but what is it?

THANKS!

Suvrutt_Gurjar

Hi @Cabelo,

it sounds that your are trying to add and subtract amounts with several individual SUM statements.

If so, can you please try removing the curly brackets { and } that are wrapped around each SUM expression.

Cabelo

Thanks @Suvrutt_Gurjar

See attachment, still same result!

Suvrutt_Gurjar

Hi @Cabelo,

Thank you.

The new error message says, the expression is evaluating to decimal, that you are looking for.

However error says that the expected type is list.Is it possible that your column type is now list type? If so, could you please change it to decimal type?

Cabelo

Oh yeah!!!

Sorry for the lack of attention here, it really looked like the same message

Thank you! One day I will understand these expressions that well!

Cheers

Suvrutt_Gurjar

Hi @Cabelo ,

You are welcome. Good to know it works.

if I may add, curly brackets are one way of denoting lists. So a list could be denoted in following two ways

LIST( “Neptune”, “Mars”, “Mercury”) or
or simply
{“Neptune”, “Mars”, “Mercury”}

I believe your wrapping those SUM expressions in curly brackets were creating independent lists ( single element) out of those each individual SUM expressions. Hence the suggestion to remove the curly brackets.

You may wish to take a look at the following article, just in case you have not that describes the formation of lists with both the notations.

Hope this helps.

Cabelo

Thanks @Suvrutt_Gurjar

For me the use of the brackets, parentheses and etc is probably the hardest part of these expressions. Thanks for the tip, I appreciate that! Learning everyday!

PetrusRJ

Hi, I am new to Appsheet trying to create a personal expenses app and I need to know how can I create a slice to show all my expenses in a specific month?

Steve

PetrusRJ

I did. I am not a developer. All the support pages for Appsheet expressions look so incomplete to me…

Koichi_Tsuji

Under Appsheet terms, anyone working with Appsheet as platform is called “developper”. I would say the Appsheet docs are well documented to do bits and pieces the app creator want to achieve.
To get familiar with appsheet, excersise the docs and do hands on excercise as well, and you will see error and success. Repeating those processess, we will get to know what Appsheet is at the end.

Jose_R_B

Hello Appsheet Community.
I have the same error message but I can't find the fault of the formula.

I would appreciate the help.🤗

Steve

Please start a new topic for help with this.

Jose_R_B

Ok Steve,

Thanks.