Any w/ select and conditions.

[Aplicaciones_Hu]

Hello everyone, this time I bring a question regarding one of the apps that we currently create with appsheet.

The question is that we have two tables, "Shift" and "Signature". In the signature form it is automated to bring the signatures with a lookup and this expression:

ANY(
SELECT(NEWKM[CREW OFFICER],
AND(

([SHIFT] = [_ThisRow].[SHIFT]),
([MOBILE] = [_ThisRow].[MOBILE]),
([ROUTES].[DATE] = TODAY())
),
TRUE
)
)

The problem arises when there are 2 employees on the same shift trying to sign, this expression gets confused and returns an erroneous signature or erroneous data regarding the person who is signing.

For example, Employee1 loads his data, when he is going to sign this expression brings his signature automatically.
Employee2 uploads his data minutes after employee1 has done it, and when he is going to sign the app returns the signature of employee1.

At the moment we have enabled a dropdown menu so that in case the employee sees a signature that is not his, he can change it automatically with this menu. But we want to make this automatic.

We already tried with autoincremented columns to assign unique numbers to each data load per employee, but when adding this condition in the above mentioned expression, it stops working correctly.

Any idea how to solve this?

[WillowMobileSys]

You will need to adjust your SELECT() to filter based on the "current" employee accessing the information.  You can get the logged in employee from the USEREMAIL() function.  You can then use it to filter the rows further.

For example, if your NEWKM row has an [Employee] or [User] in which you are storing the employee email you could ADD to the filtering something like this:

ANY(
SELECT(NEWKM[CREW OFFICER],
AND(
...
[Employee] = USEREMAIL(),
...
),
TRUE
)
) 

[Aplicaciones_Hu]

Thank you for your response. Yes we have already tried but the problem is that in this app, employees do not use their own email, but is shared by each department, so each email address covers 10 employees. In this specific app, employees must log in with their department email and within the app select who they are.

For this reason we had to attempt to filter by data entered by users and the USEREMAIL can only be used to filter views by department.

I have tried variations of this expression to see if I could make it bring me the information correctly. I added conditions using values from other columns in related tables. But it's still not specific enough to filter correctly.

For example

ANY(
SELECT(NEWKM[CREW OFFICER],
AND(
([SHIFT] = [_ThisRow].[SHIFT]),
([MOBILE] = [_ThisRow].[MOBILE]),
([ROUTES].[DATE] = TODAY()
([NOVEDADESKM].[Hora de incio]> TIMENOW()-8) ////I tried here to vary with other columns and data of the tables related to each other///.
)
),
TRUE
)
)

It should be added that the number of columns in each table is 10 per table, "Shifts" and "Signatures".

Unfortunately in this app we do not have the possibility to use "USEREMAIL()" to filter with the select in this way, so we are forced to use ANY+SELECT which would be the same as a conditional lookup.

[WillowMobileSys · Accepted solution]

Understood. 

It still remains that to solve your problem, you need to be able to differentiate between each user. 

When using ANY(), the FIRST item is returned from the list.  Without using some user identifier you cannot control which row is returned.

You mentioned that each user identifies themselves after logging in.  THAT column needs to be used for the filtering.  You will need to incorporate that user identifier column into your NEWKM rows (if not already) and then you can add filtering using that column.

_________________________________________

As a side note, do understand that sharing email addresses is not advised as it is less secure.   It is also unknown what ramifications you might run into in YOUR app when several end users are submitting data simultaneously under a single user login.  It all depends on the implementations throughout the app.