Turn records on or off

[Database Designs]

I'm working on a database that has an "On / Off" field displayed as radio buttons. Is there any way that if I turn 1 record to "On" all of the other records would be "Off". I can only have 1 record On at any one time.

[Raybaudi]

Something like this ?

OnOff.zip

[Database Designs]

Yes...exactly. But are you running the "Replace" function on all of the other records? If I get up to 30,000 records will it take long?

[Raybaudi]

No, I think that this is the quickyer way, given the fact that the field "OnOff" can't be a calculated one.

Also may be that changing that field from text to number ( with 1 and 0 formatted to show On and Off ) can make the script even quickyer.

But the script must be modified if you want to mantain a found set.

[Database Designs]

Thank you. This should work just fine.

[comment]

A convenient way to deal with this situation would be to store the ID of the selected record in a preference field. This could be a global field (for the current session only) or a "real" field in another table (for a permanent change, affecting all users).

Trying to replace data in multiple records can easily fail in a multi-user setup, where some records can be locked.

[Raybaudi]

Hi comment

If I understand you correctly you suggest to store the record Id corresponding to the field value "On" into a field and after, if another record get the "On", we will set to "Off" the field with that record Id and will keep the new record Id.

But how exactly, always with a script ?

[comment]

I am not sure we are on the same page:

Mark.fp7.zip