I want to use airtable as a CRM and activity tracking tool.
My logic is:
We are working on a project - project table
Each project contains 1-n project items - project item table, each record linked to a project
Activities are tracked on a project item level (date, effort, related project item) - activity tracking table, each record linked to a project item
Now i want to automatically create invoices for my projects for certain invoice intervalls.
For this i need to find all related activities (easy, also with an automation) and group them for each project item, in order to create the respective invoice items (tracked activities per project item within the invoice intervall).
For this I have created a seperate “invoice item table” in which i currently also have a seperate automation, that gathers all activities for the respective items within the intervall.
my core question is: can i combine and automate the necessary steps in a script?
Find all activity tracking records (for different project items) in my activity tracking
Create 1-n invoice items based on the found activities