Ways to link tables without depending on Display column?

[Parsing_Ideas]

In my scenario, there are 4 tables in question which serve a single business process. I’ll try simplifying things by listing the table names ie “Table1”, followed by a list of columns.

Let’s assume the 1st column in each table is the Display column.

Table1 - ID, Email
Table2 - ID, Email

Table3 - Email, Name, etc.
Table4 - Email, Name, etc.

The way the continuity of the business process looks like is:

• Execute a child process that involves both Table1 & Table2. The ID column is specifically required for this child process to succeed.
• Execute a separate child process that involves Table3 & Table4.

Once the child process with Table1 & Table2 is finished, there’s no reasonable way to capture the ID value so that it can be used for the separate child process that involves Table3 & Table4.

Meanwhile, the “Email” column is a common column between all of them.

Is there a way to have columns or links between Table3 or Table4 to refer to Table1 or Table2 by using the “Email” column as a second de facto Display column? So I can pull data from Table1 into Table3 for example? And vice versa?

[Patrick_Kinsella1]

The answer is yes, but I think more explanation would be necessary to be able to prescribe anything specific.

The catch-all method is to utilize the Filter() formula. For example, if you wanted to reference Table1 form Table3, you’d have a formula that looked something like:
[Table1].Filter([CurrentValue].[Email]=[ThisRow].[Email])

[Parsing_Ideas]

To add to what you said, what if I wanted to retrieve the value from the “Country” column from Table1 to be displayed in a new column in Table3?

Would it be something like [Table1].Filter([CurrentValue].[Email]=[ThisRow].[Email]).[Country]?

[Patrick_Kinsella1]

Yes, that should work.

In your case, there should only be one row that matches the email, so it will only return one value, but keep in mind that filter can return a list of values if multiple rows match the filter criteria.