AND() Expression using two ANY(SELECTs...)

I am getting a non-verified expression, when I combine them in a AND() expression. Both expressions are verified and doing the things I want, but combining them gives me the warning: Condition AND(ANY(SELECT(Lookuptable[Model ID],([Concat] = [_THISROW].[Concat]))), ANY(SELECT(Lookuptable[Model ID],([Flow in Ls] >= [_THISROW].[Flow in Ls])))) has an invalid structure: subexpressions must be Yes/No conditions

AND(
ANY(SELECT(Lookuptable[Model ID], [Designflow in units]>=[_THISROW].[Designflow in units]))
,
ANY(SELECT(Lookuptable[Model ID], [Concat]=[_THISROW].[Concat]))
)

How can I turn this in a truefalse expression?

This is a section from my Lookuptable with ~6500 entries:

There is a form with the name FormEntries, where end-users fill out a form and two fields are generated, one is a simple concat and the other one to calculate a simple calculation:

Then there is an action to copy the new added rows to another table called ModelIDSelect where there should be an expression to find the right model ID from the Lookuptable using the concat and the Designflow in units columns.

Solved Solved
0 14 778
1 ACCEPTED SOLUTION

ANY( SELECT(
  Lookuptable[Model ID],
  AND(
    [Flow in Ls] >= [_THISROW].[Designflow in units] ,
    [Concat Table] = [_THISROW].[Concat]
  )
)

View solution in original post

14 REPLIES 14

Steve
Participant V

Aurelien
Participant V

Hi @Iremlin

If you analyze your expression:

AND(
  ANY(SELECT(Lookuptable[Model ID], 
     [Designflow in units]>=[_THISROW].[Designflow in units])
  )
,
ANY(SELECT(Lookuptable[Model ID], 
     [Concat]=[_THISROW].[Concat])
  )
)

this gives a list of โ€œmodel IDโ€ with a condition on the โ€œDesignFlow in unitsโ€:

SELECT(Lookuptable[Model ID], 
     [Designflow in units]>=[_THISROW].[Designflow in units])

so, this gives any model ID you may have in this list:

 ANY(SELECT(Lookuptable[Model ID], 
     [Designflow in units]>=[_THISROW].[Designflow in units])
  )

==> there is no Yes/No type output there, so the AND() expression will have invalid inputs

First I got YES/NO Expression like this:

AND(
[Designflow in units]>=[_THISROW].[Designflow in units]
,
[Concat]=[_THISROW].[Concat]
)

Then I updated my Expression like this to get the right model ID:

ANY(SELECT(Lookuptable[Model ID]),
AND(
[Designflow in units]>=[_THISROW].[Designflow in units]
,
[Concat]=[_THISROW].[Concat])
)

why this is valid? how can I select with these two conditions?

3X_8_5_851779dac3f92ea3f00f0b0ea95ea71f7f5437ca.png

ANY( SELECT(
  Lookuptable[Model ID],
  AND(
    [Flow in Ls] >= [_THISROW].[Designflow in units] ,
    [Concat Table] = [_THISROW].[Concat]
  )
)

Now I had it verified, but it is giving me a wrong Model ID. No matter which options I choose it is giving me only the very first Model ID in my table. so the any select is not doing what it is supposed to do:

and the result is always:
3X_e_5_e5dd94c435a6b007603a82c4ac20a4e1bfdc1196.png

Letโ€™s take a look at my last option:

RAS Tank 10 10 250 flow in units: 150 (Designflow in units is also 150 in that calculation)


but I am getting always the very first model.
This is how the expression outcome is:

How come it is not selecting?

ANY takes a random item from a list.
SELECT is a list. You probably have various items in it.
If you try the select expression alone, what is the output you get ?

If I remove ANY()
I get all of the items together:

But then the app crashes with this:


so we I guess we need this ANY() but our expression is not right somehow

Your spreadsheet shows a โ€œFlow in LSโ€ column, but your expression shows a โ€œDesignflow in unitsโ€ column. Is this the problem? If not, it is certainly making your situation extra confusing.

the column name is correct, and yes, you are right , my usecase is super complicated, but let me explain:
The user is selecting some values in a form, then the record is saved in the FormEntries Table. There I have a field with the name [Designflow in units] this column has a formula:

[Flow in Ls]/[Number of units]*1+[Redundancy on flow in percentage]/100 

this is a calculation based on 3 fields.

Then I have this action to copy the values in another table: ModelIDSelect

In a SELECT() expression, AppSheet attempts to determine which row a bare column reference refers to. For instance, if given the bare column reference [Concat], AppSheet looks first to see if the table being scanned by SELECT() has such a column. If so, it will use that rowโ€™s column value. If the table being scanned does not have a column of that name, AppSheet looks to see if the row from which the SELECT() expression was run has such a column. If so, AppSheet uses that columnโ€™s value.

Looking at your expression:

3X_5_9_594742a6a07740200e0f5862a16acd77b72787fe.png

Consider [Designflow in units] >= [_THISROW].[Designflow in units]. The first operand, [Designflow in units], is a bare column reference. If the Lookuptable table (the table being scanned by SELECT()) has a column named Designflow in units, the value of that column of the scanned row in Lookuptable will be used. If Lookuptable does not have a column named Designflow in units, AppSheet will instead look for that column in the row from which SELECT() was run. In that case, [Designflow in units] is treated as [_THISROW].[Designflow in units], which in effect makes the subexpression [_THISROW].[Designflow in units] >= [_THISROW].[Designflow in units], which is always TRUE.

If Lookuptable has neither a Designflow in units column nor a Concat column, your expression amounts to:

ANY(SELECT(Lookuptable[Model ID],
AND(
[_THISROW].[Designflow in units]>=[_THISROW].[Designflow in units]
,
[_THISROW].[Concat]=[_THISROW].[Concat])
)
)

That AND() is always TRUE, so SELECT() will return all rows and ANY() will always return the first of that list of all.

Lookuptable appears to have neither Designflow in units nor Concat:

Myself, I hate that AppSheet behaves this way, specifically because it can cause confusion like this.

Now I am getting a better understanding of AppSheet. You are fully right. In the LookupTable I dont have those columns. I had [Concat table] so I changed it to [Concat] and guess what happened:


I got another response instead of the very first option, so only the half of the select is working:

In this logic, the app would work if I have the [ Designflow in units] in the Lookuptable but this is not possible because it must be a free numeric input. we cannot put it in the lookuptable because we need to run the calculation:

[Flow in Ls]/[Number of units]*1+[Redundancy on flow in percentage]/100 

this needs to be calculated, this is the main feature in the application. We are using Google Sheets at the moment and we want to transform it in appsheet for the sake of ease. If you want to take a look at our Sheet, you can create your own copy and test by selecting the yellow boxes and the green box will be the output model ID

https://docs.google.com/spreadsheets/d/1fJA7nDzII7AGI8o9BBZQtOzGGJFdlGo7fqy2lMjMp1o/copy

Oh my GOD !!!

So i have edited the Expression a little bit

ANY(SELECT(
  Lookuptable[Model ID],
  AND(
    [Flow in Ls] >= [_THISROW].[Designflow in units] ,
    [Concat] = [_THISROW].[Concat]
  )
)
)

and it turned out:

Bingo! It is exactly doing what I was looking for!

Thank you all for helping me out! Highly appreciated !!! Now I have a much better understanding of AppSheet and feel more confident. Now I can present that we can transform this ultra difficult use case with AppSheet.
Now I can have a better sleep, so happy !

Top Labels in this Space