AnsweredAssumed Answered

How do I auto-fill fields based on part of another field that is compared to fields in another table?

Question asked by mlberk on Feb 21, 2018
Latest reply on Feb 23, 2018 by beverly

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.

=INDEX(Sheet2!$C$2:$C$2000,(MATCH("*"&RIGHT(A2,4)&"*",Sheet2!$A$2:$A$2000,0)))

 

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.

Table 1

Badge NumberOfficeDesk
111HQ01
112HQ01
111HQ02
112HQ02
111HQ03
111BL12
3421BL12

Table 2

DepartmentFloorOfficeDesk
HQ011stR01D01
HQ022ndR01D01
HQ033rdR01D01
HQ042ndR01D01
BL121stR05D01

Outcomes