Hi ​@KrisHamilton,

Welcome to the Community. 

To generate a new user ID, split the alphanumeric ID, increment the numeric part, and combine them. Use Zapier's Formatter with a 'Replace' action to update the numeric portion, followed by a 'Pad' operation to add leading zeros if needed. Finally, rejoin the 'USER' prefix and the padded number using the 'Combine' action in Formatter. This process creates the updated user ID for your Zap.

I hope this helps! If you have any further questions or run into any issues, don't hesitate to ask.

Hey thanks for the response. I split the alphanumeric ID with user_id as Input and USER as Separator, and segmented index Last - which gave me the correct 001 output. When I use formatter -->Action, Numbers...and choose Perform Math Operation, Operation Value: Add, then for Values Input I map the previous Output: 001 then continue, I get this error response: 

Failed to create a number transform in Formatter by Zapier

The app returned "Expression Error: + is probably missing arguments due to an empty field mapping".

What part am I getting wrong?

Hi there ​@KrisHamilton,

Before we dig deeper into this, would you mind sharing a detailed screenshot of how your Zap is configured? Also, please share a screenshot of the “CONFIGURE” section of the action step/s that shows all of its fields.

Please don't include personal information in the screenshot, or be sure blur out any personal information.

Thanks!

here are the screenshots of the steps I took: 
 

Hi ​@KrisHamilton 

Thanks so much for sharing all those screenshots to show the current set up of your Zap with us—very much appreciated! 🧡

If you’re just looking to increment a number by one each time the Zap runs for a new record, I wonder if a easier approach would be to use an Increment Value (Storage by Zapier) action:

Storage would keep a record of the existing ID number and increase it by 1 each time the Zap runs. Then, you’d select that incremented ID number in the relevant the user ID field on an Update Record (Zapier Tables) action to have it automatically update the user ID for each new record that’s added to the table. For example:

Granted it wouldn’t have the exact format you’re after with the 00 part included. But if you needed it to have the 00 included you could always add a Code action to output 00 for numbers under 10, 0 for numbers less than 100 and nothing for numbers 100 or greater (Pad the amount). Then in the Update Record action, you’d select the value output by the Code action, to have the ids formatted as USER001, USER050, USER200 etc. If you’re not familiar with coding in Javascript or Python we have a AI feature that can help generate the necessary code for you: Generate a Code step using AI (Beta).

Hope that helps to get you pointed in the right direction. If you run into any issues on that or have any further questions just let us know!

Thanks for the suggestion. If I changed the format from USER001 to format USER-1, would your suggestion work better?
 

I am curious, was my previous approach incorrect? I was trying to follow your colleagues suggestion to “split the alphanumeric ID, increment the numeric part, and combine them. His advise seemed pretty straightforward, so I was hoping to understand where I was going wrong. 

You’re welcome ​@KrisHamilton! 

If I changed the format from USER001 to format USER-1, would your suggestion work better?

Yes, if you changed the format to that you could just type in USER- into the relevant ID field in the Update Record action followed by the Value field (selected from the Storage action) without the need for any Code by Zapier action to be used.

I am curious, was my previous approach incorrect? I was trying to follow your colleagues suggestion to “split the alphanumeric ID, increment the numeric part, and combine them. His advise seemed pretty straightforward, so I was hoping to understand where I was going wrong. 

Not entirely, that sort of approach could work although it involves a lot more steps as you’d need to get the user ID that was last added to the table in order to then split the text from the numbers and then add one to generate the next user id. I was just thinking that it might be quicker/easier to just store the user ID’s with Zapier storage and have it increment them by one each time. 

I did some checking and there is no “Combine” action available for Formatter, you’d just need to have both parts, the text and the number, added into the field to join them together. Also, there’s no “Pad” operation to add leading zeros which is why I suggested using a Code step to add the necessary number of 0’s—with it’s AI feature you could give it a prompt of “Pad the amount given in inputData to 3 digits” for example.

If you wanted to continue with the sort of approach suggested by Jammer then it may have been erroring as a + symbol was included here: 

If you remove that + symbol then it should be able to add 1 to 001 as the Add Operation has selected. That said, I expect it would output the result as 2 instead of 002. So you’d then you might want to use a Code step to pad the number to 3 digits.

Let me know if there’s anything else I can help with on this, keen to ensure you’re all set! 

Great. I followed your advice and Increment Value (Storage by Zapier) action: 

How do I test this to make sure it works? I do not have anything like a form or Interface connected to the Users table yet. Thanks again ​@SamB 

(Okay, trying to edit my previous post but making me write a new one.)

You’re totally right “I was just thinking that it might be quicker/easier to just store the user ID’s with Zapier storage and have it increment them by one each time.” 

It is easier, thank you. But...there is a “limitation” of Storage by Zapier  - it “will delete keys after 2 months of inactivity”, so it appears not a good place for long-term storage. Which brings me to another question - isn’t that what Zapier Tables is for? I mean, my first Record in my Users table is a dummy record (i.e. me) - and my thought is - a “storage” record by which all subsequent records can increment their values. That’s why I manually created the user_id as USER-1, and user_settings as SET-1 (to be linked to the User Settings table).

If that is the case, and I can use the first record in my Users Table as a “storage record”, then I still need to increment the following records (i.e. User-2, User-3, etc.). 

What do you suggest? Is it possible for you and I to have a quick phonecall/screenshare? I’m probably making this way more complex than it needs to be… Thanks again for your help.

Ah, so sorry about that ​@KrisHamilton. I’d forgotten about that 2 month limitation with Storage! 

It’s not possible for me to hop on a phonecall/screenshare but I’d be happy to share some screenshots. 

I didn’t suggest using Table’s Increment Value action previously as there’s no “get last record” type of action that would have easily got the previous user ID so it could then be incremented by 1. But we’ve actually just released a new feature called Summary Formulas which I think you could use for this instead. It would allow you to essentially keep a running total of the number of records in the table which you could use to create the user ID number value when a new record is added. I did a quick test of this just now and it worked! 

In Tables you’d have a Number type of field that’s set to count the number of values present in that field across all the records in the table.

Then you’d have a Zap trigger when the new record is added that uses a Calculate Summary Formula action to count the number of values present in the table. For example:  

Which, if each record has a value in the Number field it will essentially be outputting the number of records in the table:

Next you’d need to have an Update Record action that creates the user ID like so:

Then every time a new record is added with a value placed in that Number field the Zap will count how many values are present, generate the next user ID and add it to the record:

Do you think that approach could work better?

Thanks, let me try!  One question comes to mind, In Tables Number type of field, would I have to manually enter 1 for each new record?

Side note - is this such an unusual Zap request that this is so complex? I feel like incrementing an alphanumeric id for new records is a pretty common need, right?

Hey there, here’s an update. I followed your recommendation step-by-step (pics), but the User Table does not update. Can you tell if I did it incorrectly?

Thanks for getting back to me, ​@KrisHamilton! 

In Tables Number type of field, would I have to manually enter 1 for each new record?

No, it can be whatever number value you like. COUNT just counts it number of values it’s not like SUM which would add together all the values. 

Side note - is this such an unusual Zap request that this is so complex? I feel like incrementing an alphanumeric id for new records is a pretty common need, right?

I’ve not personally seen a lot of requests for this sort of thing with Tables but I can definitely see how it would be useful! I couldn’t see any existing feature requests for this so I’d suggest reaching out our Support team here to submit a new feature request for the ability to auto increment alphanumeric ids when new records are created. 

I followed your recommendation step-by-step (pics), but the User Table does not update. Can you tell if I did it incorrectly?

Hmm, it looks like the Record ID has a static value selected in rather than taking the Record ID from the trigger, so that might be why you’re not seeing the records updating as expected.

Can you change the Record ID field to take the ID value supplied by the trigger step instead?

Please keep us posted on how it goes!

Thanks for the reply. To my first question - In Tables Number type of field, would I have to manually enter 1 for each new record?  -  Like, would I still have to manually input 1, or can I somehow automate this to be 1. The whole reason for this entire zap is so I don’t have to manually assign a new user ID to each new record/member. 

Not at all, ​@KrisHamilton! There’s two ways you can automate the addition of that value:

1. If you’re using another Zap to create the record using a Create Record action. Then in that action you would set the Number field to have a numerical value in it. 
2. Or, if the new record is added manually, in the Zap that triggers when a new record is added you’d put an additional Update Record action (just after the trigger) so that it adds a numerical value to the Number field. Then the Zap would run those Calculate Summary Formula and other Update Record actions to add the user ID as well.

Let me know if you run into any trouble on that or have any other questions at all!