0

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.

Many thanks 

5 replies

null
    • Rob
    • 1 yr ago
    • Reported - view

    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.

    Many thanks 

    • Fred
    • 1 yr ago
    • Reported - view

    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.

    • Rob
    • 1 yr ago
    • Reported - view

    Hi Fred, many thanks for responding. Very much appreciated.

    I'll be giving it a try shortly.

    Thanks again

    • John_Halls
    • 1 yr ago
    • Reported - view

    Where you have a 1:n relationship for all the sub-table entries, I would have a second relationship but the other way round n:1, just for the active record. That way it can be easily viewed and accessed in other parts of your system. 
     

    regards John

    • Rob
    • 1 yr ago
    • Reported - view

    Hi Fred, Thats works perfectly 😃

    Many thanks for your guidance. Exactly the function I was looking for.

    And John; thank you for your advice as well.

    Very much appreciated.

Login to reply

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 5Replies
  • 111Views
  • 3 Following