Trying to add a looked-up value and DATEADD


#1

I am trying to a similar thing, but instead of adding an INTERGER for the multiplier in the DATEADD formula, I am trying to pick up a value form a table LOOKUP.

Have also tried with a FORMULA cell and it doesn.t work either.

I have different categories of appointments to book, that each have different duration and priorities

TIA,
Duncan


Due Date Formual Help!
#2

It’s the same, you just change the source of the value from a number to the name of your field:

DATEADD({Date Field}, {Lookup Field}, Unit).


#3

I’ve tried various fields and they don’t compute.

This works, but i need to look up a number of minutes from the separate table of APPT TYPE.

Duncan


#4

You’re right, I’m testing it and I am getting weird behaviour:

  • If I make a formula like Lookup + 5, I get a number, so the Lookup field acts like a number.
  • If I use it in the DATEADD function it does not work :dizzy_face:
  • Using VALUE(Lookup) does not work
  • The only way to get it to work is summing with a 0: DATEADD(Date, Lookup + 0, 'days'). Note the + 0.

I think this is a #bugs to note to @Airtable_Support


#5

thanks, that worked. I guess it is a simple bug
D


#6

A naked Lookup is returned as an array — and predicting how Airtable will treat an array when used in a formula is definitely a black art. Fortunately, you can always cast a Lookup into a string by appending ‘&''’ – that is, an ampersand followed by two quotes signifying an empty string — which can then be used in VALUE() or similar functions.

You could also define {Appt Finish} as a Rollup field using an aggregation function. To do so, configure it as a Rollup that follows the link given in {Appt type} to roll up the {Nbr of Minutes} [or whatever you call it] field. Then, in the place where you would ordinarily enter an aggregation function, enter

DATEADD(
    {Appointment Time},
    VALUE(values&''),
    'minutes'
    )

values is a keyword indicating Airtable should use the rolled up value of the specified remote field. It, too, is returned as an array, so it has to be cast as a string and then converted to a number using VALUE().

Why might you want to do this? Well, in this case you aren’t saving very much processing cost or eliminating much complexity. However, it does reduce your field count by one, in that you no longer need explicitly to look up the ‘minutes’ value and then use it in a formula field, as the remote reference and the formula both take place in the same field.


#7

Thanks so much @W_Vann_Hall … only had to change the minutes to seconds as the original table outputs seconds it seems. One less column to squeeze on the screen.

D


#8

Any thoughts on…
I have, in some cases, multiple collaborators that can perform a certain appt type, in the {Appointment Book} i want to limit a field to allow only ONE collaborator to be selected dependent on the appt type.


#9

As far as I know, nothing in Airtable supports such granularity regarding collaborators. (Actually, Airtable currently isn’t big on conditional choices of any kind.) As a work-around, I’ll sometimes implement alerting on invalid choice combinations; while it’s still possible to specify a bad combination, an alert is raised to the user indicating something is wrong and needs to be fixed.

I define the alert field as the second field in the table — that is, the one immediately following the primary field. This places the alert at the top of any expanded record for increased visibility. It’s possible to use a single alert field to reflect multiple error conditions — even ones passed from other tables. (For an example, take a look at the [garment] table in my Wardrobe Manager base. Choose the <Development (all fields)> view in [garment]. You can hover your cursor over the :information_source: to show field descriptions for formula fields; I include the formula config in the description, so you don’t need to copy the base just to check the formulas.) Typically, I’ll use both emoji-based conditional coloring in the field itself along with (for Pro-level bases) record-level conditional color.

Again, not a perfect solution, but at least one that increases the likelihood invalid data entry will be caught and corrected at the time the error is made.


#10

And even if you aren’t significantly reducing processing load in this instance, the use of aggregation formulas in Rollup fields is a good trick to keep in mind because at times it can greatly streamline computation-intensive Lookup references. The case I most often site is that of my data deduplication routines: Switching to an aggregation formula-based algorithm for version 2 seemingly cut overhead on a 1,000-record table by the equivalent of four copies of War and Peace as compared with version 1…