Check if value exists in table

MauriceWhelan · 11-07-2019 04:45 AM

Hi.

I have 2 tables i.e. Product_Master_File and Operations_Master_Check both with a common column [Product Code]

The [Product Code] must be created in Product_Master_File before it should be entered into Operations_Master_Check so could someone maybe advise on an expression that would prevent a user entering a [Product Code] into Operations_Master_Check if it doesn’t exist in Product_Master_File yet.

Thank you.

Steve

In Valid if:

NOT(IN([Product Code], Product_Master_File[Product Code]))

MauriceWhelan

Hi @Steve

Thank you for that. When I enter the non existant product code into my Operations Master Form [Product Code] column I am not getting an error, however, if I manually change an existing record in the Operations Master Check table to one that doesn’t exist in the Product Master File table and run a test in the expression assistant it seems to work.

Might I be missing something?

Steve

Whoops! I inverted the logic. The operations product code is valid if the same product code exists in the product master:

IN([Product Code], Product_Master_File[Product Code])

MauriceWhelan

ha ok thanks @Steve . I was thininking that after my last response so I tried it and it seems to be working now when scanning a QR Code.

I have a couple of follow up queries if you could help.

The expression is returning a drop down list of products from the [Product Code] column in the Product_Master_File Table. 99% of the [Product Code]'s entered in the Operations_Master_Check table will be entered by scanning a QR Code but there will be the odd [Product Code] that will have to be entered manually because the QR Code is damaged for example. Is there a way to hide the list of product codes from the Product_Master_File table and still allow the user to enter a [Product Code] manually in the Operations_Master_Check table for products they cannot scan in?
I have an existing Valid_If expression in the [Product Code] column in the Operations_Master_Check which prevents editing a record if it is marked as ‘Rejected’. So I added your expression to the existing one with an OR expression but they don’t work together, only individually. Is the following expression not viable even though the Expression Assistant is ok with it?

OR(
IN([Product Code], Product_Master_File[Product Code]),
LOOKUP([_THISROW].[Product Code],“Operations_Master_Check”,“Product Code”,“Master Reject”)<>“True”
)

Thankyou.

Steve

Yep! Disguise the IN() expression to avoid the magical behavior:

OR(FALSE, IN([Product Code], Product_Master_File[Product Code]))

or

AND(TRUE, IN([Product Code], Product_Master_File[Product Code]))

I’d think you’d want AND() instead of OR(): the product code must exist in the master file AND it must not be rejected there.

AND(
  IN([Product Code], Product_Master_File[Product Code]),
  LOOKUP([_THISROW].[Product Code],“Operations_Master_Check”,“Product Code”,“Master Reject”)<>“True”
)

MauriceWhelan

Thanks a million @Steve.

MauriceWhelan

Hi @Steve.

Just in relation to the IN() expression on this thread i.e. OR(FALSE, IN([Product Code], Product_Master_File[Product Code])). I have tested it in 2 different scenarios, one works fine but the other doesn’t.

The issue I am having is with the following scenario.

First user (Account 1) on Smart Device scans the QR Code and creates the [Product Code] in Product_Master_File table.
Immediately afterwards a second user (Account 2) on another Smart Device scans the same QR Code to create the record in the Operations_Master_Check table.
In this scenario the Valid_If constraint is valid, however, the second user is being presented with the ‘Product Not Found’ error message.
If I carry out step 2 as the First User or sync the app as the second user and then carry out step 2 I don’t get the error message. All seems fine.

In this scenario, i.e. 2 different users with 2 different accounts submitting a Product_Master_File and then an Operations_Master_Check record for the same [Product Code], would the second user have to sync the app after each Product_Master_File record is created by first user? If so is there a way around this in the setup maybe?

Hope that makes sense.

Thanks.

Steve

The behavior you’re seeing is inherent in how AppSheet operates: each device has its own copy of the data. Changes a user makes are to the copy on the device. It is only when the device syncs that the changes are made available to other users. Even then, other users must themselves sync to pull down those changes.

Typically, syncing occurs frequently in the background (unless you’ve configured it otherwise), but “frequently” is not “immediately”. In cases like the one you’ve described, it’s best for the user who’s added to the Product_Master_File to manually sync after their addition, then the other user manually sync to see the addition. Otherwise, the second user will just have to wait for the background syncing process to work itself through.