Dynamic Choice triggers twice
When trying to use a 'trigger on update' for a Dynamic Choice field, that actually changes the chosen value, the trigger runs twice. I was under the impression that sensibly a trigger is NOT activated when a field value is changed programmatically, only when the interface registers a change to the field. In which case, changing the choice of a Dynamic Choice field should NOT run the trigger a second time. It could in theory cause a race condition, although I've not seen that. Anyway…
I've not been able to come up with a way to prevent this and since the trigger also involves a calculation of a different field, the result is needless to say, nonsense.
Anyone come across this? Any way to avoid it?
I'm not sure how relevant the actual code is and may just confuse the issue, but in essence, in this table (specs) there is a Dynamic Choice field (unit) that uses the 'units' table as its source. In the 'specs' table form there are 2 fields, 'valnum' for the value and the DC field 'unit'. I want to recalculate 'valnum' and select a different choice for 'unit' using its trigger on update. I am testing with converting °F to °C:-
let factor := 0.55555556;
let offset := -32;
let centid := 5;
valnum := (valnum + offset) * factor;
unit := centid;
The 'centid' value is the 'units.Id' for the °C row in the 'units' table and if I remove that last line, 'valnum' is correctly converted from the Fahrenheit value (e.g 212) to the equivalent Centrigrade (100), although 'unit' still shows as °F. But with that last line in place, the result changes to 37.78, and 'unit' shows °C as it should. The problem is that the trigger has executed twice. I can see this if I write the result into another text field. I can see the first cycle produces the correct 100, then it runs again resulting in 37.78.
I don't think the issue here is with the code as it does what it is supposed to do. The problem is that it executes twice, first when I manually change the 'unit' field choice and then again when the trigger code sets it, changing the choice from °F to °C.
The actual code only runs when it sees 'unit' as °F and knows to convert to °C. Once the trigger changes it to °C, it knows to not run again. However, if there was no such conditional, potentially the trigger could loop forever. I'm not about to try that however.
For this reason, it is appropriate that a trigger NOT be activated when the field is changed programmatically. However, in this case it IS being run a second time. So I can only conclude it's a bug with Dynamic Choice fields. Unless there is a reason why it is supposed to trigger repeatedly. Is there?
I can't give you an asnwer but - unless I am mistaken.- As far as I'm aware this is normal practise!
"trigger on update" is what it says on the tin. Whether you update field (in this case the choice box!) manually directly or via a script causing a changed status. A change will have taken place and if your script is set to act on that choice box change , then the trigger will fire.
I have the similar thing with a GDPR choice field if the control is changed from "opted in" to "opted out" then script runs to write a copy of all the relevant fields into an Audit table.
Like wise, I have also have a script on a "change contact field" that checks for the contact change and thus deems that the old contact has been replaced and therefore by definition must be opted out so changed the dynamic choice and also adds to the audit file before finally updating the "main contact field"
so the choice list can be updated from different points - But in every case that trgger fires
Does you a trigger have to be on the choice field? Would running the trigger on one of the other fields you mention when they change work. ie do the test and have the script update the choice Dynamic Choice Field?
Hope you solve it soon for what you want thou.. could you let us know how you get on?
Often I see people ask questions on some more involved issue(s) and although they are given a few options by people with uch more exoerience than me. You don't always get to know what was the final fix (workaround).
Mel, I have read here multiple times that a trigger script is only triggered when the value is changed in the interface and NOT if the value is changed programmaticially. I have since thought about this a lot and that has to be correct since otherwise a race condition could easily result in a continuous loop which Ninox would have to detect and curtail or else simply crash.
I have just done a very simple test. The update on trigger script for a text field simply adds a character to the end of the field. If as you suggest it should trigger even when updated by itself, that would create a continuous loop as every time it runs, it changes the value and would have to run again. However, it does not. It runs once, adds the single character and exits. This supports what I have read and understand to be correct. A field's update on trigger script that changes it's own value does NOT cause itself to re-trigger.
Except it would appear in the case of a Dynamic Choice field (possibly Multiple too but not tested that). In my case it updates to a value/choice that then does NOT require any further change. So it runs the first time, makes the change which triggers the update trigger script again but which itself then does not make any changes, so the loop stops. However, it would be possible to create a script that causes continuous looping and I believe that programmatic changes to values are supposed to NOT trigger any trigger on update scripts specifically to avoid this.
I have constructed a workaround. The calculation being done on the value field basically multiplies it by a factor and now I use the square root of that factor instead. So when it runs twice, I get the correct result. It is more complicated when an offset has to be applied (as in the case of temperature conversion), but I have created a formula that works. None of this however alters the fact that I believe an update on trigger script should NEVER cause itself to re-trigger.
It would be interesting to hear from Ninox themselves on this issue, but as they don't seem to partake in these forums any more I have contacted them directly and they are looking into it. I will update this thread as I know more.
Just performed another test and indeed Mel is correct that a 'trigger on update' script DOES run when that field's value is changed even programmatically. I used a button to change the value and the script ran. But…
It certainly should NOT cause itself to re-trigger. This is the crux of the matter. The trigger on update script should execute however the field's value is changed, EXCEPT when it is changed by itself as that opens the door for a potentially catastrophic race condition. This is basically what Ninox does and it handles it all correctly except for these Dynamic (Multiple) Choice fields. Since we know there are more than a few issues with these fields (very useful though they are) it is not hugely surprising that they still have a few gotchas to iron out.
As I said, I will report back on what I hear from Ninox.
I have re-created your database with your code in the Trigger on update of the unit in the Specs table it works correctly. It doesn't run twice.
Glad you have found a work around (abeit temporary)
You have now tweaked my curiosity. I am going to try and reconstruct your situation/what you are experiencing. Not prove anything one way or the other. But simply to help develop my own abiliities (or lack of !!).
To be fair, I have had the opposite in the past - As I have had several cases where the trigger would not fire at all - even after Ninox themselves said there was now't wrong with my script(s) and script placement!
As a matter of interest are you using the Web version or Mac app?. I'm totally cloud based
and whilst at it
There are some functions that are not supported on triggers ("on create" and "after update" in the table - in the browser version!)
but only in buttons and on the "Trigger after open" in the database options:
In the Ninox Apps the function are supported also in the triggers mentioned above.
having spend days trying the get above to work as the commands are simply ignored!
as Confirmed by Ninox Support (Maria 19th July)
Yes Ninox's participation on the forum is now overwhelmingly obvious by the total lack of input! - same :-(
Thanks for trying this John. I imported your test db, added a text field (notes) and a line in the script to append Valnum to the notes field. Guess what, it runs twice.
If I enter 212, it writes 100 (as it should) and then 37.78 after it runs the second time.
Am I correct in assuming you're running the Web/Cloud version? I think this may be significant. I am running the Mac app and this might explain why we are seing different behaviours.
Slightly at a tangent, but related, as you will see, is this mathematical puzzle.
In order to perform a simple conversion by a factor when it runs twice, just use the square root of the factor so after the second run, all is correct. But…
In the example we're looking at, the °F number has that 32 offset to be removed first and then the factor is applied to get °C. How do we achieve that when it has to run twice, obviously identically each time.
I thought long and hard about how to 'adjust' that offset to get the correct result after 2 runs. I thought it had to be possible to find this 'error correction' number from the offset and the factor itself, or square root or some combination. In truth, I failed. I could not figure out a formula to find the required 'correction factor'.
So I spent a little while on a 'trial and error' experiment which revealed that I need to multiply the offset (-32 in this case) by 0.427 and then by using the square root of the conversion factor and this 'adjusted' offset, after running twice, the resulting number is correct. So:-
Valnum := (Valnum + offset * .427) * sqrt(factor);
But this bothers me hugely, for 2 reasons:-
Why? Where does .427 come from? Apart from being numerically the capacity in cubic inches of several large V8 engines from GM, what is the significance of .427?
Is it valid only for this particular conversion, or universal? I've not tried to use it for any other conversion as temperature is the only one that requires the use of any troublesome offset, but apart from testing it, since I cannot fathom how this value is derived, I do not know if it would work in any other case.
So this is just a little mathematical puzzle that I have been unable to get my head around. I have the correct answer for what I need, but I don't know why. Anyone offer any suggestions as to where .427 comes from?
First of all, if you enter 212 and then change the units, what is the result you get?
Secondly, with the code you posted, you will only see one value in Notes. Try this:-
Notes := Notes + "
" + Valnum
That will write a line into Notes each time and you will see both. Best to manually clear it before you start to make it clear what has occurred.
I also notice John you have the Units 'C' and 'F' the wrong way around. Doesn't make any difference to the test, but the script should be changing °F to °C and so needs to change Units to 'C' which is actually Id = 1 in this test database. So to be correct, either swap the 2 Units or change the script to:-
centid := 1
I changed the test db slightly to correct the above and also added a reset button. So just click the button and then change the units and see what it does. Not a big deal, but makes re-running it simpler.
Let "x" be the value that must be converted. Applying "offset" and "factor" once must give the same result as applying "o2" and "f2" twice:
(x + offset) * factor = ((x + o2) * f2 + o2) * f2
x * factor + offset * factor = x * SQR(f2) + o2 * SQR(f2) + o2 * f2
The equality must hold for any value of x, which implies:
factor = SQR(f2) and
offset * factor = o2 * SQR(f2) + o2 * f2
f2 = SQRT(factor)
offset * factor = o2 * (factor + SQRT(factor))
o2 = offset * factor / ( factor + SQRT(factor))
If factor = 0,55555556, then o2 = offset * 0,4270509841