Valid if guestbook app expression help

[KerryIBCLC]

Hi All,

I've got a simple guestbook app that captures a first name and phone number

If the last 3 digits of your phone number matches any one who have registered today it won't validate so you cannot login twice.

However if two people with different names have a phone number ending in the same 3 digits it won't allow validation for the second person trying to register today.

An example is that Ken has registered already today and so cannot register again:

Sarah has registered before, but not today and has the same last three digits of the phone numbers:

Sarah should be able to register.

I need to expand the valid if expression to evaluate the first name column as well but my attempt at this is not working.

Guidance would be most appreciated. 🙂

Thanks.

This is the valid if expression I'm using on the "please confirm the last digits of your phone number" field:

AND(
NOT(IN([First name], SELECT(LLL guestbook[First name], 
TODAY() = [Meeting date]
)))
,
NOT(IN([_THIS], 
SELECT(LLL guestbook[Matched phone numbers], 
TODAY() = [Meeting date]
)))
)

[Joseph_Seddik · Accepted solution]

You should use:

ISBLANK( SELECT(guestBook[whatever],
  AND(
     TODAY() = [meeting date],
     [name] = [_ThisRow].[name],
     [number] = [_ThisRow].[number]
  )
))

But what would you do if you have two persons with the same first name and last 3 digits registering on the same day?

[KerryIBCLC]

Thank you!  That works exactly as it should.  

So the use of 

ISBLANK( SELECT(guestBook[whatever]

 ...is really just to act as a mechanism to allow us to define the multiple select row conditions needed?

Clever!

As regards if multiple people turn up with the same names and phone numbers I'd say I'd be pretty unlucky.  Its a pretty small meet in real life and we only keep their data on file for a year. 🙂

[Joseph_Seddik]

Yes, with SELECT we benefited from the ability to combine the filtering conditions on a per-row basis, while the expression on the original posts performed matchings on the whole table. 

Just out of curiosity, why don't you just use unique information like the email address or the full phone number? 

[KerryIBCLC]

Anonymity mainly, and also that it was super easy to write the expression to chop the phone number down to the last three digits.  😁

If two people with the same name have registered before they are presented with the last three digits of each persons phone number, this allows them to identify their record (so we can create a new row for the registration today) but with anonymised data.

It's simple but its very effective.

Thank you for your help again!

[Joseph_Seddik]

Welcome and thank you. Since you already have the full number, you can still show only the last digits to the user, while matching the full number in your expressions.