I'm trying to enter information that will end with specific characters (Table 1::Badge Number). These characters need to compare to another table (Table 2::Department). In Table 2 there is a list of Department records, each with different information across multiple fields. Table 2's information will already be imported and set.
Essentially Table 1 will have bulk information being imported from a .csv and upon import will need to compare the last 4 digits of the Badge Number to the list of Departments in Table 2. If the last 4 digits match one of the Department fields in Table 2 then it will need to auto-fill the corresponding fields over to Table 1, respectively.
In Excel I am able to accomplish this with a formula similar to the following.
I'm trying to get a similar script made for the "Badge Number" field upon import. The setup below is similar to what would be imported (Table 1::Badge Number). It will reference Table 2::Department for the last 4 digits, no matter what it begins with.