Incrementing Number when its a Key / Invoice Number Generation

[baggster55]

Hi all - still very new to AppSheet. I am creating an app to let users enter an Invoice. Each invoice has an invoice number, and the beginning of that number indicates what sales channel the invoice is for. For instance, a sale in a shop starts with an 8 (80001), while a sale online starts with a 2 (i.e. 20001), etc.

I would like to automatically generate the next invoice number based on the Sales Channel that the user selects.

So for example, User selects Online as the sales channel in a drop-down, so the Invoice Number automatically populates with a new Invoice Number, which is the next number sequentially that starts with “2.” So if the last Online invoice was 20001, the system would assign 20002 to this new Invoice.

I’ve no idea how to get this done, so am looking for some tips. Another issue is that the Invoice Number is the Key, so I think I am limited on the expressions I can build here.

Any advice is appreciated!

[tony1 · Accepted solution]

Hi @baggster55. You shouldn’t use sequential numbers as your keys. See this article for a discussion of why not: https://help.appsheet.com/en/articles/1726585-sequential-keys

I would recommend making a separate key column from your invoice number column. You can use UNIQUEID() as an initial value formula for your key column. You still might end up with invoice number collisions, but it won’t affect the functionality of your app, since you’ll have unique key values.

Here’s how I’d get sequential invoice numbers. The idea is to use an initial value formula that looks up the largest existing invoice number of the same type, and then add one to it.

Something like this: MAX(SELECT(Invoices[Invoice Number], [Invoice Type] = [_THISROW].[Invoice Type])) + 1. This will only work if your invoices all start with a non-zero number.

See SELECT() and MAX() and List expressions for more details.

[Jonathon]

Also reference this thoughtful topic from @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 …

[baggster55]

tony:

MAX(SELECT(Invoices[Invoice Number], [Invoice Type] = [_THISROW].[Invoice Type])) + 1

That’s great, worked perfectly! Thanks also for the tips on the key, I’ve made the adjustment as you recommended.