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 

5replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
    • Rob
    • Rob
    • 9 days 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 

    Like
    • Fred
    • Fred
    • 9 days 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.

    Like
    • Rob
    • Rob
    • 9 days ago
    • Reported - view

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

    I'll be giving it a try shortly.

    Thanks again

    Like
    • John Halls
    • John_Halls
    • 9 days 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

    Like
    • Rob
    • Rob
    • 4 days 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.

    Like
Like Follow
  • 4 days agoLast active
  • 5Replies
  • 39Views
  • 3 Following