How to cross reference multiple tables?

Ask the community
#1

Hello,

New to AirTable, we’re using it to run some data asset surveys.

We are looking at a way to cross reference two tables like so:

We have following tables:

  • Data Assets
  • Data Elements
  • Data Processes
  • Data Asset Survey
  • Data Processes Survey

Moreover:

  1. Data Asset Survey links multiple Data Elements per Data Assets.
  2. Data Processes Survey links Data Elements and Data Assets (source of data element) independently.

Objective:
We want to be able to check that Data Elements linked under a Data Process Survey record indeed exist for the Data Assets declared in the same record. This could be done:

  • either by filtering input choices of Data Elements based on declared Data Asset, or vice versa,
  • or by adding some extra column that will verify that the Data Element - Data Asset relationship indeed exists.

Not sure if this is explained clearly.

In other terms, if we associate A and B to record C, we need to check whether the A-B relationship already exists, to either limit input or to throw a warning after input.

What do we need to do to achieve that?

#2

Hello,
Try adding a lookup Data Element (from Data Assets) in Data Process Survey then create a new formula field where you will use FIND({Data Elements},{Data Element (from Data Assets)}) formula that should return the position of the first found Data Element in Data Element (from Data Assets). The only bug in that solution is that if you do not have a value in Data Element this formula will return 1

#3

Hi again,

I just asked a fiend about the bug mentioned in my previous reply and it could be solved if you used this formula IF({Data Elements},FIND({Data Elements},{Data Element (from Data Assets)})) instead of the one mentioned in my previous post.

Hope that this do the trick, best of luck

#4

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.