Reference Row Not Populating

[colodev]

I have several tables that reference each other, and for the life of me I cannot figure out how to get the referenced data to populate in my data source. Here’s an example:

So - on the Days table, the Opportunities info populates no problem:

But I cannot get the Position information to populate. I have tried:

And I’ve removed that and tried:

Which matches what I did with Opportunities, so I’m not sure why it’s not working.

Here’s the backend:

This is happening on another table as well, and I just don’t get it. Days are “part of” Opportunities, but not Positions and Positions are not “part of” Days, so I don’t know if that’s the problem? And, if so, is it impossible to get data to show on another table if it’s not a child or parent?

[Shourya_Tomar]

Hello @colodev

The Problem: Indirect Reference

Your data structure is:

1. Days (Child) → Opportunities (Parent)

   • The Days table has a Ref column that points directly to the Primary Key of Opportunities. This is why [Opportunities].[Opportunity Name] works.

2. Opportunities → Position

   • The Opportunities table has a Ref column that points directly to the Primary Key of Position.

3. Days →(x) Position

   • The Days table has no direct Ref column pointing to Position.

You cannot directly use a formula like [Position] or [Position Name] in the Days table because those columns don’t exist in Days.

The Solution: Dereference Chain

To access data from the Position table while you are in the Days table, you must follow the chain of references.

You need to tell the system: “Go from Days, jump to the linked Opportunities row, and then from there, jump to the linked Position row.”

The correct expression to use in your virtual column or initial value for the Days table is a Dereference Chain (using the . dot operator):

Days → Opportunities → Position Data

1. Identify the Reference Columns

• In your Days table, you have a Ref column pointing to Opportunities. Let’s assume this column is named [Opportunity].

• In your Opportunities table, you have a Ref column pointing to Position. Let’s assume this column is named [Position].

2. The Final Expression

To get the Name of the Position from the Days table, the formula should be:

{[Opportunity] . [Position] . [Position Name]}

• [Opportunity]: This jumps from the Days table to the referenced row in the Opportunities table.

• . [Position]: This then jumps from the Opportunities row to the referenced row in the Position table.

• . [Position Name]: This finally pulls the data from the Position Name column of that Position row.

  I hope it helps!

[Steve]

I can’t figure out what you want to accomplish.

[colodev]

I’m confused. The Days table has references to both Opportunities and Positions - so why can’t I populate the information directly?

[colodev]

In my data table for Days, I want it to populate with the key for the associated Opportunity and Position, which are two different tables. I have references to both from the Days data table. However, Days is a child of Opportunities, as is Position. Can I not call the Position key into the Day table because they are not in a child/parent relationship even through they reference each other?

[Steve]

@colodev

In my data table for Days, I want it to populate with the key for the associated Opportunity and Position,

Here?

Use these:

App formula for Opportunity: ANY([Related Opportunities])

App formula for Position: ANY([Related Position Details])

[colodev]

Unfortunately, that isn’t working.

When I test it, the expression result is good

But it is still not populating in views nor the sheet

[Steve]

That’s because App formula expressions of normal (non-virtual) columns are only recomputed when you edit or otherwise change the row. With those expressions in place, open a row in a form view and save it (no changes needed). The newly-saved row should then have values in those columns.

Do you strictly need the values in the spreadsheet? If not, make those two columns virtual columns and they’ll get updated automatically.