0

Coping Multiple Records from One Subtable to Another

I have table named 'Trial Setups' with a subtable named 'Tech Sheet'. I also have a table named 'Generic Setups' with a subtable named 'Generic Tech Sheet'. 

 

In 'Trial Setups' I have reference from 'Generic Setups'. As seen here:

Screen Shot

What I want to accomplish is formulating the button "Apply Selected Generic" to copy the selected Generic Setup's Generic Tech Sheet to the Trial Setup's Tech Sheet.

 

I have searched the forums and discovered the "Automatic Move Data from Table A to Table B" example seen here:

 

let reply := dialog("", "Would you like to continue moving this record to Table A", ["Yes", "No"]);
if reply = "Yes" then
let TempA := (create 'Table A');
let myT := Text;
let myN := Number;
TempA.(Text := myT);
TempA.(Number := myN);
openRecord(TempA);
alert("Data moved successfully")
end

I have messed around with it, but I can't figure out how to apply it to what I want to achieve because I'm trying to copy multple records. I'm assuming I need to be capturing the data within an array, but I'm struggling with the proper syntax.

 

The columns/fields in both subtables are identical and here are a few of them: 'Print #', Product, Pantone, Mesh.

 

Any suggestions would be greatly appreciated.

11 replies

null
    • Sean
    • 5 yrs ago
    • Reported - view

    William, I think someone needs to come up with an exercise where you can finally use your array 馃槈. Most tasks can be completed without using arrays in Ninox. Please see this familiar thread...

     

    https://ninoxdb.de/en/forum/technical-help-5ab8fe445fe2b42b7dd39ee7/renumbering-subtable-records-5d52bce44e061c58ea3a25c5?post=5d5338de88bfaf262e9df309&page=1

     

    You should be able to use a for-loop to cycle through the records you want to copy over.

    • Avient Specialty Inks
    • Bill.1
    • 5 yrs ago
    • Reported - view

    Sean,

     

    I've attempted to incorporate the for-loop but it's telling me that the expression returns multiple records for each variable. Here's what I have as of now (just copying the first two columns until I get it working):

     

    let reply := dialog("", "You are about to apply the selected generic set-up. Continue?", ["Yes", "No"]);
    if reply = "Yes" then
    let myID := this;
    let pNum := 'Generic Setups'.'Generic Tech Sheet'.'Print #';
    let prod := 'Generic Setups'.'Generic Tech Sheet'.Product;
    let TempA := (create 'Tech Sheet');
    TempA.(for r in (select 'Generic Tech Sheet')['Generic Setups' = myID].Id order by 'Print #' do
    r.('Print #' := pNum);
    r.(Product := prod))
    end
    end

    Let me know if you have any suggestions.

    • Sean
    • 5 yrs ago
    • Reported - view

    William, try this...

     

    let reply := dialog("", "You are about to apply the selected generic set-up. Continue?", ["Yes", "No"]);
    if reply = "Yes" then
    let myID := this.Id;
    for r in (select 'Generic Tech Sheet')[Id = myID] do
    let TempA := (create 'Tech Sheet');
    TempA.'Print #' := r.'Print #';
    TempA.Product := r.Product;
    end
    end

     

    The editor will probably add parentheses to what I posted. It might take some tweaking, but it should work as is. Let me know.

    • Sean
    • 5 yrs ago
    • Reported - view

    Change

    [Id = myId]

    to

    [number(Id) = myID]

     

    Sorry about that.

    • Avient Specialty Inks
    • Bill.1
    • 5 yrs ago
    • Reported - view

    Sean,

     

    I've tried reworking it to no avail. It does not populate any new records in 'Tech Sheets' as is.

     

    Let me know if you have any further suggestions and I'll keep messing with it to see if I can get it to work.

    • Sean
    • 5 yrs ago
    • Reported - view

    I realized you are trying to use the Ninox Id and that's not going to work. I guess I was thinking of your T#. If you have an Id # that is not the Ninox Id # you'll need to use that.

    • Avient Specialty Inks
    • Bill.1
    • 5 yrs ago
    • Reported - view

    Sean,

     

    I'm getting very close to a resolution. The only issue I'm encountering now is that it's copying all of the 'Generic Tech Sheets' from 'Generic Setups' and not just the one selected.

     

    Here's the code:

     

    let reply := dialog("", "You are about to apply the selected generic set-up. Continue?", ["Yes", "No"]);
    if reply = "Yes" then
    let myID := this;
    for r in (select 'Generic Tech Sheet')['Generic Setups'] do
    let newRecord := (create 'Tech Sheet');
    newRecord.('Applies to:' := myID);
    newRecord.(select 'Tech Sheet')['Applies to:'];
    newRecord.('Print #' := r.'Print #');
    newRecord.('Existing Product' := r.'Existing Product');
    newRecord.(Product := r.Product);
    newRecord.('Sample Product' := r.'Sample Product');
    newRecord.(Color := r.Color);
    newRecord.(Mesh := r.Mesh);
    newRecord.('Off Contact' := r.'Off Contact');
    newRecord.(Durometer := r.Durometer);
    newRecord.(Angle := r.Angle);
    newRecord.(Stroke := r.Stroke);
    newRecord.(Pressure := r.Pressure);
    newRecord.(Speed := r.Speed);
    newRecord.(Flash := r.Flash);
    newRecord.('Flash Temp' := r.'Flash Temp');
    newRecord.(Dwell := r.Dwell);
    newRecord.(Index := r.Index);
    newRecord.(Cool := r.Cool)
    end
    end

     

    And here's the result:

    Screen Shot 2019-08-26 at 11.28.55 PM

     

    I only have two 'Generic Setups' at the moment and the button is copying both to the 'Tech Sheet'.

     

    I've tried a slew of syntax options at the begining of the code in an attempt to get the correct result, but either it ends up producing the same result or not working at all.

     

    Hopefully it's an easy fix and I can finally put this one to bed. Let me know if you have any recommendations.

    • Sean
    • 5 yrs ago
    • Reported - view

    William, in the brackets in the following line...

     

    for r in (select 'Generic Tech Sheet')['Generic Setups'] do

     

    'Generic Setups' is the group being filtered, but you don't have a filter criteria. It should be like...

     

    for r in (select 'Generic Tech Sheet')['Generic Setups' = yourCriteria] do

    or

    for r in (select 'Generic Tech Sheet')['Generic Setup Id' = yourId] do

     

    If you have, or can add, an Id field then you can filter based on that. I generally don't use the Ninox Id number as a filter criteria. Once, you do that you can probably drop...

     

    newRecord.('Applies to:' := myID);
    newRecord.(select 'Tech Sheet')['Applies to:'];

    • Sean
    • 5 yrs ago
    • Reported - view

    One other suggestion. If each 'Generic Setup' is a single record then this should work and you can use the Ninox Id # without using a for-loop. Create a variable to copy the record to like this (assumes 'Generice Setup' is the table where you are getting the record from)...

     

    let GS := select 'Generic Setup' where number(Id) = yourId;

     

    After you create the new record you can assign values from GS to newRecord like this...

     

    newRecord.('Print #' := GS.'Print #');
    newRecord.('Existing Product' := GS.'Existing Product');
    newRecord.(Product := GS.Product);
    newRecord.('Sample Product' := GS.'Sample Product');
    newRecord.(Color := GS.Color);

    etc

    • Avient Specialty Inks
    • Bill.1
    • 5 yrs ago
    • Reported - view

    As for your first suggestion, any time I tried to specify a criteria the button would fail to populate the table. Your second suggestion wouldn't work because there's more than one record associated with a 'Generic Setups'. However, the combination of your two suggestions made me recall the last time I had a similar issue, and again (like last time) I was making things more complicated then they needed to be. Turns out I just had to call the field at the start of the code like so:

     

    for r in 'Generic Setups'.'Generic Tech Sheet' do

     

    Leaving the rest of the code untouched.

     

    Thanks again for the help on this!

    • Sean
    • 5 yrs ago
    • Reported - view

    You're welcome.  :)

Login to reply

Content aside

  • 5 yrs agoLast active
  • 11Replies
  • 3118Views