0

adding an auto incremental number to existing records

I am well used to adding auto numbers on the fly when creating records. However I have a batch of some updated 105,000 records that I want to add an simple incrementing number to ie 1,2,3 etc. so that i can export to use in segmentation in my mailer ie (give me records wher RefNum is between - and 5000. I do a have a uniqe ref number on all records but it is a two part complcated number and i don't want to ise that (als ref ninox ID number is you bit as internal number like 225330 etc

My script and variations of it does not appear to work - any idea why

i have tried doing from both client side and do a server and also defining RefNum as a number and text fields but script seem to be ignored (test is on 3 records only)

Want it to run from the current record i am in. ( ie press button on that records and go....)

any idea why

let xnum := 1;
let xStamp := slice(select '118 Data' where RefNum = " ", 1, 3);
for r in xStamp do
    r.(RefNum := xnum);
    xnum := xnum + 1
end

also tried with a max condition (adding 1 to first record)

let xnum := max(select '118 Data'.RefNum) + 1;
let xStamp := slice(select '118 Data' where RefNum = null, 1, 3);

for r in xStamp do
r.(RefNum := xnum);
xnum := xnum + 1
end

and

let xnum := 1;
let xStamp := slice(select '118 Data' where RefNum = null, 1, 3);
for r in xStamp do
r.(RefNum := xnum);
xnum := xnum + 1
end

does nothing !

I have many buttons tha loop through records all fine (ie set a yesy no to true etc)

eg (this script is used on same data)

let xStamp := slice(select '118 Data' where Export = false, 1, 5700);
for r in xStamp do
    r.(Export := true)
end

- i have probably missed something dead obvious (as usual) 

(also is anyone using zappier to do simple updates ie a ref field from Ninox to mailer software. I only want to do a simple 2 step trigger for testing and I understand that you can get this free for x qty of zaps! don't want to buy it yet until I am fully happy it will do what i want. (or is ther anything better out there?)

6 replies

null
    • Fred
    • 3 days ago
    • Reported - view

    This works for me:

    let xNum := 1;
let xRecs := slice(select TableA where not Score, 0, 2);
for rec in xRecs do
    rec.(Score := xNum);
    xNum := xNum + 1
end

    It looks like yours so I'm not sure what is going on. Can you post your DB?

      • Mel_Charles
      • 3 days ago
      • Reported - view

       that's encouraging Fred.

      I can't easily upload the DB as a) it has over 900k records and obviously holds lots of client data.

      I will try it in my cut down testing DB and see how I get on there. then if needed upload a sample

    • Mel_Charles
    • 3 days ago
    • Reported - view

    Fred - I am stupid

    My script was working but just not showing the update to the first screen full of records. I was sitting on record 1 but it was updating other records (off screen so to speak). I guess it might be to do with Table field sort order as by defaul th ellisting is for null or false records to show first! and thus the screen refresh was moving updated records out of the way or to the bottom of the list by default!

    As after resorting to descending order the table shows the info - However, after running the script for 3 times on test updating 3 records at a time, I see my script is flawed in that on each run it is resetting to 1 and I need to go back to other version to test test for last max number OR run the script to all 63000 records in one go in this particular table - ha ha 

    Cheers Fred ! 

      • Fred
      • 3 days ago
      • Reported - view

       We have all done that. I'm glad it is working. As always, there is the need to iterate the code as we discover that it doesn't do exactly what we want the first time around. Good luck.

    • Mel_Charles
    • 3 days ago
    • Reported - view

    Actually it is doing it by table import order (ie internal Ninox creation ID)

    I have added a formula to point to ID and sortedon table on that and now I cvan see each record in turn updating.

    live and learn ! 😄

      • Mel_Charles
      • 3 days ago
      • Reported - view

       and now sort it to stay in syc as pressing the button in first record skips that record as cound 0,1,3 punched only 1 and 2 data in the next two records 

      so as you can see id ! is blank (button pressed in record 1)

      and RecNumb is 1 but in ID 2 etc.

       but just sorted it by chnaging

      RefNum, 1, 3); to this RefNum, 0, 3); in the script

      Fab !  😁

Login to reply

Content aside

  • 3 days agoLast active
  • 6Replies
  • 15Views
  • 2 Following