Link a record based on a date range

[Daniel_Ordonez]

Hello,

I have a table that shows deliveries with pickup dates and delivery days plus the date someone summitted it to be invoiced. I also have a Invoicing schedule table that has date ranges so anything summited within those dates will be invoiced together in one invoice which already has a pre-established invoice number.

At the moment I manually look at the date summited and link it to the invoice record it should be a part of. I would like to automate this process where Airtable will automatically links the invoice that has the submit date with its range.

Thanks,
Danile

[Rebecca_Elam]

What are you invoicing? like what type of product and who do you sell it to?

[Daniel_Ordonez1]

We run a small long hall trucking company. Each week a driver will do pick up and deliveries that are logged in Airtable.

Each week we create what we call Settlement Statements. This it’s not really an invoice but a statement for the driver of all the loads he did for that week and any deductions such a fuel, insurance etc.

Drivers are paid weekly and settlements are 1 week behind.

The date that the driver submits all the needed paperwork (RateCon, BOL, etc) is the tied directly to an established settlement schedule.

An example:

1/1/21 - 1/7/21 is Week 01 settlement for 2021. Anything submitted between those dates we’ll be settled in settlement 2101 and be paid on 1/15/21.

In the delivery log I have a settle id field which would like to 2101. At the moment I have to manually look at the date it was submitted and see where it lands on the settlement schedule (which is already in Airtable for the whole 2021 year). The input into the settlement iD column on the delivery look.

Ideally I would like it if there was way for Airtable to automatically add the settlement ID by using the settlement schedule to determine which settlement id it will fall under.

[Rebecca_Elam]

Idk why its titled formula field reference but heres the basics

Loom

Formula field reference – Airtable Support

[Rebecca_Elam]

sorry i forgot my monitors are so big. heres the formula i used to output “statement”
IF(WEEKNUM({LOAD DATE})=WEEKNUM(TODAY())-1, "STATEMENT")

[Rebecca_Elam]

please feel free to ask questions ill answer in another loom video

[Alicia_Bethel]

Hi there,

I’m looking to do something similar but I feel may be slightly different.

I have a table called SITRep and all it is is a list of every Monday of the year. I have another table called Tasks. That has tasks with “Start Date” and a “Calculated End Date” as well as another marker called “Key Event” (It’s a checkbox)

What I need is to be able to have airtable automatically link a task record where “Key Event” is checked AND “calculated end date” falls within 2 weeks before the date on the SITRep table and not create a new record each time because I’ve already created the list, and this has to be done weekly on Mondays.

Use case - Leadership has to be notified of Key Events 2 weeks prior to go live, but the SITRep Admin has to review what’s being submitted prior to sending to LT. It is also the case that a task can appear on the SITRep notification 3 weeks (Mondays) in a row.

[TheTimeSavingCo]

Hi Alicia, hmm, if I were you I’d do that via an automation I think.

I would make an automation that would trigger when Key Event is checked, and it would then:

1. Find records in SITRep where their Date value is within 2 weeks before the Calculated End Date value of the triggering record
2. Update the triggering record by putting the record IDs of the records found in the previous step into the linked field that links SITRep and Tasks

This would do the links automatically whenever a task is created with Key Event checked. I’m not entirely sure what you mean about creating a new record each time though, sorry!

Let me know if you have any questions!

[Alicia_Bethel]

Thank you so much!

I am hitting a snag on the last step…

The error is “Field “Include in SITRep” cannot accept the provided value: Could not find matching rows for string.”

[TheTimeSavingCo]

Hi Alicia, hmm, that’s odd. I’ll DM you about this so that we don’t end up bothering everyone else in this thread