Creating multiple records based on a range of numbers

[acealex123]

I’ve been working on a base with multiple tables as well as using some Zapier integration. Without going into too much detail, I want to create records in a table with records from another table, but with some updates. The two most important fields is the “ID” field and the “Events” field. Basically, if a user enters an ID and 5 as the number of “events” into the previous table, another table would automatically update with ID appended with 1-5. So 5 records each appended with numbers 1 through 5. For example:

Table 1:
ID: 123
Events: 5

Table 2:
ID: 123.1
ID: 123.2
ID: 123.3
ID: 123.4
ID: 123.5
(5 Separate records)

Is there any way to do this with airtable and zapier? I have tried many different types of zaps and even some scripting but I can’t figure anything out. Thanks!

[W_Vann_Hall]

If you can set a maximum number of events, it can be done in Airtable alone; alternatively, using the same base, a Zap can be configured to generate the records using standard Airtable functionality. (If you can’t predict the maximum number of events, you’ll have to go back to your original plan to have Zapier create each new record individually, as Airtable currently cannot handle open-ended loops.)

This base demonstrates the technique, allowing for automatic generation of up to 10 event records. The process is as shown in the following clip:

1. The user enters an ID and the number of event records desired (from 1 to 10).
2. The base creates a string of event IDs consisting of {ID} concatenated with '.' and the event number and separated by commas.
3. The user copies the string of event IDs by selecting the field and pressing Ctrl-C. (Airtable doesn’t support explicit, mouse-driven copies of an entire field, but that’s what’s going on in that section of the video.)
4. The user pastes the Event ID string into the linked-record field pointing at the [Events] table by selecting the field and pressing Ctrl-V. (Ditto.)
5. Airtable generates a new [Events] record for each ID in the string.

The copy/paste routine can be automated using Zapier. The demo base contains a view entitled <New Event IDs> that can be used to trigger a ‘New record in view’ Zap that updates the triggering record by saving the contents of {Event IDs} to {Link2Events}.

If for some reason you can’t have the events linked to the related {ID}, this method won’t work for you — but given your event naming convention, I suspect that’s not the case…

[acealex123]

Wow this is perfect! Thank you so much

[acealex123]

Also, if you could share the formula with me it would be greatly appreciated!

[W_Vann_Hall]

Oh, sorry: Use the provided read-only link to access the base. From there, click on the drop-down beside the base heading and choose ‘duplicate base’. Once a copy is in your own workspace, you’ll be able to see and edit formula fields. (I use shared read-only links and personal copies to keep from stepping on anyone else’s code and data and to make sure I’m not billed for several hundred collaborators each month! :winking_face: )

[sbn24]

This is a great solution for a limited number of events. Would you have any recommendations on how to scale for a larger number of events? Say around 250 to 300?

Thanks!

[W_Vann_Hall]

Copy-and-paste in your formula. :slightly_smiling_face:

Seriously: I’ve used the same technique to create nearly 470 records at once. (To make things even more interesting, each record had a big-ass branching IF() statement that used MOD({Autonumber},468) to determine which one of 468 skill descriptions to display. That was when I discovered IF() statements crap out somewhere in the mid-350s as far as maximum branches goes…)

However, the biggest tip I can give is to let Airtable write its own damn formulas. For instance, with the aforementioned base, I created a new table called [Development] that contained an autonumber and a single-line text field. I then pasted a list of all 468 descriptions into the text field, resulting in 468 autonumbered records, each of which contained a single skill description. Since the autogenerated records were to be named PersonName-001 through PersonName-468, I next created a formula field with this formula:

"Name&'-"&Nbr&",'&"

That can be a little hard to decipher; essentially, Name is a text constant that will be incorporated verbatim in the resulting formula segment, but Nbr is the name of the autonumber field in the table; in the formula segment, it will be replaced by the value of {Nbr} for that record. As a result, in the [Development] table I had a field called {Record Code Generator} that contained 468 sequential values in this format:

Name&'-001,'&

through

Name&'-468,'&

Now, to create the equivalent to the {Event ID} field, I clicked the header for {Record Code Generator} to mark the entire column of values and pressed Ctrl-C to copy them. I then created a new formula field in the appropriate table, clicked in the formula configuration window, and pressed Ctrl-V to paste the full column of formula snippets. Scrolling to the end of the pasted-in formula, I deleted the final ampersand and clicked ‘save.’ Voila! Airtable had written its own formula to create the strings to use as the primary field for autogenerated records. I wrote a similar formula to turn the list of descriptions into the two long IF() statements that drove that portion of the record.

So, that’s the main tip for dealing with such massive and tedious formulas: Make someone else do it. If that someone else turns out to be Airtable, then even better.

[Cameron_Deleone]

In case it’s of interest, I’ve come up with a slightly easier way to handle this scenario. Easier in the sense that it doesn’t involve a deeply nested IF statement. Modified template here, which you can copy: https://airtable.com/shrRlL45cc8BaX7HM

The gist is that you first create a formula field outputting a comma separated string of numbers, and then use SUBSTITUTE() to insert your record name/other invariant elements into the string.

SUBSTITUTE(',' & LEFT({Array 1:999}, FIND(''&Events, {Array 1:999}) + FLOOR(LOG(Events))), ',', ', ' & ID & '.')

It currently handles up to 999 elements (but that could be extended by increasing the array size; the formula should work accordingly).

To get the output of the formula to create new linked records, you can drag (using the fill handle), copy/paste, or set something up via Zapier to copy the output of the formula to the linked record field, creating the linked records automatically. The comma at the front of the formula won’t be a problem.

Hope this is helpful!

(Thanks @Vann_Hall for your work on the this, by the way!)

[jeremys]

Thanks to @W_Vann_Hall  and @Cameron_Deleone, very useful.

I'm impressed by your ingenuity. I've reversed engineered your formula Cameron because I like to understand things, I'm even more impressed! Here's what I understood in case it helps other people understand your formula:

1. create a formula field outputting a comma separated string of numbers : 1,2,3,4...999
2. if you have 3 events for the ID:546, you need to extract "1,2,3" from the aforementioned string. To do that you're using LEFT()
   1. To use LEFT, you want to find how many characters need to be extracted. In my example there are 5 characters in "1,2,3".
   2. So you use FIND() to find the number of characters to be extracted, which depends of the position of the number of events in the string of numbers. In my example if number of events = 3, there are 5 characters to until 3 (included) in the string 1,2,3,4...999.
   3. But if the number of events is between 10 and 99, you need to count one more character because those numbers have 2 figures. Same for number between 100 and 999, you need to add 2 more characters, because 3 figures. To calculate the extra character(s), you use FLOOR(LOG() : it extracts the lowest integer from log() : log(10)=1, log(11)=1,04, log (99)=1,99, floor(log99)=1)    
3. once you have extracted "1,2,3", you just need to use SUBSTITUTE() to replace 1 by 546.1, 2 by 546.2, and 3 by 546.3 to get the ID name into the string of numbers.

Very clever, well done!

(Pardon my English, I'm french)