Airtable Free: Create Invoice between last invoiced date and date requested

[boken4]

Very green user here. I have Airtable Free, and I have four tables in my base: Work Entries, Clients, Projects, and Invoices. I built my base by modifying the one presented here https://blog.airtable.com/supercharge-your-client-management-with-automatic-invoice-creation/#base 

Ultimately I want to be able to print an invoice file from a record of my choosing in the Invoices table, but for now the problem is...

How do I create an Automation that will populate the new Invoice record I create with a form? I want to have Automation take just the Client I select and a date I select in the form, go to the Work Entries table and look for all records for that Client AFTER the last invoice date for that client (invoice dates are in the Invoices table), and then sum all the Billable Amounts (in $) from the Work Entries table, summing only those Billable Amounts that are both for the Client I selected in the form, and between the last invoice date and the date I enter on the form.

[TheTimeSavingCo]

Hmm I think the biggest issue is that we can't use a "Find Record" action to look for records with a dynamic date value

To get around this, I think I'd try:
1. In the Work Entries table, create lookup fields to grab:
  - The latest Invoice date for that Client
  - Date Requested on the latest linked Invoice
  - Client for the latest linked Invoice
2. In the Work Entries table, create a formula field to check whether that Work Entries date is in between the lookup fields for the latest Invoice Date and the Date Requested and create a view that filters on this formula field
3. In the Work Entries table, create a formula field to check whether the Client is the same as the Client in the latest linked invoice and add this to the view filter
4. Make the automation that triggers on the Invoice record creation find all the records in the Work Entries table and link them to the newly created Invoice record
5. Use a "Find Record" step to grab all the records in the view mentioned in step 2 and 3
6. Link them all to the newly created Invoice record
7. Use a rollup field to sum everything up

Should work I think

[ScottWorld]

Actually, the “Find Records” action can find a dynamic date range if you create formula fields to convert the date fields to numbers, using the DATETIME_FORMAT function and using the specifier ‘X’ to convert the dates to UNIX timestamp numbers. (Then, you’re technically searching on a number range.)

- ScottWorld, Expert Airtable Consultant

[Mike_AutomaticN]

Nice one @ScottWorld!! I think I never thought of that!
Thanks both for sharing :D.

Mike, Consultant @ Automatic Nation

[TheTimeSavingCo · Accepted solution]

Oooh that's neat!  That way we don't need the view for the filtering and can just use the Find Record directly

And so it'd be:
1. In the Invoices table, create lookup field to grab the latest Invoice date for the linked Client
2. In the Invoices table, create formula fields with DATETIME_FORMAT to output the latest Invoice date and Date Requested values as numbers with the X specifier
3. In the Work Entries table, create the same formula field from step 2 for the date value
4. Make the automation that triggers on the Invoice record creation find all the records in the Work Entries table where the value from step 3 is in between the values from the formula fields in step 2
5. Link them all to the newly created Invoice record
6. Use a rollup field to sum everything up

[boken4]

Thanks so much guys! After struggling through a couple more hours to understand how to read the configuration of an Automation, I have successfully followed this advice and the system is operating as desired!

Solved!