Hello. Part of the app I am building requires...

(Peter Teo) #1

Hello. Part of the app I am building requires me to check that a client receives no more than one token a day. I have built a column that combines the client ID with the today’s date =CONCATENATE([ID], DATE(TODAY())) so I have a unique text in that column that indicates that the token was consumed today. What I thinking is to use the editable field in a column to check if CONCATENATE([ID], DATE(TODAY())) exists, If it already exists the field is not editable and no token will be issued. If it does NOT exist that it is OK to click YES and and issue the token.

In short is there a quick way to check if a text CONCATENATE([ID], DATE(TODAY())) exists in a column and return a TRUE or FALSE? Thanks

(Peter Teo) #2

Problem solved I think. I’ll use CONTAINS([Test Column],CONCATENATE([ID], DATE(TODAY())))

(Aleksi Alkio) #3

Check this article and the section “Preventing Duplicate Field Values”… help.appsheet.com - List Expressions and Aggregates List Expressions and Aggregates help.appsheet.com

(Peter Teo) #4

@Aleksi_Alkio Thanks. You are right CONTAINS([Test Column],CONCATENATE([ID], DATE(TODAY()))) does not work because it includes the current field prior to saving. I did look at the link you sent but I don’t understand how NOT(IN([_THIS], SELECT(Customers[State], NOT(IN([CustomerId], LIST([_THISROW].[CustomerId])))))):


(Peter Teo) #5

I used

NOT(IN([_THIS], SELECT(Customers[State], NOT(IN([CustomerId], LIST([_THISROW].[CustomerId])))))). It does work and it displays a message to say the data is invalid but it allows the data to be saved. There was one instance when it did not but that was just that one instance. Not sure if I did something to different.