Incrementing Number when its a Key / Invoice Number Generation

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!

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.

3 Likes

Also reference this thoughtful topic from @Steve

3 Likes

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

2 Likes