Base Design for tracking customer history

[oreocereus]

We run a weekly vege bag program, and I need some help scaling up and improving our record keeping. 

Members can get their vege bag delivered for a fee, or pick it up.

We ask members to commit to being a delivery or pick up, but there are always a few who need a one-off delivery each week.

This creates 2 checkbox fields:

1. "regular delivery"
   and
2. "one-off delivery"

On vege-bag day I create a view that filters only "one-off delivery" and "regular delivery" to create my delivery list.

However, for tracking payments, we need to be able to see a history for each weeks' "one-off-delivery" (to make sure the delivery fee is being paid), so simply unchecking the "one-off delivery" doesn't work that well. 

However, creating a new field each week with "Delivery 30 march" and then a new view which filters "regular delivery" and "Delivery 30 march" is cumbersome and prone to human error.

This might seem fairly menial, however there are several other pieces of information I need to be able to track similarly. Such as collection confirmation, donations of vege bags (members who can't collect one week can donate their produce through us), payment confirmation, notes - these are all temporal pieces of information but creating 50+ new columns for a season is messy, cumbersome and ultimately very prone to human error.

Basically I need to be able to see relevant info each week, have it cleared for next week, but keep a record of last weeks info.

Does anyone have any thoughts on streamlining this?

[TheTimeSavingCo]

If I were you I'd have a table where each record represented a single Customer <> Week pair, which would look something like this:

And to power it I'd have an automation that ran once a week to look for the customers that were active, and it would create one record per active customer in this table, does that make sense?

[Dan_Montoya]

It sounds like you want to create a billing event when "One off delivery" is checked.  I would use an automation that creates a charge in your table that tracks chargeable events.  Then your monthly invoices will include these one off charges.

[oreocereus]

Ah yes, that's something like what I was thinking, I just wasn't sure how to automatically create a table each week with 75 customers (scaling to 150 in the next 6 months, hence the need to sort this out sooner!).

So, basically you're suggesting:

1) A customer table that has all the basic customer info

2) A weekly table that is automatically populated once a week

I didn't consider that I could automate "active customers" into a new table!

I think that makes sense, and should hopefully work!

[oreocereus]

Thank you, that could also work! 

[TheTimeSavingCo]

Yeap pretty much! 

If you find that you don't need to key in data for individual week <> customer pairings much or want to keep the historical data, I think you should use Dan's suggestion instead.  It has far fewer records created which would help with managing the record limit, and at 150 customers, you'd end up with 8k records created per year, most of which wouldn't serve much purpose you know what I mean?