Skip to main content

Zapier monthly tree purchase

P
Forum|alt.badge.img+7

I had no idea what to call the subject for this post.....

I want our company to donate £5 per 100 confirmed hours worked on site, to purchasing trees via ecologi.

On the 5th of every month, I want the airtable to add up the total number of confirmed hours worked in the roster for the previous month.

This number is to be divided by 100 with the result being x.

x may be rounded up (optional at this stage), then the total amount is to be sent to our ecologi account using a webhook to zapier.

Any leads onto how best to sum up the previous months totals please?

Pro's and cons to watch out for?

    2 replies

    A
    Forum|alt.badge.img+2
    • AdamM
    • New Participant
    • 1 reply
    • February 4, 2025

    There may be easier ways, but this is how I think I would approach it.
    Create a Table "Tree Donations"  with the only field being "Date" and it is a string.

    Go to your hours table and add 2 fields.

    - Date as String (Formula field that is configured like below)

    - Tree Donations field (Linked Record field that points to the Tree Donations Table. 

    Go back to the Tree Donations table and add 2 new fields

    - Hours Worked (Rollup field that is configured like below)

    - Amount to Donate (Formula field that is configured like below)

    Then you need to create 4 automations

    Set Tree Donations Value on Creation

    Set Tree Donations Value on Update (This makes sure the linked record is correct when the date field is updated)

     

    The last 2 automations I can't create because I am using a free version and it would require a script.
    - Create an automation that runs the 1st of every month that will create a new record in the Tree Donations table for the current month. Script should be something like this

    var months = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December']; var date = new Date(); output.set('date', months[date.getMonth()] + ' ' + date.getFullYear());

    Then use the output value to create a new record with the date as the name field.

    - Create an automation to send the amount calculated from the previous month to ecologi. Probably need to use the same script above to return a value, but instead of the current Date do the current date - 1 month. Then use find records to find the record with that name. Get the amount you need to send and call their api to send it.

    Here are what the 2 finished tables should look like:

    TheTimeSavingCo
    Forum|alt.badge.img+28
    AdamM wrote:

    There may be easier ways, but this is how I think I would approach it.
    Create a Table "Tree Donations"  with the only field being "Date" and it is a string.

    Go to your hours table and add 2 fields.

    - Date as String (Formula field that is configured like below)

    - Tree Donations field (Linked Record field that points to the Tree Donations Table. 

    Go back to the Tree Donations table and add 2 new fields

    - Hours Worked (Rollup field that is configured like below)

    - Amount to Donate (Formula field that is configured like below)

    Then you need to create 4 automations

    Set Tree Donations Value on Creation

    Set Tree Donations Value on Update (This makes sure the linked record is correct when the date field is updated)

     

    The last 2 automations I can't create because I am using a free version and it would require a script.
    - Create an automation that runs the 1st of every month that will create a new record in the Tree Donations table for the current month. Script should be something like this

    var months = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December']; var date = new Date(); output.set('date', months[date.getMonth()] + ' ' + date.getFullYear());

    Then use the output value to create a new record with the date as the name field.

    - Create an automation to send the amount calculated from the previous month to ecologi. Probably need to use the same script above to return a value, but instead of the current Date do the current date - 1 month. Then use find records to find the record with that name. Get the amount you need to send and call their api to send it.

    Here are what the 2 finished tables should look like:


    Hmm, instead of a script, what if we just used an automation to paste the 'Date as String' value into the linked field instead?  That would create the record per month automatically:

    How the automation gets triggered would depend on how the data's keyed in though, and I'm running with the assumption that the hours are logged via a form

    Link to base

    Adam, Consultant @ The Time Saving Co

    Reply


    Most helpful members this week

    Terms & Conditions