Is there a way to include a unique reference ...

[Nick_Parsons]

Is there a way to include a unique reference number (consecutive) to the app when submitting a new record?

[Grant_Stead]

help.appsheet.com - What is a Key? This has an explanation of consecutive keys. The short answer is no… What is a Key? help.appsheet.com

[Eso_Surveyors]

Hi,

how about when it is not going to be used as a key?

[WillowMobileSys]

AppSheet does not provide the capability to generate sequential values. You can certainly create your own mechanism to do so.

You are probably aware of UNIQUEID()? This is a randomly generated alpha-numeric value guaranteed to be unique. It does NOT need to be a key value. I mention it just in case it helps.

[Eso_Surveyors]

thanks! i’ve been trying to work out a mechanism where each child record gets a seq number from 1 up the value goes in a field “Friendly Line #”. I dare ask, can you suggest something that happens while the children records are being entered, before being saved?

[WillowMobileSys]

I want to be careful before providing something that won’t fit your needs. So I want to ask, what is the reason for having a sequential number applied to the records?

I’m asking because if you read the AppSheet doc mentioned above, it is virtually impossible in a multi-user system to guarantee each record get assigned a unique sequential number.

An example… if the app has two users and each are adding a new record at the same time, whatever mechanism was built would deliver the same next value to each user. The devices don’t know anything about the new records about to added by another user. The app ends up with two records having the same value.

It still might be possible in YOUR use case to build something but it would be HIGHLY dependent on your app and how your users interact with it.

For example, as mentioned in the article, if you only ever have a single user adding records then uniqueness can be achieved. Once that stops being the case, all bets are off!!

[Eso_Surveyors]

Fair question! this is a timesheet APP. only one person will ever create the parent and children records, and rarely modify them, no other user will be able to make changes.

[WillowMobileSys]

Ok thats good! In that case you can simply use a MAX() function, to find the highest number and then add 1 to it and place that expression in the Initial Value.

But still, with an abundance of caution, I ask…what would you use the sequential number for?

If its to keep track of the number of records, it is much better to simply compute the count. I know you say the records will be rarely modified, but I can almost promise that issues will arise and records will either be deleted or new ones created to “replace” the old bad one. When a knowledgable person is not around, other people will find a way to resolve an issue that may not be ideal.

[Eso_Surveyors]

thank you, i should’ve said.
Field staff fill the timesheet APP and email copy the customer.
The seq # is for identification of each child record when there are multiple children.
Each child record of the Timesheet will be used in payroll and invoicing to customers.
Each child needs to be identified when payroll staff question any of its fields, and when it is included in the invoice for the customer to match with their signed/approved copy of the timesheet.
Modifications are allowed, but not deletions.
The records are filtered to show only if the logged on user is the original creator, there is no chance 2 people will access the one parent or children.
I’ll try MAX() again, i couldn’t work it out before posting here. i’ll let you know.

[WillowMobileSys]

It sounds like you care more about the unique nature of the number and not the sequential aspect. I would then recommend using UNIQUEID() function in Initial Value and be done with it. You’d never need to worry about it again.

BUT, if you have your heart set on the sequential numbers, the MAX() function you need should look like this:

MAX(Table[Column]) + 1

Just change the table and column to match your data. I’m not 100% sure what MAX() does if there are no rows (e.g. when adding the very first row) but if you encounter a problem then you could do this

IF(COUNT(Table[Column]) > 0, MAX(Table[Column]) + 1, 1)

Good luck and post if you still have questions!!

[Eso_Surveyors]

MAX(HRF16[Friendly Line #]) + 1 works well increasing by 1 for each child record.
I really need the first child of every new parent to start from 1.

[Eso_Surveyors]

Hi, @WillowMobileSystems, maybe this helps others, there is no built-in sequential numbering in AppSheets.
I needed to have a sequential number for each child record in my app that would reset back to 1 for every new parent, not for use as a key, but to append to a TEXT field.
I ended up creating a column in the CHILD table called “Friendly Line #”. Set Initial Value to MAX(HRF16[Friendly Line #])+1. Created an Action CLEAR Friendly Line # to set Friendly Line # = 0.
and a Workflow on the CHILD table for All_Changes to run CLEAR Friendly Line #.
This works fine for my purpose.

[WillowMobileSys]

Another way to do it without the need of Actions:

MAX(SELECT(HRF16[Friendly Line #], [Parent ID] = [_THISROW].[Parent ID])) + 1

The SELECT chooses all the rows from the table for only that Parent.