Re: Creating projected payments

855 1
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

Hi there - I’m a bit stuck if I can do this with Airtable and definitely can’t get it to work with Zapier, so wondering if you can give some insight…

Here’s what I’d like to have happen:

  • Member joins program and chooses a 6-month payment plan
  • Their 1st payment gets imported into AirTable
  • 5 automatic records for their subsequent monthly payments get created so I can track projected revenue

I thought I could do this in Zapier by using the date modifier (of adding +1mo to the current payment date), and although it imports, the date is left blank.

Is this a formatting issue? Any other ways to do this with Zapier?
Screen Shot 2022-06-13 at 4.53.26 PM

Can I do this with Airtable instead? Trying to keep it as simple as possible!

4 Replies 4

I’m sure you can do it with Zapier, but my guess is that “+1mo” is not a valid way to do date math in Zapier.

I don’t use Zapier much so I can’t really guide you there without spending time researching it, but I know that you would do this very simply in by using their “addMonths” feature.

In the first screenshot below, I’m adding 1 month to the date field that I selected. And in the second screenshot below, I’m adding 2 months to the date field that I selected.

Screen Shot 2022-06-13 at 9.46.52 PM

Screen Shot 2022-06-13 at 9.46.58 PM

You could also do this in Airtable using Airtable’s native automations, but since Airtable’s automations can’t do math at all, you would need to first create 5 different formula fields (each one representing a different date in the future), and then you would hide those formula fields on your grid, but you would refer to those hidden formula fields when creating your new records.

In Airtable, you can add months by using the following formulas:

To add 1 month:
DATEADD({Date Field}, 1, 'months')

To add 2 months:
DATEADD({Date Field}, 2, 'months')

To add 3 months:
DATEADD({Date Field}, 3, 'months')

+1mo came straight from Zapier documentation, but it does not import properly to Airtable. Thanks for the tip on the other tool - hoping to keep the ones I have without adding another.

Hi @Quinn_Tempest
Do you need 5 rows(records) created or just 5 other date due and payment amount fields?

You have the Join date, so you just need to build formulas off of that date field. DATEADD({join date}, 1, ‘month’) then repeat that DATEADD({join date}, 2, ‘months’) and so on. Put a payment amount next to each date field and you can add a formula to sum the payments per row.

You might want to let the Zapier people know about this. It might be a problem with their Airtable modules that they need to address.