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
-
Well, I've just spent half an hour writing a detailed reply, only to lose it!
Sorry about that...
-
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 );
-
Hi
You can also make
if count(existingRecord) > 0 then for record in existingRecord do delete record; end endbecome
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.
Content aside
- 3 mths agoLast active
- 3Replies
- 69Views
-
3
Following