Dynamic (calculated) link to a record in a linked table

[Nick_Kosogor]

How to create a dynamic (calculated) link to another table?

There is a Table#1 (Daily Expenses) [ID, Date, Sum, subject,MonthNo].
Where MonthNo=MONTH({Date})

There is a Table#2 (Months of the year) just as follows:

MonthNo MonthName
1 January
2 February
3 March
etc.

Linking Table#1 to Table#2 will allow me to calculate monthly expenses directly in a Table#2 by means of Lookup(Sum) function.

How can I do this? If I choose a standard Link field then every time later I will have to select a MonthNo for each record in «Daily Expenses» manually instead of linking automatically. Manual input is such cases is unnecessary and harmful action, since it can often lead to mistakes.

[Michael_Gillman]

I ran into a wall when trying to determine project costs per month.

Since the project rates per role can vary each month, I need to dynamically select the record containing the rate applicable to a given month.

Without the ability to understand dynamically determine which record to link, it Airtable just isn’t a good fit when modeling the status of a project, team, or company that changes over time.

Please correct me if I’m missing something!

[Matthew_Billio1]

You should make this a feature request! Great idea.

It could provide a decent workaround for airtable’s lack of queries.

It would also solve this issue: the fact that users cannot make LookUp fields only show unique links (again, if your suggestion were implemented, then users could construct a formula to get via linking to multiple records via formulas).

How can I make a lookup field show only unique results? Ask the community

The result I am looking for is similar to the ARRAYUNIQUE function for a Rollup field. However, the results are a link to another table, and it seems that using a rollup can only show text results as opposed to clickable records. How can I make lookup field show only unique results, or make the unique results of a rollup field clickable?

[Matthew_Billio1]

Also, check out this feature request:

Comparing two arrays and remove values that appear in both

it would be nice to have access to LOOKUP(table, field, linked-records-field) and ROLLUP(table, field, function) as functions within a formula field as well.

Ingredients Table has single-select field = {Kind}
Sandwiches Table has linked records field = {Ingredients}
In Sandwiches Table, show all Ingredients of {Kind} = “Vegetable”:

ARRAY_EACH({Ingredients}, variable, IF(LOOKUP(Ingredients, {Kind}, variable) != "Vegetable", SUBSTITUTE({Ingredients}, variable, "")))
= #for example [Tomato, Lettuce, Avocado]

The above feature (quoted from Jeremy_Oglesby’s response to a feature request) would provide us with a more general solution.

(another related request is found here Array intersect formula, but I prefer how Jeremy_Oglesby’s request included not just one formula request, but also related formula’s with his request)