Auto Numbering

How to setting auto numbering that appear with a new order submit by a form button?

Example, i have 3 form submitted. i need the first submitted form showing:

0001 - “form result; …”
0002 - “form result; …”
0003 - “form result; …”

those 0001,0002,0003 auto generate by data backend.

1 Like

Glide cannot natively do this, but you can create an arrayformula in the spreadsheet to auto generate these numbers.

Here’s a sample arrayformula.

={"Auto numbering";ARRAYFORMULA(IF(A2:A="","",TEXT(ROW(A2:A)-1,"0000")))}
1 Like

Can using by Query function to do this ?

Why do you need a Query for this though? I think it’s too simple to use a Query. If you want to number things after a query then you can use my arrayformula.

Yes it can, sortof :wink:

Let’s say the destination column is called ‘Order ID’.
Create a Rollup column that takes the Max of Order ID - call that MaxOrderID.
Then create a Math column that is MaxOrderID + 1 - call that NextOrderID.
And NextOrderID is then used in the form submission.

PS. I use this method in that thing I shared with you the other night :slightly_smiling_face:

4 Likes

But it can’t format the ID as 0001 though, right? :sweat_smile:

1 Like

heh, not easily…

but, where there is a will there is usually a way… with a sufficient number of ITE and Template columns :rofl: :rofl:

3 Likes

I agree with @Darren_Murphy’s method. It’s a lot safer than having a formula create the number…especially using the order number example. Otherwise with an arrayformula and dynamic numbering, it’s too easy to delete one row and have all order numbers get renumbered and messed up. I think the best way is to have one column with just a number and another column with the alpha characters, then build the complete order number with a template column.

I think to get the left padded zeros, you could take the number, divide by 1000, create a template to lock in the value and replace the decimal with nothing.

6 Likes

So smart. I guess I wasn’t reading the post closely enough…I missed the part about form submission…I was assuming just ordering rows. Jeff’s solution with the template column is clutch.

3 Likes

How would you do the “replace the decimal with nothing” bit?

Here is how I would do it:

“Padding” is just a simple ITE…

So putting all that together, 4 columns required:

  1. Rollup to determine the current max order ID
  2. Math column to increment that by 1
  3. ITE column to determine how many zeros required for padding
  4. Template column to join 2 & 3
13 Likes

This should be it, well done :wink:

1 Like

this is so smart! but if want to duplicate something between 0001, and 0002. example 0001 i need duplicate to become 0002, and previous 0002 become 0003 and continuous the following number. if i did duplicate in Glide app, my google sheet arrayformula/query will become error.

My solution is an alternative to the arrayformula.
So if you use my solution, then the arrayformula isn’t required.

1 Like

ok! Thank you so much for sharing & teaching!

I think I was thinking about using a template column and replacing the decimal point there, but trying out, I realize now that you need to have another column with a blank value for the replacement and the template doesn’t seem to like numeric columns as the base template, so it would first have to be converted using another template column to alpha. Seems to work, but ultimately not any more efficient than your solution.

1 Like

Ah, gotcha.
So something like this, yes?

4 Likes

yep!

If there are a lot of simultaneous submissions, are you sure 2 or more rows will not take the same Roll UP +1 and consequently 2 rows will have the same number??

2 Likes