Using values from another table in a formula


#1

I have two tables:

  • On Call Roster
  • Holidays

I want to use the values from holidays in some formulas in On Call Roster. The Holidays are defined by Name and Date (no date range). The aim is to get them summed up so that I can use them as a parameter in the following function: WORKDAY(startDate, numDays, [holidays]).

I am mostly stuck in how I reference the fields from the other table, without linking them.


#2

This can only be done in AirTable by linking the records in some way


#3

So I’ve tried solving this a different way, but now I get a formula error with a description about what’s wrong. I have a lookup field that contains the following values (without quotes): 2019-01-01, 2019-04-22, 2019-05-1, 2019-06-10, 2019-07-21, 2019-08-15, 2019-11-01, 2019-11-11, 2019-12-25

My formula looks like this: WORKDAY_DIFF(From,Until,{Holidays}) .

Why does this create an error?


#4

I have no idea. I tested this method too and also got an error. I’m even having trouble with a plain text field and the WorkDay_Diff formula. Sorry I couldn’t help!


#5

Support came back with a solution. Behind the scenes, the values from the lookup field are treated as an array, so if you wrap the reference to the lookup field in ARRAYJOIN it works.