Issue with ANY(Select(... formula

I have this formula:

ANY(Select([Related Peoples][Email],[Label]=[_ThisRow].[Training Contact]))

Which should return NULL if [Training Contact] is blank. Except it pulls in the first [Related Peoples][Email] record it can find and seems to ignore the Select() qualification rule.

This works perfectly

ANY(Select(People[Email],AND(
[Label]=[_ThisRow].[Training Contact],
[Company]=[_ThisRow].[CompanyID]
)))

But I’m stumped as to why the first one doesn’t work correctly. Hopefully its not something dumb…

I am not sure but you could try this:

ANY(Select([Related Peoples][Email],[Related Peoples][Label]=[_ThisRow].[Training Contact]))

I’m not sure @Bellave_Jayaram , I think this would cause issue

Things like this

have trouble some times. Your syntax looks solid.

[Email] is the Key, right?

No [email] isn’t the key. The key for the People table would be [PeopleID]

Try using that instead of Email.

ANY(Select([Related Peoples][PeopleID],[Label]=[_ThisRow].[Training Contact]))

How about this?

ANY(Select([Related Peoples][Email],[_ThisRow].[Training Contact]=[Label]))

Seriously, try it.

5 Likes

Really?!?!?! Just switch the arguments around and it will interpret it correctly?

Morpheus Show Me

1 Like

When using the is-equal-to (=) or is-not-equal (<>) operators, if the left operand is blank, the expression will be TRUE regardless of the right operand. So, yes, swapping the operands can make a difference.

My single biggest pet peeve of AppSheet.

5 Likes

Thanks for all your suggestions. So some feed back for @MultiTech_Visions @Bellave_Jayaram and @Steve

ANY(Select([Related Peoples][PeopleID],[Label]=[_ThisRow].[Training Contact]))
Using Matts suggestion of changing of changing [Email] to the key column [PeopleID] does not change the issue. But it does prove that this formula always returns the first value it finds in [Related Peoples][PeopleID] if [Training Contact] is blank

ANY(Select([Related Peoples][PeopleID],[Related Peoples][Label]=[_ThisRow].[Training Contact]))
Jays suggestion does not work because [Related Peoples][Label] is a list

ANY(Select([Related Peoples][PeopleID],[_ThisRow].[Training Contact]=[Label]))
Steves suggestion works fine. I think I’m a shocked about this as Matt is.

@praveen this is not a complaint, more of an ‘observation’

As someone’s who’s programming expertise does not extend beyond Appsheet, I think I’m well placed to make this comment. In order for Appsheet to be available to the masses, its important to remove any barriers. Having little quirks like this in Appsheet could stop a new user in their tracks. As a user with almost 3 years of experience of Appsheet, I understand how good the platform is and will diligently find a workaround or ask in this community. But there is the risk that someone who is new will try Appsheet, hit an issue like this and ‘bounce’ to a different platform.

I think the shortcut solution is simply to make people aware of any usual behaviour. In a former career I ran an IT support company who used some ticketing software called Connectwise. Which on the whole was very good. But now and again I tie myself in knots for hours trying to get some part of it working, usually because figures or time was not adding up correctly. What they do is to host user group meetings (https://www.connectwise.com/theitnation/share/agenda) at least twice per year in each location. The key thing I found out from these meetings was what didn’t work as expected so I didn’t have to waste time myself. Incidentally Praveen, I’m still open for a discussion to do something similar if your interested?

A short term solution would simply be to make users aware if this bugs in the help.appsheet.com files and also in a known bugs section of this community.

But a long term view should be to fix these quirks and make everything much more intuitive. Steve’s listed his pet peeve, here are mine:

1 = A virtual column showing a different result in a form view vs a detail view. I know this is because these are calculated via 2 separate bits of code. And they are calculated at different times. But I once spent a week on one formula because I had to take into account the order in which where calculated and which items were VC’s

2 = Having to specify 2 as 2.00000 in the code so that the result doesn’t get any random rounding. Surely if we set the column to 5 decimal places the backend code could simply change 2 to 2.00000 as a short term fix?

3 = I’m based in the UK. So for me, date is DDMMYYYY. If I put <<[Date]>> in a report I get DDMMYYY. But if I do <<CONCATENATE([Date])>> I get MMDDYYYY. To rectify this I have to do <<CONCATENATE(TEXT([Date]))>>

Hope this helps :slight_smile:

Simon@1minManager.com

1 Like

Just today, another little quirk…

I have a workflow with a start condition that wasn’t working. With this formula in a Virtual Column, it is an empty list:
Select(Booking[BookingID],AND(
[People].[Company]=[_ThisRow].[CompanyID],
[Valid Until]<=Today(),
ISBLANK([Superseded]),
ISNOTBLANK([Qualification Code])
))

Yet if I wrap it in ISNOTBLANK() and use it as a start condition for a workflow or report, it triggers

If change it to COUNT()>=1 then it does not trigger (the expected behaviour)

:thinking:

The use of CONCATENATE() here is confusing. Why use it at all?

This:

<<[Date]>>

and this:

<<TEXT([Date])>>

and this:

<<CONCATENATE(TEXT([Date]))>>

are all effectively the same thing.

@Steve it was just and example. You could do CONCATENATE(“foo “,[Date],” bar”) and the issue is the same :smirk:

The issue is the date formatting swaps to MMDDYYYY if you try to use a date column in any string of text

Hi @1minManager the basic response to your feedback is — you are right. These “quirks” are aggravating and unexpected. The problem we have had in fixing them is that they change semantics and fixing them to be “correct” also will break people who use them currently.

So we need to define a clean “new version” and have people opt into using the new version. As you can imagine with a product that deploys with incremental versions every day, this is tricky.

There are historical reasons for each of them, but this is not the place for them. The biggest issue is the inconsistent treatment of Value = in the backend vs in the app client.

As an aside, CONCATENATE() is used to construct computed keys. If the key values were dependent on the locale of the user, the same row would have a different key value if accessed from two different devices/browsers with different locales. That’s what led to this behavior (again, which cannot be changed without breaking thousands of apps that have computed keys).

3 Likes