0

Copy Table Reference Data to a different table

I'm working on a database to track custom orders for my business. I have it almost working the way I need, but one bit has me stumped. I'm not a coding wizard, so I'm basically beating my face against the keyboard until I get the results I want. Here's the basic setup.

I have 4 tables. They are:

Active Orders
Ready To Ship
Complete
Customers

I have a Table Reference in Active Orders to Customers. When I create a new record in Active Orders, I choose the customer's name from Popup. After all of the other requirements for the form have been met, (Projuct status, etc) I have a button appear that will copy the record over to Ready to Ship, then delete it from Active Orders. The problem that I'm having is that everything I want copied works fine, with the exception of that Customer table's selection. I can't figure out how to get the Customer Table Reference in Ready to Ship to populate with the customer I intitially selected in Active Orders. This is the code I'm using for the button, courtsey of Nick from a few years ago:

let reply := dialog("ATTENTION", "I will move this record to Ready to Deliver / Ship. Are you sure?", ["Yes", "No"]);
if reply = "Yes" then
let myB := (create 'Ready to Deliver / Ship');
let myField1 := Name;
let myField2 := Customer.Name;
let myField3 := Paid;
let myField4 := 'Invoice / Receipt Number';
let myField5 := 'Date Accepted';
let myField6 := 'Date Due';
myB.(Name := myField1);
myB.(Customer.Name := myField2);
myB.(Paid := myField3);
myB.('Invoice / Receipt Number' := myField4);
myB.('Date Accepted' := myField5);
myB.('Date Due' := myField6);
delete this;
openRecord(myB);
alert("Record moved successfully")
end

I don't want to just filter my views with one table, as Jorg suggested in one of the posts I read looking for the button code. I need to be able to move the record, along with its linked selections from the Customer table. The reason for this is that I will eventually be adding a Raw Material table, a Bill of Materials table, a Manufacturing table, and a Product table that will all require me to move quantities of selected records around. I haven't through that through all the way so I'm starting small with this Custom Order bit. Any ideas? Thanks in advance.

5 replies

null
    • Fred
    • 2 yrs ago
    • Reported - view

    Hi Jess -

     

    I think I can help. I'm guessing that the reference field in Active Orders and Ready to Ship is called Customer, so to link the record in Ready to Ship to the same Customer that was in Active Orders you need to just reference the field as Ninox will know it is a reference field and then link the records.

     

    let myField2 := Customer.Name; becomes let myField2 := Customer;

     

    myB.(Customer.Name := myField2); becomes myB.(Customer := myField2);

     

    I know in other DBs you need to tell it what field to link on but in Ninox you don't. Took me a moment to learn this.

     

    Another thing you could find handy is the "this" "function". I don't know if it is a function or command or whatever.

     

    If you create a variable like:

     

    let curRec := this:

     

    Then you can replace all your myfieldx variables with just this one.

     

    Then in the create part it would look like:

     

    myB.(Name := curRec.Name);
    myB.(Customer:= curRec.Customer);
    myB.(Paid := curRec.Paid);
    myB.('Invoice / Receipt Number' := curRec.Inovice);

    etc

     

    This way if you add a new field or forgot a field you don't need to create a new variable to store it. What "this" does is tell Ninox to store all the data of the current record. Then you can access any data from it. It is handy as you don't need to know what fields you need to use right off the bat, but I guess it is a bit sloppy if you are only targeting one field.

     

    Anyways, good luck and let us know how it goes.

    • S&S Woodsmithing
    • Jess_Spiers
    • 2 yrs ago
    • Reported - view

    Nice. Thanks Fred. I appreciate it. I'll go give it a shot and see how it works. Thanks for the tip on the "this" function. I really don't know anything about DBs in general.. Like I said in my original post, I just smash my face into the keyboard until things work. =) 

    • S&S Woodsmithing
    • Jess_Spiers
    • 2 yrs ago
    • Reported - view

    Fred... You, sir, are a Genius. Thank you. Using Customer instead of Customer.Name totally fixed it. Now to rework things to use the "this" function like you recommended. So much to learn with Ninox. =)

    • Fred
    • 2 yrs ago
    • Reported - view

    Glad I could help.

     

    I can't tell you how many times I've "redone" my DB as I've learned things.

    • S&S Woodsmithing
    • Jess_Spiers
    • 2 yrs ago
    • Reported - view

    Lol, I bet. That's why I'm trying to build them incrementally as I learn. The first major endevor I did was my Warhammer 40k Paint colors DB and I learned a ton from it. Today's DB stemmed from my trial for Monday.com running out and not wanting to pay for a sub. I KNEW I could make something more functional and useful than their website with Ninox. Just had to figure out how. Thanks again Fred. I greatly appreciate it.

Login to reply

Content aside

  • 2 yrs agoLast active
  • 5Replies
  • 560Views