0

Automatic Numbering Issue

Good Morning,

 

I have automatic numbering working on my database but i need to see how i can improve

 

Here is my current number . (Year - Job # - Auto number-001) 

2019-522-001.001

2019-522-002.001

2019-522-003.001

2019-522-004.001

The .001 represent review number, if for somereason it goes thur multiple reviews the number will grow (.001 manual grows)

 

But the issue im having is if i change something it counts the wrong thing

2019-522-001.001

2019-522-002.001

2019-522-002.002

2019-522-004.001 . (It skips 003 since there are 4 entries)

Current Code (On Create)

let y := text('Work Order Master Code');
let z := year('Current Date');
let new := z + 'Work Order Master Code';
let newnew := count(select Submittals where year('Current Date') + 'Work Order Master Code' = new);
'Submittal #' := z + "-" + y + "-" + format(newnew, "000.001")

2 replies

null
    • Mconneen
    • 5 yrs ago
    • Reported - view

    That is correct.. because you are counting the rows.. which also include the revision 2019-522-002.002 ... I can think of a couple of strategies..  First .. in keeping with your logic.. You could add a "contains" condition to count only the Submittal numbers that contain ".001". 

     

    Another strategy is to keep all the pieces separate .. until you want to display them.   So it sounds like your "Submittal #" is 

                    Year - Work Order Master Code - Job # Increment - Revision # Increment.. 

     

    This way.. you can grab the "max" of 'Job # Increment' and add 1 to it. 

    • Ninox partner
    • RoSoft_Steven.1
    • 5 yrs ago
    • Reported - view

    Have also a look at: https://ninoxdb.de/en/forum/use-cases-5abd0b9c4da2d77b6ebfa395/create-tables-with-autonumbering-with-ease.-5d5ee82ab6ba1f2dab0664b4?post=5d5ee82ab6ba1f2dab0664b5&page=0

    for an example database.

Login to reply

Content aside

  • 5 yrs agoLast active
  • 2Replies
  • 1423Views