Skip to main content

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?

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:

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

Terms & Conditions