I have been trying to brainstorm a way to report on all the orders in my Airtable system. I would like to be able to pull reports based on some time measurement (weeks, months, just today, etc.). The best way that I can think of is to have a script that creates a “week record” in another table and assign orders to a week. I would then use those week records to report on.
This mostly works, but it feels very chunky. Are there any better ways of doing this?
Hi Carson! This is a great idea and you actually don’t need scripting to get what you want. You can use Formula Fields in the Orders table that convert each order date into reporting periods like week, month, quarter, and year. Then you can build filtered/grouped views and Airtable Interface dashboards off those fields. I always love using formulas because you can really tailor them to what you want and it makes organization so much cleaner. For example, if you want to do weekly reporting, you can have formula fields that label records with “Week of 5/11”, “5/11-5-15”, or just a first day of week label like “5/11”.
Scripting can be really really useful and powerful, but it seems for your use case a formula field would be cleaner and easier. I hope this is helpful!
+22Yep, formula fields can provide an easy way to group records together for this. Even simpler than writing out a SWITCH formula, you can use DATETIMEFORMAT to automatically format out a date into it’s component day, week, month, and year. The specifiers are in this support doc. I often include formulas to group by year and month at least to provide easy grouping and metrics.
+32Could also consider using a pivot table in Interfaces too as it gives you a week option:
If you need to reference the data with an automation or API call etc then having a dedicated table for it’s the cleanest way I reckon
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
