Dynamic Choice triggers twice

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?

In answer to John's question, no, certainly not. Oh if it were that simple. :-)

 

In any case, this is clear since the trigger script is only executed once unless I include the line that changes that DC field's value. That is what then triggers it again.

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.

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.

 

https://www.dropbox.com/s/obvc517s9bun723/Spectest.ninox?dl=0

Thanks Alan. I knew it had to be something like that. I guess I just didn't try hard enough.

 

Would be interested to know if anyone else sees the same problem when using the test db above.

Sory Alain. I would correct what I inadvertently typed (fingers overriding brain) but cannot. My apologies for typing your name slightly wrong.

Oh boy. Brain just doesn't seem to be in gear. Of course that should be "Sorry Alain".

Ninox have replicated the problem and will introduce a fix sometime as it should NOT happen.

 

They have confirmed the problem only occurs in the Mac app and not on the web version.

In the intervening period, Ninox have fixed this issue. It all now works as it should.