Monitoring certain records value on a regular schedule

[Mikko_Mantere]

In my work, we manage project-tasks in Airtable and project-billing is based on completed tasks (billing in another system). Each task therefore has a monetary value and the person responsible for the project predicts task revenue date by entering the revenue forecast in the date field. The predicted monetization months may extend even up to a year from present day, and as the projects progress, the forecast month may change several times. We have many projects running at the same time and each of which may have several billable tasks predicted for the same date (month)

I would like to create a table where would automatically create record the predicted value of each future month on a given day of the month. The value in this new record must not change after the record has created, but on the corresponding day of the following month, a new record is automatically created with the current monetary value of the same forecast month.

So, in long term, I can monitor the change in the value of each month, until all the tasks has been billed

Base has two tables, projects and tasks. The tast records are linked to a Project.

For this monitoring, I have thought about different implementation models, but so far, I have not found a working solution.

Any ideas?

[TheTimeSavingCo]

Hm, if I were you I’d set up a third table called Predictions or some such and have an automation that would trigger once per month per record in the Projects table that would create a new record in Predictions along with the monetary value

It would trigger once per month per record via a formula field that would check whether a Predictions record needed to be created for that month or not

[Mikko_Mantere1]

Thanks Adam!

The third table is exactly my idea. I just haven’t been able to build a workflow that would gather the tasks of the same month into one summary record and add up the amount of money predicted for that month.

In that record, I would like to see e.g. the following code information.

• Number of billable tasks
• The total money amount of billable tasks
• How many projects are the tasks related to (link from the task table to the project table)

[TheTimeSavingCo]

Hm okay. Could you share an example of your data as well as an example of how you would want the end product to look from said data?

Would love to help but don’t have enough details I’m afraid

[Mikko_Mantere1]

Hey,

We ended up using a script as part of the automation. The script adds up the money and number of the rows that meet the criteria, creates a new row “Predictions” in the table and records the result in the created row. Naturally the script writes also date and few other details.

We realized that this goal cannot be achieved without writing a script.

Unfortunately I can’t share views to Base or data, but I’ll try to figure out a way to demonstrate this solution. I think others may need it too.