Populating Table A with Table B Information


#1

I have spent time reading through the forum and can not find the answer on how to achieve this or if it is even possible. Any direction would be greatly appreciated.

I have a client who wants their evidence room inventoried. They can provide a .csv of everything that is suppose to be in the property and evidence room. Roughly 17,000 items. All the items in the property room have a barcode on them that matches a field in the .csv import. We will have people that will go through the property room and scan the barcodes on each piece of evidence into Table B. After we are done we want to be able to report several things.
What did not get scanned?
What got scanned that was not on the imported inventory .csv

Here is what I have tried to set up.

Table A - Imported Inventory - This table will be populated with the data imported from the .csv file. It contains a field that will match the data that will be retrieved from the barcode when scanned into Table B

Table B - Scanned Inventory - This table has two fields. The information that is scanned in from the barcode and the date and time the record was created.

What I would like is that when an item is scanned into Table B it will look up in Table A to see if that record exists. Then populate a field in Table A indicating that the item has been inventoried. I could then create a view that filters out all items that have been located leaving me with the items that have not been located.

In addition would it be possible to have something populate in Table B if when the item is scanned and there is no corresponding record located in Table A

Thanks in advance for any guidance anyone can provide.


Comparing cells across tables
#2

Hi Brian

I can’t immediately think of a way to do this in Airtable alone - however it would be quite straightforward to do with Airtable / Zapier:

  1. Link the two tables together
  2. Set up a Zap triggered by a new record in Table B (i.e. when an item is scanned)
  3. In Step 2 of the Zap Search for a record in Table A with the same barcode
  4. If found, update the link field in the record (in Table A) to link to the Table B record (from Step 1 in the Zap)

Having done this, it will be easy to identify new Items without matching table A records - and vice versa - with filters and/or grouping.

Hope this helps.

Julian


#3

Thanks that worked perfect.


#4

Great - and you’re welcome!