Is there currently an auto-increment field of some type? If not, is there a way in code?
(I found some old posts, so I don't know how relevant they are anymore, and the solutions they proposed don't work if you delete records as they rely on record counts, etc.)
This is going to seem like over-kill, but it's the way I do it as I have a Ref field in most of my tables.
I have a Reference table with the following structure
NextRef has the formula
Prefix + lpad(text(NextNumber), Length, "0") + Suffix
I have a global function called nextRef
function nextRef(Name : text) do do as server let a := first(select Reference where Table = Name); let b := a.NextRef; let c := a.NextNumber; a.(NextNumber := c + 1); b end end
which I call in the Trigger on new record: of my tables
Ref := nextRef("Batch")
If Prefix is "B", suffix is "", nextNumber is 11 and length is 4 this will give me B0011 and nextNumber will be set to 12.
But I think that "before" has a problem... IIRC from my testing, if I have, say 9 records...and I delete #9. Then create a new record, the "before" causes the new increment to be 9 again (because the max has changed). That's no good... It has to be universally unique. If there was a 9 there before, the new one should be 10. No?
Yes it will be 9 - but as you say you deleted the old 9th record - thus ninox no longer knows that that record existed. If you must preserve that record. Then would it not be better to archive the records as flagged as deleted but don't actually delete them. Unfortunately, Ninox (to date) have not build in a true sequence from field functionality.
this is probably taking you further way from where you want to be - but on some of my tables I have hidden the add/deletion buttons as per this thread https://forum.ninox.com/t/g9hrtf1 from Sean ..
Then I added a delete button to the forms that basically archives the form and hides it from the various table views. a variation of this would still preserve your number- as used already but technically deleted.
Really depends on how far you want to go with it. If you are happy to delete a record then you presumably are not expecting to preserve some kind of audit trail - so why do you need to keep a very strict number trail?
Does that help?
Kent Signorini To be fair Ninox is pretty awesome. I used it for last 2 years and I have managed to move a whole load of my business processes onto it. It is not perfect (what software is?) But I have found and continue to find that there are many ways to circumnavigate it's shortcomings. I agree a sequenced/Auto Number function (pre pref or post fix!) should be there. May be in the not to distant future it will be..
If you decide to still with it then are a bunch of dedicated users that will do their best to help (where we can :-)