Validate based on an other tables value

[Mirtl]

Hi there, I'm trying to solve the following problem since a few days:

I have a table named "Bookings" where people can checkout and check in items within the company.
The second table "Items" stores all available Items of the company and a column which stores the current state (wether the item is available or not). 

In the booking table they have two options to do bookings:
1st. via text (which is simply a reference to the Items table.)
2nd via QR Code (which gets the ID of the item and sends it to a parsing column)

I'm facing the following problem:
Data should only be valid if:
the column [check in/check out] from (current)Bookings Table = "Check OUT" and the column [current state] from Items Table = "available" 
else if
the column [check in/check out]from(current)Bookings Table = "Check IN" and the column [current state] from Items Table = "not available" 

For the REF Text Insertion the following List selection works (via auto compute but also this needs validation too):
IF([check in/check out] = "Check IN", FILTER("Items", [current state] = "not available"), FILTER("Items", [current state] = "available"))

But within the QR-Code thing it obviously just inserts nothing without throwing an error which is a problem.

Thank you in advance! 

[WillowMobileSys]

Sorry!  It is not clear to me what issue you are facing.   Can you please explicitly state what the problem is?

[Mirtl]

Hey @WillowMobileSys I've just want to validate a QR-Code Scan Value based on a value in another tables row. 
I drew a diagram to show what I'm trying to achieve.

 

[WillowMobileSys]

i am not sure where you are struggling but there is no real magic to using QR codes.

First, make sure the device you are using to test with has the ability to scan QR code turned on.  For this, go into the camera settings on the device  to find the setting.

Once you have the device scanning QR Codes, all the app will do is take the text from the scanned code and place it into your column.  That's it!  From there, you would perform the validation like you would with any other column just as if the user manually type in the value.   

Maybe you validate against a list of known possible values from a table.  Maybe you validate the accuracy of the text itself.  Whatever you would have normally done for validation if a user entered the value, that is what you would implement in the column for the scanned QR Code.

[Mirtl · Accepted solution]

Thanks for your replies.
However I just kept trying and this snippet finally did the job:

IF([Check in/Check out]="Check in",
AND(
IN([_THISROW].[Scan], Items[ID]),
IN("In Use",
SELECT(Items[Booking_State],([Item Parse]=[ID]))
)
),
AND(
IN([_THISROW].[Scan], Items[ID]),
IN("Free",
SELECT(Items[Booking_State],([Item Parse]=[ID]))
)
)
)

[RS2020]

Not a specialist, but I think you need to calculate the stock in google sheets database OR and use a function expression in Appsheet.  "Calculate new values from existing ones using expressions."

For example: If available stock - lent out  = <0 then you can set a value in the database to 1. You can use this conditional value in Appsheet (Use the Expression assistant) to disable a button or change the name of a textfield. Also check https://support.google.com/appsheet/table/10104782. 

Expression in Appsheet can be found in the 'actions' tab. 
Please read this article: https://support.google.com/appsheet/answer/10104642

I am not a specialist but I hope this brings you a bit further to your answer. 

[Mirtl]

Hey, thanks for your answer but thats not what I want to achieve. 
As I wrote I want to validate a QR-Code based on REF Value from another table. 

Thank you anyways!