Hello Airtable Community! I need help figuring out an easy data entry model for our YOY production reports.
The basics: We have ~150 clients and ~75 suppliers (every year some clients leave, some are added, which is true for suppliers as well, meaning those lists are not static). Our clients sell products from our suppliers. For every year, our suppliers report sales for each client, which we collate into an individual sales report per client (I need help with reporting too, but that's a problem for another day). So each record in the production table is something like this:
| (linked to Client Table) | (linked to Supplier Table Sync) | Sales Year | Sales Amount |
| Client A | Supplier 1 | 2023 | $1,000 |
| Client B | Supplier 1 | 2023 | $0 |
| Client C | Supplier 1 | 2023 | $5,000 |
| Client A | Supplier 2 | 2023 | $500 |
| Client B | Supplier 2 | 2023 | $10,000 |
| Client C | Supplier 2 | 2023 | $0 |
| Client A | Supplier 1 | 2024 | $750 |
| Client C | Supplier 1 | 2024 | $1,000 |
Other info: The client table and production table are two separate tables/tabs in one base (client base), and the supplier list is synced in from the supplier base.
The problem: in order to make these entries, I have to create a new record for each of our active clients (~150), then populate that record with one of the supplier names (which I can easily copy down to populate each client), then enter the sales. Then, to enter sales for the second supplier, I have to create ~150 new records for each of those same clients, select the second supplier, and then enter those sales numbers. I would have to do this for each of the ~75 suppliers which is essentially hand creating 11,250 records per year.
Excel method: We have been doing this in Excel. I have a separate Excel worksheet for each year with that year's client list running down column A and each of our supplier running along the top in 75 rows.
| 2023 Sales | Supplier 1 | Supplier 2 | Supplier 3 |
| Client A | $1,000 | $500 | $2,000 |
| Client B | $0 | $10,000 | $200 |
| Client C | $5,000 | $0 | $10 |
Solution?: Is there a way to auto generate the same client list multiple times in the same production table such that I would just need to enter the supplier and sales amount? Or even better, auto generate the entire list of 150 clients 75 times with the client name and supplier filled in such that I would just need to enter the sales amount?
Caveat: I'm looking for a relatively simple, sustainable method. The more complex things are, the more there is to breakdown, and I will need to hand this off to other employees who would need a simple data entry platform.
Thank you for any suggestions you might have!
