Re: How to make sequential number?

Hazem_Mohammed_ · 03-02-2020 01:50 AM

how to make a sequential number?

Steve

Hazem_Mohammed_

Thanks for your kind reply, so what is the alternatives for this case?

Steve

Did you read the article?

Hazem_Mohammed_

which article?

Steve

The one I posted above.

Hazem_Mohammed_

Yes, i did, but I did not got this (One way to implement serial numbers is with a normal (not virtual) column named (e.g.) Serial of type Number and an Initial value expression …)

Dialect_Junk

Hazem,

I have a work order app that creates sequential numbering for our work orders. Here’s the formula I use in the “Initial Value” column:

CONCATENATE(RIGHT([Year],2), [_RowNumber]+1)

Which gives me work orders that start with “20” and then the resulting row number. For example, right now I’m up to work order 20232. Works ok for our purposes.

2 lessons I learned:

1 - Don’t use this column as your “Key.” It’s not a reliable and unique enough number.

2 - Be careful if you decide to clean up your spreadsheet/database. I was cleaning up the app and decided to delete several work orders that were unnecessary. I then decided to remove these blank rows in my spreadsheet which created all sorts of issues with sequential numbering and work order creation. Not fun.

Hope this helps.

mercymc50

Hi My name is Mercedes, I was scrolling through looking for exactly what is posted here... sequential number for work orders. I attempted the formula, but apparently I've done something incorrectly because the formula is not working for me. I keep getting an error. I tells me I need an apostrophe before the equal sign because this is "1+1" formula and the [Year] is being looked at as "text". What have I done wrong? Thank you for your help

Eduardo_dos_San

I was looking for a similar solution.

The best I found was to do this management through the spreadsheet with the command

ArrayFormula ().

Ps: MySql is easier because there is autoincremment

furkizio

This is how i handle a sequential numbering. PUR1 is my static value for puchases.

“PUR1”&RIGHT(“000000”&([_RowNumber]-1),5)

Pro_Nature

This works…Thanks

Jerry1

is this formula to be input in excel? or in the column of appsheet?

Steve

That’s an AppSheet expression, not Excel.

ROBERT_Nelson

I’m looking at developing key columns that have more meaning to me than the default. Therefore, I was hoping to create a column called “BusCustomers” and “Customers”, providing the first column as auto-incrementing and unique. The problem is, this approach doesn’t work. I’d stick to the standard items but I noted that Customer Names and Business Names weren’t unique enough to form a key. Is my approach off, or do you have an alternative suggestion (maybe creating a field, as above, then using it plus the customer last name would serve me better?

furkizio

Have you tried to use a virtual column as key? So then you can concatenate different values from different columns and make 1 key. Like this:

LEFT(“Customers”,4)&RIGHT(“000000”&([_RowNumber]-1),5)

Btw, this will work in ID column as well. Give it a try

Pointer

Unique() I recommend using a key. With the formulas suggested by friends for the row number, you can create the row number with a second column. Because doing this prevents the use of the same key when more than one person uses the application.

jkevz07

I used this expression, in reference to my table columns: COUNT(SELECT (Customer Items[Serial No.], [Reference Number] = [_THIS].[Reference Number]))+1