Monthly Sample Sizes
I have an app for tracking audits on classes and this year, they want to add a sample size for each class for each month. Sample sizes range from 5 to 20. Currently, I have a Class Catalog table in the app with the following fields:
- Quarterly sample size field that is a numeric, user-entered field.
- # of completed audits that is a look up summary field from the audits table based on the class has many audits relationship. Also, a # of audits completed this quarter field, with the filter of the date of the audit being in the current quarter.
- Percent complete fields that are formula fields based on the sample fields and the # of completed audit fields.
They want to change these quarterly samples to monthly sample sizes. I can change the # of completed audits this quarter to this month by changing the filter, but I'm trying to think of a way to update the monthly sample size dynamically. We've been manually updating the quarterly sample size since it was only four times a year, but going in every month on the first seems excessive and unrealistic in the long term. I'd like to set this up more dynamically, but I'm not sure how. No one goes in and edits these class records, or at least, no one does on a regular basis, so I can't use a "when data changes" automation or form rule.
I thought about having 12 fields; one for each month, but it would be nice if only the current month's sample size showed up on the form. But I'd still have to update the formula field to divide by the new month's sample size.
I also thought about doing an automation on a schedule but can't figure out how to modify the fields I need. Lastly, I thought about creating a sample size table and creating the automation from there, but can't get my head wrapped around how to do that either.
Do you all have any ideas on how to make this work without having to manually update the app every month?
------------------------------
Jamie Owens-Knight
------------------------------