0

Auto-increment?

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.)

18 replies

null
    • John_Halls
    • 2 yrs ago
    • Reported - view

    Hi Kent

    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.

    Regards John

      • Kent_Signorini
      • 2 yrs ago
      • Reported - view

      John Halls This looks like it would ensure that all new numbers are unique. So in my case I need Project Numbers and Quote Numbers. I suppose I could have a table of each that I pull from each time.

      But what creates the new record in your Reference table, and what would create a new Project Number or Quote Number in my separate tables?

      • John_Halls
      • 2 yrs ago
      • Reported - view

      Kent Signorini Hi Kent They are all in the one table, Reference. I have a record for each reference number that I need to track. My Reference table might look like this

      Each call  

      Ref := nextRef("Batch")

      Grabs the NextRef value and increments it ready for the next time.  It doesn't add a new record.

      Regards John

      • Kent_Signorini
      • 2 yrs ago
      • Reported - view

      John Halls Oh, I see now. Interesting. 

    • Mel_Charles
    • 2 yrs ago
    • Reported - view

    and here is my simple one for adding a job bag number - this is placed in the trigger on new record in the table

    let before := max((select JobDockets).'Job Bag');
    'Job Bag' := before + 1

      • John_Halls
      • 2 yrs ago
      • Reported - view

      Mel Charles Do I have a problem overthinking things? Tell me the truth...

    • Mel_Charles
    • 2 yrs ago
    • Reported - view

    John Halls Wouldn't like to comment John 😗 I keep things simple cos i'm incapable of thinking any deeper 🤣

    • Kent_Signorini
    • 2 yrs ago
    • Reported - view

    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?

    • Mel_Charles
    • 2 yrs ago
    • Reported - view

    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.
     

      • Kent_Signorini
      • 2 yrs ago
      • Reported - view

      Mel Charles I suppose it might be alright in my case. I'm just very used to doing it a different way in the other system I use (Knack). With some thought, I could probably be okay with the before/job bag version. I may or not build in a soft-delete system as you suggest. It's easy enough, but it just adds that extra level of condition to everything.

    • Mel_Charles
    • 2 yrs ago
    • Reported - view

    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
      • 2 yrs ago
      • Reported - view

      Mel Charles It helps. Gives me lots to think about. I haven't started building my app in-full yet, just testing to make sure I can do what I need to do in Ninox. It has many benefits over Knack (speed, simplicity, workflow, changeability/programmability) but there are definitely some weird things too (no auto-increment?! wth!)

      I like that you can do that stuff with the webviews to hide/show/replace buttons, etc.

      Thanks!

    • Mel_Charles
    • 2 yrs ago
    • Reported - view

    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 :-)

    Mel 🙂

      • Kent_Signorini
      • 2 yrs ago
      • Reported - view

      Mel Charles Great! (Any ideas how large the installed base of Ninox is?--I'm hijacking my own thread, but...)

    • Mel_Charles
    • 2 yrs ago
    • Reported - view

    Sorry I don't know - I'm totally on the cloud version

      • Kent_Signorini
      • 2 yrs ago
      • Reported - view

      Mel Charles I guess that's what I mean. I wonder how many use it? (I'll be going cloud, too.)

      • jurgenpeter
      • 2 yrs ago
      • Reported - view

      Kent Signorini 

      According to Ninox, 300,000. However, I always take vendor sales numbers with a big grain of salt.

      I am familiar with several (more than several) development platforms. I don't know, maybe 5 or 6 months ago I started using Knack, which is pretty good as far as it goes. But it only goes so far. So a couple days ago I started evaluating Ninox. It looks promising on the surface, then reading the forum I see the familiar road-blocks. A non-responsive product management team. Management's seeming disinterest in user preferences (what do stupid customers know anyway?) I have to wonder how a company that claims 300,000 users has such minimal, skimpy documentation? No auto-increment field - seriously? Ninox looks pretty good on the surface, but when you look under the hood you see the same problems as so many other platforms have. Sigh...

      • Database App Developer
      • vermau81
      • 3 mths ago
      • Reported - view

       This is how I do it. I have this script in the "On Create" of the tables wherever I need it. It generates unique alphanumeric series.

      "Script to auto generate a new SO Number on creation of a new Sale Order";
let factor := "";
let SOCounter := 0;
if cnt((select 'Sale Orders').'Sale Order Number') = 0 then
    factor := "000";
    SOCounter := 1
else
    let lastSONumber := last(((select 'Sale Orders') order by 'Sale Order Number').'Sale Order Number');
    SOCounter := number(substr(lastSONumber, 10)) + 1;
    if SOCounter <= 9 then
        factor := "000"
    else
        if SOCounter > 9 and SOCounter <= 99 then
            factor := "00"
        else
            if SOCounter > 99 and SOCounter <= 999 then
                factor := "0"
            else
                factor := ""
            end
        end
    end
end;
'Sale Order Number' := "SO-" + format(today(), "YYYY") + format(today(), "MM") + factor + SOCounter
Login to reply

Content aside

  • 3 mths agoLast active
  • 18Replies
  • 502Views
  • 5 Following