Re: Field Populated by Rotating Sequence of Fields from other Table
It’s not simple — but I’m not convinced it’s as complicated as I seem to have made it. If anyone can suggest a more elegant solution, please chime in!
There are several reasons this isn’t a slam dunk. First is the way Airtable handles linked records. If each day’s meals are kept as an individual record in one of 7 (5?) separate tables, then each record in your [Menu] will have to be linked to each record in all 7 (5?) tables. Second, Airtable doesn’t really support persistent data, so there is no easy way for a formula to ‘know’ the last time it executed, Meal X was chosen, so this time it should select Meal X+1. Third, Airtable has no mechanism for conditional retrieval of linked records. That is, there isn’t really a way to say ‘if today is Tuesday, retrieve Tuesday’s menu selections.’ Instead, you would need to retrieve menu selections for all days — for every record in [Menu] — and then choose from the appropriate list.
So, then, am I saying it’s impossible? Not at all, but the solution is, as they say, somewhat non-intuitive.
Here is a demo base that does what you want. I’ve changed the architecture somewhat, for reasons I’ll explain, but it allows you to specify 10 meal entries for each day of the week and then parcels them out over a 10-week period so no individual meal is served more than once every 10 weeks.
That’s a read-only link; copy the base to your own workspace in order to examine the formulas in use.
—not that doing so is likely to help you much; I doubt I could reverse engineer the main formula to arrive at the underlying algorithm, and I wrote the damn thing. I’m hoping if I describe briefly how the base works, though, the code might not be quite so opaque.
There are three tables in the base: [Menu], [Meals], and [Join]. [Menu] is the main table, but I’ll discuss that last. [Meals] represents my main alteration to your design: Rather than having each day of the week’s meals stored in a separate table, with each meal saved as its own record, I instead built a table of meals, where each record represents an individual day of the week. Each record contains 10 individual data fields, here labeled {Item1} through {Item10}, each of which holds a single meal entry. There’s also a field, {DayNumber}, containing that day’s number for a Sunday – Saturday week.
More importantly, there is a field, {DayMeals}, that concatenates all of the meal entries for that day, prefixed with an index value, into a single string, with the vertical bar character (’|’) used as a separator between entries. For instance, here’s {DayMeals} for Sunday. (The ‘…’ indicates where I’ve removed entries to save space.)
|1.0:Sunday meal 1|1.1:Sunday meal 2| … |1.9:Sunday meal 10
The index value 1.0: means ‘Day 1’ — that is, Sunday — ‘Meal 0’ — that is, the first of ten meals numbered 0 – 9.
The remaining table, [Join], exists solely to roll up all 7 {DayMeals} fields into one long {MealString}. By doing so, we eliminate the need to link every record in [Menu] to every record in [Meals]. Instead, [Join] contains only a single record, with that record linked to every record in [Meals] and every record in [Menu].
And now is where it gets confusing… :winking_face:
The main table, [Menu], is actually pretty straightforward. It contains 4 fields, of which 3 are actually required:
-
{Date}is self-evident. -
{Link2Join}is the aforementioned link to[Join]that every[Menu]record must have. Whenever you add a record to[Menu], you have to link it to the single record in[Join]. This can be done the usual way (clicking on the plus sign within the field and selecting the record); but it can also be done by selecting and dragging the fill handle from an already-linked record or by copy-and-pasting the name of the record (in this case, ‘.’, the period character) from either an already linked field or from a text field. -
{DoW}isn’t necessary; I added it just to show which day of the week{Date}was during troubleshooting. -
{Meal}is a simple process embedded, alas, in a lot of ugly.
At heart, {Meal} takes the number of the day of the week plus a modula value to create an index value — e.g., 1.0: — and then feeds that value into a MID() function to extract the meal entry for that day from {Join::MealString}. That’s a pretty basic algorithm; in fact, if {MealString} and {IndexValue} existed as separate fields within [Menu],¹ you could write it like this:
MID(
{MealString},
FIND({IndexValue},{MealString})+4,
FIND('|',{MealString},FIND({IndexValue},{MealString}))-
(FIND({IndexValue},{MealString})+4)
)
There are a couple of things that make it messier in this implementation. First, I define it as a rollup field using an aggregation formula — essentially a rollup field combined with a formula field, which unfortunately obscures some of the variables involved. Second, to arrive at the ‘.#’ part of the index value, it performs modula arithmetic based on {Date}'s epoch day, which introduces additional INT(), VALUE(), and DATETIME_FORMAT() functions into the formula. And, finally, since it has to calculate the length of the string for MID() to extract on-the-fly, the rather ungainly formula that creates the index value is repeated three times in the final formula:
MID(
values&'',
FIND(
(DATETIME_FORMAT(
Date,
'e'
)+1)&
'.'&
MOD(
INT(
VALUE(
DATETIME_FORMAT(
{Date},
'X'
)
)/86400
),
10
)&
':',
values&'')+4,
FIND(
'|',
values&'',
FIND(
(DATETIME_FORMAT(
Date,
'e'
)+1)&
'.'&
MOD(
INT(
VALUE(
DATETIME_FORMAT(
{Date},
'X'
)
)/86400
),
10
)&
':',
values&'')
)-(FIND(
(DATETIME_FORMAT(
Date,
'e'
)+1)&
'.'&
MOD(
INT(
VALUE(
DATETIME_FORMAT(
{Date},
'X'
)
)/86400
),
10
)&
':',
values&'')+4)
)
My recommendation is that unless you have some pressing need to modify it, simply treat the formula as a black box: Copy and paste it into your base, and forget about it. Should you need to tweak it in the future, this post will presumably be here to reference.
One final point needs to be mentioned: Anytime someone wants a repeating cycle through a fixed number of options, I automatically turn to some sort of modula function — and if you look through all 7 weeks’ worth of sample data in the demo base, you’ll see that each menu entry for each of the 7 days occurs only once. However, they do not occur in numeric sequence; instead, for example, Friday’s meals occur in the series 1, 8, 5, 2, 9, 6, 3, 10, 7, 4. Each day follows its own pattern. If your original meal sequence contained some sort of internal logic (the way Peking Duck is traditionally followed by Duck with Bean Sprouts and ends with Duck Skeleton Soup), you may need to modify your sequence of meals accordingly.
- Actually, you could implement the base with
{MealString}and{IndexValue}as discrete fields within[Menu], but at a cost of increased complexity and additional processing cycles.
Normally it’s quite difficult to tell how many items are in a series. So to see if this is the third or fourth Monday in the schedule takes some complicated linking. But if you want to just see which where a particular Monday places in the year is quite easy with:
WEEKNUM({date})
You can tell what day of the week it is with:
WEEKDAY({date})
And since the week number isn’t very helpful on its own. Let figure out where is in a series of ten:
MOD(WEEKNUM({date}),10)) - 1
I added the minus 1 so that we could start at zero.
You can then combine this to get a unique ‘Weekday.SeriesNumber’ that can be used as a Meal_ID:
WEEKDAY({date}) & '.' & (MOD(WEEKNUM({date}),10)) - 1)
This will give you a number like ‘1.2’ which would be the second meal of the Monday Meals. So I created a meal table structured that way:
We can then link to this in our schedule:
Note you only need ‘f_Meal ID’ to figure out the ID number. The other columns are to show logic.
Each time you add a new date copy the ‘f_meal ID’ into ‘Meal ID’ and you’ll get the corresponding meal. Or copy the entire column over once you have all of your dates.
Hope this solution works for you. Let me know if you run into any issues.
