4 Replies Latest reply on Aug 19, 2013 2:32 AM by davehob

    Toggling multi-choice attributes


      I’m in the process of re-writing an application in FM12, and there’s something I want to get right at this early stage.


      In a People table, there are lots of attributes, such as disabled or not, gender, sexuality, etc. Some of these are simple Yes/No attributes (e.g. disabled), whilst others have 2 or more options. (Actually, these will be in a separate “attributes” table, but logically they are part of a People record.) I want to use Boolean fields wherever I can, to make the process of later analysis as easy as possible.


      With a Yes/No attribute, it’s simply a case of having a field, “IsDisabled”, with a button that does:
      Set Field [PPL::IsDisabled; not PPL::IsDisabled]
      to toggle the field. A nice bit of conditional formatting on the button to show “on” or “off”, and all is well.

      But what’s the best way to handle, say, the gender, or other attributes with up to 10 settings, where if one option is “on”, all others are “off”?

      Ideally, a single script will run when user clicks on “Male” button, which will set “IsMale” to 1 and “IsFemale” to 0, but I can’t see how to do this without a multi-step script, which seems a bit elaborate. And where there are more options (e.g. ethnicity, sexuality, etc.) it gets more wordy still (i.e. unset all the Booleans, then set the one selected). Not a huge problem, admittedly, but I can’t help feeling there’s a better way to do it.

      Or should I just have separate, value list-based fields, with separate Boolean calc fields (“IsMale”, “IsFemale”) set when Gender is entered or altered?





        • 1. Re: Toggling multi-choice attributes

          Theoretically it is simple to have an attributes table that has two fields: attribute and value. You can then provide the attribute field with a label, ie, gender, and the value field with male or female. The next record might have a different attribute, such as "ethnicity."


          This sort of construction is useful in circumstances where you might expect users to collect ad-hoc data. An example might be a contacts database where you want a knowledgeable sales team to be able to extend the data set in unusual ways, such as "football team: Dockers" or "children: esther, will".


          There is an absolute minimum in overhead in creating this system when you only want to put data in. But you need to manage the way in which you report on these. A report on gender has to find all "attribute"="gender" and then summarise by "value".  That's kind of extra work may be fine for information that you only want to report on occasionally. However, If you expect the majority of records to carry a value for any of these attributes then you should simply make a field for them. Whether you store them in the same table or in a related table with a one to one relationship is up to you.



          • 2. Re: Toggling multi-choice attributes

            Hi Malcolm,


            Thanks for this advice.  I don't need the flexibility of adding ad-hoc data in unusual ways, and, yes, most of the records will have values for some of the attributes ("gender", "ethnicity", etc.), but not for others ("likes football", "trustee", etc.).  So while I'm keen to treat all attributes in the same way (in the past I've been tripped up by not doing so, when it comes to reporting), I think you're suggesting that I use separate fields for some (i.e. the ones that are held by the majority), but a separate attributes table for the rest.  Or have I misunderstood?



            • 3. Re: Toggling multi-choice attributes

              That's mostly right.


              Building this sort of thing for collecting and displaying attributes against a record is easily done. The real trick is to factor in the reporting component.


              If you need to report on the unusual attributes at the same time as the common attributes then it may be better to have one system. Otherwise you have to generate two reports using different methods and that increases workload/complexity.



              1 of 1 people found this helpful
              • 4. Re: Toggling multi-choice attributes

                Thanks again Malcolm.  I'm going to sit on it for a bit - part of me is thinking that all attributes belong in the same bucket (and I guess in terms of mormalisation rules that's definitely the case), but when FM makes it so easy to do it otherwise (i.e. with a simple "gender" field, and a couple of Boolean calcs - "IsMale" and "IsFemale" - for counting and reporting), it's also tempting to go down that route.