Sequential Field - NOT KEY

[Luis_Gomes_Mac1]

Hi again guys,

I am certain somebody have asked this question before, but I besides searching both on the community and Youtube I couldn’t found answer for it.

I need to create a sequential field for a a Quote Form I am building, such as:
PC301
PC302
PC303
etc…

It is important to say that this field will NOT BE A KEY as for indexation purposes, this is just for a presentation reason.

how and where do I build this expression?

[LeventK]

@Luis_Gomes_Machado
How many active users you have using this app?

[Luis_Gomes_Mac1]

for now none, but I hope to have no more that 15 to 20

[LeventK]

@Luis_Gomes_Machado
Being a key or not, sequential numbering have the cons of creating doubles provided 2 or more users try to create a record at the same time. It’s very easy to read the last recorded number from the table and increment it by 1, however when 2 or more users are trying to record a data, as the data is not in the back-end yet, there’s no way for AppSheet to know if the next generated number is already used by another active user or not. Therefore multiple same numbers can be created.

[Luis_Gomes_Mac1]

I see what you mean… how do you recommend to create an “invoicing system” for instance, that need to have a sequential number?

[LeventK]

For the apps that I have created for our clients, - in general - we give the privilege to a particular app user for creating invoices, orders etc. that by-nature or by-law or by-major needs sequential numbering. Otherwise, with multiple users, unfortunately there’s no way to prevent the duplicate records.

[Luis_Gomes_Mac1]

that is something that we consider to implement as well.

If so how do you build the sequential number?

[LeventK · Accepted solution]

You can always read the last recorded value with a simple LOOKUP(…) expression, stripe the number part and then increment it by 1 and re-join,

CONCATENATE(
	"PC",
	NUMBER(
		INDEX(
			SPLIT(
				LOOKUP(
					MAX(Tablename[_RowNumber]),
					"Tablename",
					"_RowNumber",
					"InvoiceNumberColumn"
				),
				"PC"
			),2
		)
	) + 1
)	

[Luis_Gomes_Mac1]

thanx for your help Levent!!

[Luis_Gomes_Mac1]

Hi Levent

I have been trying to use the expression that you kindly help me with:
NUMBER( INDEX( SPLIT( LOOKUP( MAX(Contactos[_RowNumber]), “Contactos”, “_RowNumber”, “Num Empleado” ), “” ),2 ) ) + 1

to create an incremental number of a table which contains a list of employees, although each register has its own unique() we need a employee number for other reasons.

When I use this expression is not incrementing the field

the table name is Contactos and the column is Num Empleado

do you have any idea why?

[Jonathon]

It looks like the SPLIT() portion of your formula is missing the “PC”. If nothing has changed with your invoice numbers, your formula should be in the format:

CONCATENATE("PC",
  NUMBER(
    INDEX(
      SPLIT(
        LOOKUP( MAX(Contactos[_RowNumber]), “Contactos”, “_RowNumber”, “Num Empleado” )
      , "PC" )
    ,2) 
  ) + 1
)

Explanation

EQ1

LOOKUP( MAX(Contactos[_RowNumber]), “Contactos”, “_RowNumber”, “Num Empleado” )

This portion of the code is returning the most recent invoice label by assuming the highest row number contains the most recent invoice. The output from here should be a single invoice in the format PC1234 as long as nothing has changed.

EQ2

SPLIT(EQ1, "PC" )

This portion of the code is SPLITTING the returned invoice number into a list where it finds the substring “PC”. The output here will be a list of two strings, in the format {PC, 1234}. Note that your issue may be occurring here - you do not have the “PC” in the formula you posted.

EQ3

NUMBER(
  INDEX(EQ2, 2)
) + 1

This portion of the code is selecting the second element from the referenced list, In this case the numerical string, converting the string into a NUMBER, and incrementing it by 1.

EQ4

CONCATENATE("PC",
  EQ4
)

This portion of the code appends the “PC” prefix to the incremented invoice number.

[Luis_Gomes_Mac1]

Thank you Jonathan,

in this case I am not using PC just trying to increment the employee number when I add a register, how can I achieve this?

[Steve]

Serial Numbers, If You Must Tips & Tricks 💡

Danger Ahead! In general, sequential numeric identifiers (i.e., serial numbers) are risky in AppSheet: if two users happen to add rows at the same time, both rows could be assigned the same serial number, which could lead to confusion (at the least) or data loss (at the worst). For this reason, serial numbers are strongly discouraged! Basic Serial Numbers 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 …

[Luis_Gomes_Mac1]

Hi Steve,
I know about this as you have mentioned to me before, nonetheless this field is not a key and will only be inserted by a single user

[Steve]

Key or not, the expression is the same, as given i that post.

[Luis_Gomes_Mac1]

Thank again Steve!

in this case I am not using PC so if I remove PC is not doing the increment