Choice field: There can be only one!
Hi All, sorry for the daft title.
I am trying to establish a rule in a sub-table where only one choice field in all the records can should as active, when that record is set to active all other choice fields in the records contained in the sub table must go to inactive.
I wan't to establish a price list for products with a new record every time there is price change. I wan't to be able to see the history of the prices (hence the separate records) but only have 1 as active which is defined by the choice field.
I have searched the forum but no joy, my search terms might be inadequate.
Apologies that I have not attached examples/images here. In a moment of frustration, I deleted the sub table and not yet re-built it.
Any suggestions would be gratefully received.
Hi, An image that I hope might explain it better:
My hope is to be able to create a new record, make it active and have the previous "active" record change too inactive. I don't want the new record to be automatically active (hence no default value used) as I may need to establish a cost for a future date.
One possible solution is to put this in the Trigger after update of the Status field:
let t := this; if Status then for loop1 in (select child)[toTable2 = t.toTable2 and Id != t] do loop1.(Status := false) end end
For me Status is a Yes/No field. You will need to change the appropriate table and field names to match your DB.
Line 1 creates a variable t and puts the ninox record reference ID into the variable.
Lines 2 - 6 is a if command that checks to see if the Status field is set to Yes/True then we will run the following for loop command.
Lines 3 is the start of the for loop command to find all records in the child table that is related to the parent record except for the current record we are on and then set the Status to false/No.
I hope this helps.