Cross-Referencing Tables

Hi guys,

I’m trying to cross-reference two tables within one base and add some automation. It seems like this should be relatively straightforward but can’t figure out a way to do, maybe overthinking it!

Anyway, one table (“Completed Reviews”) is dynamically generated when a person submits a Review form. When the form is submitted, an automation creates a new record in this table, and a Reference ID is generated (i.e., Ref Id) based on a formula that parses and sequences certain text.

In another table (“Assigned Reviews”) I’ve uploaded (from a csv) a single field that matches the Ref ID structure listed in the “Completed Reviews” table and have added a blank column (called “Completed”). What I’m trying to do is add a “1” or a “Yes” or some other signifier to the “Completed” column when a Ref ID is created in “Completed Reviews” that matches the one listed in “Assigned Reviews” Ref Id field.

image

Any help would be greatly appreciated!

Thank you!

The method I propose is not so straightforward, but it’s the one I could devise (not tested):

  1. Make ‘Ref ID’ the key field of ‘Completed Reviews’ (as it should be)
  2. Create a linked field in ‘Assigned Reviews’ to ‘Completed Reviews’
  3. Make field ‘Completed’ a formula: IF (Number = linked-field, “Yes”, “”)

This way no special automation is needed - database’s own automation does the job.

Side note: Advise Minnie that her assigned review is being inaccurately informed: an underscore is missing. I suspect her review will never be marked completed.

Maybe a simple automation can do the trick?

“when record created” trigger > “find records” action > “update record” action

I tried both proposed solutions but not having luck.

Re: Grunty’s formula approach, when I link the field in “Assigned Reviews” it wipes out all the uploaded data except for what is already a match to “Completed Reviews”…therefore, I don’t think this works on an ongoing basis?

Re: Databaser, for some reason, when I tried to insert the “Find Record” for the Ref ID field, I’m not able to do this properly. Am wondering if this is because it is a formula?

image

image

At any rate, I ended up just making it work via a Zapier integration with Google Sheets. Appreciate the help, though!

Yep, that will be the problem…