Not understanding Select() and ways to test/debug

[KGingeri]

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!!

[AleksiAlkio · Accepted solution]

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#]))
)

[KGingeri]

---

@AleksiAlkio wrote:

NOT(IN([Seq#],Songs[Seq#]))

---

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!...

[AleksiAlkio]

Good to hear it's working!

[KGingeri]

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.  🙂

[AleksiAlkio]

You have probably written it differently. I made a quick test and for me it works just fine 🙂

[KGingeri]

---

@AleksiAlkio wrote:

You have probably written it differently

---

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. 😊

[AleksiAlkio]

The 1st part was missing and the OR(), that's why it didn't work properly.

[KGingeri]

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 #]) )
  )