0

Script to update records in another table

So I for this scenario I have 2 tables. One is called DID the other is called E911.  I have data in both tables.   In both tables there is a piece of information that may be the same.  In DID I have a field DID Number (text field) and a field E911 (Yes/No field).  In E911 I have Associated Number (text field), Date of Last Update, Location name, and Location(which is linked from a table called Addresses). 

Since both tables have data I really don't want to have to empty one and manually populate or copy/paste.  I would like to accomplish two things.  First to have a script run on update that checks the DID table field E911 (Yes/No) and if yes to force entering data into the E911 table (location etc...).  

Below is the script as written thus far, though I am running into issues.  In particular in the update script on the DID table it fails on the field name (which I have verified is correct).

if this.E911 then
    let existingRecord := (select E911 where 'Associated Number' = this.'DID Number');

    if count(existingRecord) = 0 then
        let newRecord := (create E911);
        newRecord.('Associated Number' := this.'DID Number');
        newRecord.('Company' := this.Company); // Adjust field name if necessary
        newRecord.('Location' := this.Location); // Adjust field name if necessary
    else
        for record in existingRecord do
            record.('Company' := this.Company); // Adjust field name if necessary
            record.('Location' := this.Location); // Adjust field name if necessary
        end
    end
else
    let existingRecord := (select E911 where 'Associated Number' = this.'DID Number');
    if count(existingRecord) > 0 then
        for record in existingRecord do
            delete record;
        end
    end
end

Once I get this part working I will work on the second part which is a script to compare the two tables and update accordingly.  Ideally I would only want the "Numbers" stored in the DID table and referenced in the E911 table only if the E911 (Yes/No) is set to Yes.

My guess is I am making this a lot harder than it needs to be.  I have done some database work in the past but it is not my day to day job and its been a few years.  Any and all help/tips/points in the right direction are welcomed.

Thanks,

3 replies

null
    • John_Halls
    • 6 mths ago
    • Reported - view

    Well, I've just spent half an hour writing a detailed reply, only to lose it!

    Sorry about that...

    • Fred
    • 6 mths ago
    • Reported - view
     said:
    though I am running into issues.  In particular in the update script on the DID table it fails on the field name (which I have verified is correct).

     Are you talking about lines 16 - 21 of your script?

    I'm not sure what you mean by "fails on the field name"?

    Just a tip, lines 2 and 16 are the same so you can move it on top of the first if statement, thus it becomes line 1. You don't have to change anything else, an you have removed 1 line of code. :)

    When you are referencing a variable that is a record you don't need to repeat the variable name. You can do:

    newRecord.(
        'Associated Number' := this.'DID Number';
        'Company' := this.Company; // Adjust field name if necessary
        'Location' := this.Location // Adjust field name if necessary
    );
    • John_Halls
    • 6 mths ago
    • Reported - view

    Hi 

    You can also make


    if count(existingRecord) > 0 then
   for record in existingRecord do
      delete record;
   end
end

    become

    for record in existingRecord do
   delete record;
end

    Because if count(existingRecord) is zero it will do nothing anyway.

    I would also assign this a variable at the start of your script and then replace this with the variabe through out. It's just good practise with Ninox scripts.

Login to reply

Content aside

  • 6 mths agoLast active
  • 3Replies
  • 124Views
  • 3 Following