Help

Re: Need Help: Easy Data Entry for Complex YOY Production Reports

Solved
Jump to Solution
225 0
cancel
Showing results for 
Search instead for 
Did you mean: 
SPerry1
5 - Automation Enthusiast
5 - Automation Enthusiast

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 YearSales Amount
Client ASupplier 12023$1,000
Client BSupplier 12023$0
Client CSupplier 12023$5,000
Client ASupplier 22023$500
Client BSupplier 22023$10,000
Client CSupplier 22023$0
Client ASupplier 12024$750
Client CSupplier 12024$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 SalesSupplier 1Supplier 2Supplier 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!

 

1 Solution

Accepted Solutions

Oof, yeah, you're right about it not working as your client list is >100.  Yeah, switching it around seems like it'd work fine

See Solution in Thread

4 Replies 4

Try using a repeating group automation: https://support.airtable.com/docs/repeating-groups-of-automation-actions

I would do this by:
1. Create a checkbox field in the "Supplier" table called "Create client list" or something
2. Trigger the automation off of the checkbox field being ticked
3. Use a "Find Record" action to find all the records in the "Clients" table that are in the client list and use that as the repeating group list
4. In the repeating group, have a "Create Record" action that will create one record per found client, and link it to the supplier record that triggered the automation

And so to create the client list for each supplier you can just tick all the checkboxes in your supplier table, does that make sense?

Thank you, I will give this a try - may take me some time to go through it. I did see in the repeating group automation info you linked, that there was mention of a 100 record limit in "Find Records". With my client list being 150, I'm assuming that won't work. Maybe if I went the other way and built the repeating group based on my 75 supplier list.

Anyway, thanks again, I'll test this all out.

Oof, yeah, you're right about it not working as your client list is >100.  Yeah, switching it around seems like it'd work fine

SPerry1
5 - Automation Enthusiast
5 - Automation Enthusiast

Thanks TheTimeSavingsCo. I hadn't delved into the automations at all previously, so your recommendation pushed me into it. I've gone a very simple route of creating a check box trigger to create a record of "Client" + "Year", which I can quickly trigger one-by-one. Then I manually select the first supplier and drag down. At that point I can add the sales figures. Lather-rinse-repeat.

Thank you so much for introducing me to the concept of automations!