Best way to have a base-wide 'constant' variable?

[Michael_Norris]

Hi there

I’m a beginner to Airtable, but am impressed by the promise of it. To start with, I’m seeing if I can use Airtable to help my event management. Every event has more or less the same list of to-do items, but of course the due dates change depending on one single constant: the date of the event itself.

So I’m trying to set up an Airbase base that includes a bunch of to-dos with a ‘weeks before event’ column (e.g. 4 would mean 4 weeks before the event date), and then another column that calculates the actual due date using DATEADD.

But what’s the most elegant way to define this event date as a constant across the whole base? My current solution, that feels a little clunky, is to define an ‘Event information’ table, which includes the date of the event itself. In the ‘To do items’ table, I then include a column with a Lookup in it, to get the event date from the other table. This column is then used by the DATEADD formula column to work out the To do item’s due date.

Is that the best way to do it, or am I missing something? The main drawback of this method is that whenever I create a new to do item, I have to remember to populate the Event Date column of that record, because Airtable doesn’t currently have an autopopulate function.

It would be more elegant if I could simply include a link to the event date in the ‘Event information’ table as part of the formula, without having to use a Lookup column in the original table, but I can’t see any way to do that.

Thoughts?

[HammerOz]

Hi

"The main drawback of this method is that whenever I create a new to do item, I have to remember to populate the Event Date column of that record, because Airtable doesn’t currently have an autopopulate function.”

But isn’t your lookup auto populating the date (rollup field form the event) once you select a linked event?

This sounds like the way I would do it… for what that may be worth!

[Michael_Norris]

Yes it is, but because there’s only ever one event per base, I don’t want to have to select the event each time I create a new to-do item.

[W_Vann_Hall]

I suspect, if you look at any of the last dozen or so bases of mine, you’ll find just such a construction: Somewhere, there’s almost certainly a table containing only one record, and that record will be linked to all the records in at least one of the other tables. Typically, this is for one or more of the following reasons:

• in support of ‘global’ constants
• to get around Airtable’s prohibition against performing lookups or rollups against fields of the same table
• to create a centralized junction table through which data pass to and from other tables, rather than requiring each table to link to every other table

To date, I’ve not found any way around this mandatory link. However, I do think I’ve found a way to minimize its intrusiveness while providing an easy visual confirmation the link has indeed been made.

If you’ll take a look at this base, near the center you’ll notice a column of check boxes labeled Aggregate.They don’t look like Airtable checkbox field checks, as they use a different emoji character; they are also slightly blue-tinted, indicating they are actually linked records. More precisely, they are many-to-one links between each record in the Daily Closing Price table and a record in the Aggregation table with the name ‘ :white_check_mark: ’.

Once I’ve defined the Aggregation table. I paste a ‘Heavy White Check Mark’ emoji – :white_check_mark: – into the Name field of the first record. I then delete any other records from the table so that only :white_check_mark: remains. Once I return to the Daily Closing Price table and begin data entry, each record I add — as you noted — must be linked explicitly to Aggregation. Doing so takes no more than two mouse clicks a few seconds apart.: As you enter the new DCP record, when you come to Aggregate, click once on the plus sign (’+’) to create a link record and pop open a window to the Aggregation table; click a second time on the sole existing record, ‘ :white_check_mark: ’, to establish the link and close Aggregation.

Admittedly, it’s more work than no work — but it’s not all that much work. And the use of ‘ :white_check_mark: ’ for the record’s does make it easier to tell which records haven’t been linked, while at the same time it helps declutter the DCP table visually. As workarounds go, it is respectively unobtrusive.

[Charles_Beckett]

This is SO incredibly useful! Thanks, you made my day!