Have row data from data table auto update each day

[Pete_Wilson]

Hello Airtable experts,

I’m trying to solve the issue below and tried with links etc but my scripting knowledge is limited. In excel I have multiple columns. Date the student is set to graduate, today’s date. The difference between the two that calculates the ROW. And the message pulled from the second student tab. Is there a way to do this within Airtable to auto populate each day with the correct messages from the second table?

[Williams_Innova]

Hi @Pete_Wilson,

This is a great question and yes, I believe what you want is doable in Airtable. Let’s dive into to make sure.

1. First, the date calculation is doable using a formula field. There are a few different date formulas you can use so it will depend on your preference (calendar days, week/working days, etc.). In Excel, I use the following formula to get the same result, which is the difference between the two date in calendar days. This makes be assume you want calendar days to show in the column E or “row.” Can you confirm this?
   a. 5/26/2022 - 12/30/2022 = 147
   b. Assuming the above is correct, this will be done in Airtable by using a formula field and the formula DATETIME_DIFF([date1], [date2], ‘units’). Also, you can reference the Formula field reference page here.

2. The second part of this is still doable, albeit a bit more tricky. I’m assuming you have date difference ranges that live in the students sheet. The same can be accomplished using a series of if statements, either in a formula field or in a script. The tricky part will be how many conditions you have to set up. I’ve approached this a few different ways, to build if statements, and what works best for me is writing them out in notepad first or writing smaller versions of the if statement to get it working in a separate field, then nesting them in the main one. Obviously, there are pros/cons with using nested if statements.

Hope this helps.
Chris

[Pete_Wilson]

Hi William,

Thank you so much for getting back to me so quickly! Question #1 is confirmed and is working for me, and #2 helps, and I will try to do this as you suggested.

Appreciate it!

[Williams_Innova]

Hi @Pete_Wilson,

Thanks for the confirmation! Let me know if you need any help with the formulas.

Chris