0

Copy Data from one for into another then open that new record

Hi 

Having a bit of a blockage here

I want to copy a few selected fields from one table to another table (which also has slighlly different fields names) so can't simply use duplicate(this) etc.

The current table is called 'LeadsData' and the script works fine as below to copy the dat into a differend table called 'Customers'

Basically if a Lead Places and order etc then transfering into current Customers !

let xCurrRec := Id;
let xCName := 'Company Name';
let xFName := 'First Name';
let xLName := 'Last Name';
let xEml := 'Email Address';
let xPhone := 'Contact Phone';
let xAdd := Address;
'Opt Out' := 2;
Won := 2;
let i := (create Customers);
i.(Company := xCName);
i.(FirstName := xFName);
i.(LastName := xLName);
i.(Email := xEml);
i.(Phone := xPhone);
i.(Address := xAdd)

This script works no problem but after copying I then want to go on and open up that new record in the customers table for further processing of other data to be manually added.

I have tried various conbinations of openRecord/PopupRecord etc but have not got the script quite right as all attempts seem to be ignored

How do i get the Id of the newly created record? as this will be different from that in the LeadsData table

I'm sure I have seen this or similar question on the forum but can't get a search to find it

Thankyou in advance

Mel

10 replies

null
    • Ninox partner
    • RoSoft_Steven.1
    • 3 yrs ago
    • Reported - view

    openRecord(i)

    • Mel_Charles
    • 3 yrs ago
    • Reported - view

    Hi Steven

    Thanks 

    I have tried that but for some reason it is ignoring it. ?

    • Mel_Charles
    • 3 yrs ago
    • Reported - view

    Hi Steven

    strange - I tried this yesterday morning along with other options and Ninox was not playing ball.

    Retried it this morning by taking all the script out closing the button/saving and re entering (just by copy/paste)

    Adding the openRecord(i) again and now it works?

    I have encountered this before sometimes a perfectly valid script simply does not work. ??

    • Mel_Charles
    • 3 yrs ago
    • Reported - view

    Actually Stephen / other fleeow members

    You may be able to help with an extension to this based on the above prior code

    I want to be able to transfer all records from tableOne to tableTwo where Archive = "yes"

    tableOne and tableTwo are identical (tableTwo will be an used as an archive storage table

    so basically want to do - script in englich is - For tableOne if Archive = yes then duplicate all those records (all fields) into tableTwo

    and delete same records in tableOnee

     

    i was thinking something along the lines of this extract

    for 'Archive' in select 'tableOne' where "yes" do
    duplicate(this);
    create TableTwo;

    then go on to change some further details

    ie 'Archive Date' = today()

    then delete same batch of records in tableOne

    end

     

    Except I don't have this right and know there is something missing

    as testing this simply adds duplicate records to existing table

    any pointers would be gratefully welcomed

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

    Something in the line of:

    for i in select tableOne do  (here we take all the Id's from tableOne one at a time in variable i and loops one by one)

    if i.Archive = "Yes" then (this checks if the current record with Id=i, contains a "Yes" in the field Archive)

    let a := i.FieldA;  (this put's a field from tableOne in a variable)

    let b := i.FieldB;  (same here)

    let c := create tableTwo; (here we create a record in TableTwo and the Id is kept in variable c)

    c.(FieldA := a); (this puts the variable a containing the data from tableOne in FieldA from tableTwo)

    c.(FieldB := b); (same here)

    end

    end

     

    This is not tested en may need some tweaking....

    Steven

    • John_Halls
    • 3 yrs ago
    • Reported - view

    The if statement can be taken out by tweaking the line before, so you end up with

    for i in select tableOne[Archive="Yes"] do

       let a := i.FieldA;

       let b := i.FieldB;

       let c := create tableTwo;

       c.(FieldA := a);

       c.(FieldB := b);

    end

    • Mel_Charles
    • 3 yrs ago
    • Reported - view

    Thats great - thanks

    Can you suggest a variation on this for doing the job in the SAME table

    I have 800,000 records that have a telephone number in - Except they are missing the leadin zero's (0) eg 123456789

    so i have formula field (fxTemp) next to 'Telephone' field that has script in it "0"+ Phone which give me the proper number eg 0123456789

    plus a 3rd enty field that I want to copy the formula field content into called Phone

    (then I will delete both Telphone and fxTemp fields afterwards)

     

    for i in select Table1 do
    if i.Phone = null then
    let a := i.fxTemp;
    Phone := a
    end
    end

    but this is not looping through the records

    just update same record over and over ????

    • John_Halls
    • 3 yrs ago
    • Reported - view

    Hi Mel

    I think you need to change Phone:=a to i.Phone:=a 

    for i in select Table1[Phone=null] do
       let a := i.fxTemp;
       i.Phone := a
    end

    Regards

    John

    • Mel_Charles
    • 3 yrs ago
    • Reported - view

    Hi John

    Thanks for the update .... Works a treat :-)

    At first was not sure was working as appeared to be hung

    but with over 800,000 records to update it was updating off screen

    Brilliant !

    Kind regards

    Mel

    • Mel_Charles
    • 3 yrs ago
    • Reported - view

    I have not done the table a to b yet but thanks in advance both John and Steven

    Mel

Login to reply

Content aside

  • 3 yrs agoLast active
  • 10Replies
  • 1085Views