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


#1

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.


Comparing two arrays and remove values that appear in both
New Formula Field Functions
#2

Just few words in addition. Since Artable does not provide a flexible reporting system I considered the mechanism of dynamic links to be the only way to maintain a small accounting application. For example, to consolidate figures with subtotals by periods or by categories, to organize a simple balance sheet etc.


#3

I too am in need of (or need helping finding, if it already exists) how to created automatically linked records. My example:
Column A = Company (text)
Column B = Date Purchased (date)
Column C = unique field created by formula concatenating A & B
Column D = Single select field - can choose either “stock” or “option”

I have 2 other tables, a ‘Stock’ table and an ‘Option’ table, each which also have the same concatenated unique field. What I want to happen is to have a conditional Column E that is essentially - if Column D = ‘stock’ then link to record in Stock Table where Column C = [Unique Column from Stock Table], if Column D = ‘option’ then do the same but from the option table.

Then I could hide this field and use lookup fields to pull in more data about each record. Is this possible?


#4

I would also support this feature request. If it exists, please point me to an explanation. If it does not, please add it.

Automatically created links also seem very promising for AirTable’s business modell. If one can add rows that link automatically, a lot of people will add rows much faster and thus need to upgrade their accounts.


#5

This is needed for so many reasons. I would also be able to calculate dates into work weeks then have a reporting table that shows the number orders in each stage by week.


#6

Yes it is very important feature I do not know if anything has been done in direction or not. But I also need the similar functioanlity


#7

Hi @Nick_Kosogor,
i see that you had asked this ques 1 yr back so don’t know how much its worth to you now but, you can use Roll field to calculate on linked table fields


#8

The issue is still important to me. However, the keyword here is “dynamic” i.e. a link to another table which does not exist until I calculate it using a formula.


#9

Note necessarily the answer you are hoping for, but as an interim work-around, you can do this:

  1. Define your tables as described in your original post.
  2. Define a linked records field tying the [Daily Expenses] table to the [Months] table.
  3. Enter monthly expenses as normal, but ignore the linked record for now.
  4. One you have entered the expense data, select the {MonthNo} cell in row 1.
  5. Scroll to the last row in the base. While holding down the shift key select the {MonthNo} cell in the bottom row. The message '## cells selected' should appear in the lower left of the screen.
  6. Press Ctrl-C to copy the cells. The alert '## cells copied' should pop up in the lower left of the screen.
  7. Scroll to the top of the base and select the {Months} cell (that is, the linked record cell) in row 1.
  8. Press Ctrl-V to paste the {MonthNo} values into the {Months} column. The alert 'Pasting...' should pop up in the lower left of the screen. Airtable will create links between the expense and months tables.

Yes, it is still a manual process — but it is not a manual data entry process, so the possibility for error is minimized.

From your description, it does not appear you make use of the linked record during data entry — that is, you do not seem to drill through from the [Daily Expenses] record to the [Months] record to enter data there; obviously, if you did, this approach would not work for you.

While I have not tested this, I assume this copy/paste routine could be performed on a record-by-record basis using Zapier. (In fact, as the copy and paste both take place on the triggering record, it could be run from a free Zapier account — subject to the tasks-per-month and time interval limitations of such accounts. If you are interested in pursuing such automation, the Zapier Guide contained in the [Documentation] table of my Wardrobe Manager base offers a step-by-step guide to defining such a Zap.


#10

Hi,
I have a solution to make this dynamic update.

Check out the demo here and read my comments on the video : https://www.useloom.com/share/c92154941b2346ddaf4e9e1587c001fc

This solution is based on a service that i have developed to trigger synchronization each 1 min or manually.
Check out my service here http://bit.ly/i-will-be-your-airtable-consultant-For-Setting-Up-Database-Forms-And-Workflows to contact me and get more details about this service.


#11

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!


#12

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).


#13

Also, check out this feature request:

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)