How to auto generate number with letter like PO001 if a value is empty?

Hi im building a purchase order module, i got this

it works good, but i would like to put the field Ref Number when is empty it should auto generate a number with the format example PO001 and should be incrementing each time or a random unique number always that wont conflict with others purchase orders and save to the db, and also give the option if user put something like 1287312847219 to save it too or just leave the first option, to auto generate to avoid extra validations with db correct?

thank you

Hey @agaitan026! To make sure I'm following correctly, you just want any random unique number or does it have to be formatted like PO###?

If just a random unique string, would {{ uuid.v4() }} work for you?

If just a random number, Math.floor(Math.random() * 90000) + 10000 should generate a random number with 5 digits (though this doesn't guarantee uniqueness).

Similarly, if a random string formatted like PO###, 'PO' + Math.floor(Math.random() * 900) + 100 should work! Again, this doesn't guarantee uniqueness.

In order to guarantee uniqueness, we'd have to query your db to check for pre-existing IDs and check our random number/string against that list!

Let me know :slight_smile:

1 Like

Yeah I think I will need the last solution, check db for the id so I got uniqueness, how is that possible? In using mysql

In order to check for uniqueness, you’ll need to write a separate query to search for that given ID in your database, and if it doesn’t return any results, then you know you’re given ID is unique! Otherwise, you’ll need to query all of your data and write JavaScript to check if the given ID is in that list of IDs.

SELECT * FROM table WHERE id = {{textInput1.value}} low tech Otello business minded. I don’t remember the freezing of that podcast.

But this solution would only work if you’re OK with coming up with a random idea and then rejecting it if it’s not unique. This is not guaranteed that you initially create a unique idea. To do that we’d need to write some custom JavaScript. Is this something you’re interested in? I’d be happy to help!

1 Like

Yes I need uniqueness

Should people be able to enter a POno themselves? Or does it always need to match the sequence and uniqeness from your db? Does the POno always have the same layout?

If so I'd suggest to create a query that fetches the last used PO no from the db. Do this on form submit, not on page load, because you might get duplicates if multiple people are using the form simultaneously.

select max(right(POno,3)) from database;

Then simply +1 to the result and write to db.
You could add an extra check on submit to check if the POno exists:

select POno from database where POno = newPOno;

If you get a result back, you can throw an error or even query again for the latest number and +1 again.

1 Like

yes, i wont let people enter PO number, so it should be auto generated better, it have layout POxxxxxxx maybe 10 digits after PO letters

Just wanted to check in and make sure you're not blocked! Would @mbruijnpff 's suggestion work for you (fetching the last used ID and then adding 1)?

1 Like

how i add that +1 ?

Something like this should work! {{'P' + (parseInt('P12309'.slice(1)) + 1)}}

For example,

Let me know :crossed_fingers:

1 Like

great, thank you so much