I have used different relational databases in the past, very new to Airtable. The solution I seek is to automatically lookup data from a large table matching the value of one field in a newly created record in a separate table. I tried using linked and lookup fields for this without success, cannot figure out how to make this work.
Schema: 2 tables.
Table 1 (inventory) has a list of all parts ordered for the project. One field is called “inventory number” populated with an alpha-numeric code. This table has a few thousand records.
Table 2 (receiving) has a field for the scanned bar code, creation date-time, user, and “inventory number”. This table is only populated via the Airtable mobile app for the bar code scanning feature.
Workflow (via mobile app)
User opens Table 2 (receiving), presses the + sign to add a record.
User scans the bar code. Bar code data populates the bar code field.
User looks at the “inventory number” field on Table 2 to check that matching data was pulled from Table 1 (inventory) after the scan. User checks that this data matches the bar code number.
Note: The inventory number in Table 1 is simply the bar code number. Bar codes are unique across all records in Table 1. The user performing the bar code scan never looks at Table 1, only working with Table 2.
Workflow: The receiving dock person will be scanning bar codes very frequently as packages arrive. After each scan, the user verifies that the bar code is valid. If not valid, they scan a different bar code on the package until a valid record is created. The cross table lookup must be automatic, cannot have user scroll through multiple records to manually set the relationship. They simply create record, scan, and check that it’s a match; must be very fast.
I addition, I would like Table 1 to pull the receiving information (the record creation date) from Table 2 without manual user intervention. … got this to work.
How does one setup Airtable for this automatic cross table relationship without having the user manually set this? Is there an undocumented LOOKUP() formula that can perform this task?