OK, I need some enlightenment here. his may have been answered elsewhere and I did browse a bit, but most cases were more complicated than my own. (I come from an MySQL and MSSQL background)
PART A
I have a single “Songs” table in my app’s DB. I have a field called Seq# (to sequence a list of songs for a performance set). I don’t want the user to be able to enter a Seq# that’s already assigned to another song. So I got the following SELECT() working:
Not( In([_THIS], Select(Songs[Set #],[Row ID]<>[_thisrow].[Row ID], FALSE)) )
However, I don’t understand why a statement like the following would NOT work:
Not( In([_THIS], Select(Songs[Set #],[_This]=[_thisrow].[Set #], FALSE)) )
The second example “reads” much better IMO, (note the = vs <>) but is [_This] not available to the Select statement?
I also tried the condition part as “[Set #]=[_Thisrow].[Set #]” which did not work either? In this case, is [Set #] returning the current rows searched Set #?
PART B
Is there any sandbox or console app that you can use to test out to see just how AppSheet logic works and what it returns? I’m thinking like a JavaScript Console.
PS: It’s late here in Ontario Canada, so I’m hit’n the hay, but I’ll check in tomorrow. Thanks in advance!!
One way is to write it like NOT(IN([Seq#],Songs[Seq#])). It checks that there isn’t any Seq# in that table already. Though if you need to update the record later, you should add that condition as well to your formula.. for example like..
OR(
[Seq#]=LOOKUP([_THISROW],[ID],Songs,ID,Seq#),
NOT(IN([Seq#],Songs[Seq#]))
)
1 Like
Thanks for the response @AleksiAlkio - I can over-think AppSheet logic, coming from the programming world. 
A simpler solution may sometimes work!
However when testing (from the expression editor) I was getting thrown off! I had two records set with the same Set# but when creating a new row, of course there is no other row with the same Set# yet! DUH!
Your results gave the same as my other SELECT statement, so before saying it didn’t work, I investigated a bit more. Your logic is so simple it seemed it had to work! 
So instead of looking at the test results, I saved it and tried it in the app and it works fine! Soooo… I also plugged back in my 2nd SELECT statement above, and lo and behold, it works also!! So I need to beware of testing outside the expression editor and in a real app execution!
To that end, and to answer PART B above, I also noticed that the little blue flow-chart icon is clickable and gives some better output as far as debugging goes! So this is helpful!..
Good to hear it’s working!
1 Like
Actually @AleksiAlkio it’s not working! Once the value is written to the row and I go back in to edit, I says it’s already used. So it only works on a new record, but not in editing! So there is only one row with Set# 6 in my data, yet I get this)…
OK, I’m back to the original SELECT as it does work. I guess it really does “read” OK. I need to be sure there is no other record with the same Seq#, but ONE is allowed and OK. 
You have probably written it differently. I made a quick test and for me it works just fine
1 Like
It was literally like this:
NOT(IN([Set #],Songs[Set #]))
However, I’ve messed with it so much maybe I didn’t do a save in between or something. I’ll test again more carefully later. I’ll leave it as the best answer as I like the simplicity and elegance. 
The 1st part was missing and the OR(), that’s why it didn’t work properly.
1 Like
Oh! I don’t think I realized you updated the answer - at least I didn’t notice the OR’d statement - or maybe I saw it when it was only partially complete. I’ll give it another shot!
UPDATE: Yes Sir, that’s work just fine for me as well. My final code is:
OR( [Set #]=LOOKUP([_THISROW].[Row ID],"Songs","Row ID","Set #"),
NOT( IN([Set #],Songs[Set #]) )
)
