Pulling information from one table based on Single...

[Shannon_Bradley]

Hello amazing people! I am working on my film workspaces and trying to build a table to use for a Page Designer for a Call Sheet. I have several tabs I need to pull details from, all based on the Date Field. This Scenes tab is one of the places to pull from, and it has a Filming Date field.

I am pulling IN to this Call Sheet tab

What I need is if the Date on a record in the Scenes tab matches the Date in this Call Sheets record, I want the records to link. I will be using a number of associated lookup fields from the containing records.

I assume I will need to use a Formula? I have trouble wrapping my head around those for some reason 😖 I've tried various other options and none will allow me to just say "If this date matches that date please link" and I can't restrict to a view because I would need to change that parameter for every new call sheet.

I hope this makes sense! If anyone has any suggestions I would really appreciate it!

[Kenneth_Raghuna · Accepted solution]

It won't be a formula. Here are two methods I can think of:

• Method 1 (Simplest): Instead of a using a date field that you directly assign the value for, replace "Filming Date" on the "Scenes" table with a link directly to "Call Sheets." Then create a lookup field titled "Filming Date" that looks up the date from the "Call Sheets" table. Alternatively, you can make the record name on the "Call Sheets" into a date field. Then the linked record name would be the actual date.
• Method 2: Create an automation that watches for updates on the "Filming Date" field. Anytime it is updated, have it search for record with a matching date in the "Call Sheets" table, then have it link the records.

Let me know if you'd like me to explain either of these further.

[Shannon_Bradley]

Oh...my....goodness!! I had been staring at it so long it never occurred to me to look at it from the other direction!! Thank you so much, this solves so many things and let's me actually streamline a few other things. This community is absolutely fantastic ❤️ 

[Kenneth_Raghuna]

No problem!