Link a record based on a date range

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

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

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.

Idk why its titled formula field reference but heres the basics

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

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