Automation for tracking weekly spend of inventory

[Dynamic]

Not sure if this belongs in automations or airtable.

I want to create an automation which creates a new entry in a separate spreadsheet which is being used to track spend for inventory in my business.

Ideally it creates a new entry, one for each week.

When a new item is added to the main buying spreadsheet, the cost of that is taken by the automation and added onto the spend for that week. Even better if it can take it away from the spend if a refund/return is taking place.

Is this possible? Sounds quite simple. If anyone can give me some advice where to start to build this, I would greatly appreciate it.

[jsep]

You can definitely set up this automation in Airtable to manage your inventory spend tracking. Here’s how you could structure it:

1. Trigger: Set the automation to activate whenever a new record is added to your main buying spreadsheet.

2. Actions:

   • Find Record: Use the "Find records" action to locate the existing weekly spend record based on the date or week number. If the record does not exist, proceed to the next step to create a new one.
   • Create Record: If no existing weekly spend record is found, use the "Create record" action to initiate a new record for that week’s spend.
   • Update Record: If a weekly spend record is found (or after creating a new one), use conditional blocks to determine if the new entry is a purchase or a return. Update the record by adding to the spend for purchases or subtracting for returns.

3. Conditional Logic and Scripting: Consider using a scripting block if your logic for updating the spend based on purchases and returns becomes complex. This will allow for more flexible handling of data and ensure accuracy.

If you need help setting this up or would like to discuss further details, I offer a free 15-minute consultation call. Feel free to schedule one with me to get started.

[TheTimeSavingCo]

Check out this other thread here, seems like you're trying to achieve the same thing: https://community.airtable.com/t5/base-design/sum-fields-in-a-table-based-on-week-number-in-another-...

If so, yeah, just follow the steps there and you should be good to go.  As long as your refund/return records have a negative value to indicate it's a refund the rollups will work just fine too