3 Replies Latest reply on May 13, 2011 6:40 PM by LaRetta_1

    calculating a field based on checkbox results from multiple other fields

    ThomasWaldron

      Title

      calculating a field based on checkbox results from multiple other fields

      Post

      I'm sorry for a question like this, but i'm on a timer and can't seem to get this to work on my own. 

      I'm something of a filemaker newbie, but I'm still the one that knows FM best in this office. 

      I'm trying to create a dashboard report listing a number of candidate records and details related to that person. I've got everything working beautifully except one thing. i need a field that reports on the candidate's status, based on the results of several other fields, which are already set up as checkboxes (yes/no/maybe). i could change that to dropdown or some other form, but it's how it's been set up here for years and changing might make it all that more difficult to get them to use it. 

      so in my report (for which i've made a list layout) the status field needs to indicate where that member is in a process: initial interview yes/no/maybe, initial interview status pending/scheduled/complete, secondary interview yes/no/maybe, proceed to semifinals yes/no/maybe, proceed to finals yes/no/maybe,with a date for the first two, OR have that checkboxed status be overwritten by a "dashboard notes" field.

      that last part i can figure out, but i can't find the best way to pull the proper checkbox, replace the simple "complete" with "Initial interview complete" (or whatever), and only pull from the highest level checkbox (i don't need to know if they've made the initial interview if they're proceeding to the final round). let alone adding in the date (i have a separate date field, which i imagine i merge in using an "if", but i haven't played with it enough to know how). 

      can anyone suggest the best way of putting this together? I'm sorry for asking like this - everything else i've been able to figure out on my own but I'm on a tight time crunch now. :\ thank you in advance for any suggestions you might have! 

        • 1. Re: calculating a field based on checkbox results from multiple other fields
          aammondd

          If the structure is sequential then using nested if logic starting with the end and working backwards would be best.

           

          something like

          if notes field is null

           if checkbox5status = unchecked

            if checkbox4status = unchecked

              if checbox3status = unchecked

               if checkbox2status = unchecked

                  set finalstatus = checbox1status

                  else

                 set finalstatus = checbox1status

                endif

               else

               set finalstatus = checbox2status

               endif

              else

              set finalstatus = checbox3status

              end if

            else

            set finalstatus = checbox5status

            end if

          else

          set finalstatus = notes

          end if

          (This is simply logical code)

          • 3. Re: calculating a field based on checkbox results from multiple other fields
            LaRetta_1

            Hi Thomas,

            I would approach this a bit differently (since you simply cannot change the structure now) but I would change it as soon as you possibly can.  I would use a calculation to code the final Checkbox Status and let it run by itself. 

            "OR have that checkboxed status be overwritten by a "dashboard notes" field."

            I would never overwrite notes.  It never hurts to have a notes field to show how the interview progressed; in fact I would suggest a notes table but I will skip that for now).

            I am still unclear on the theory here ... what would you do if the checkbox Proceed To Finals contains 'maybe' but the next checkbox Proceed to Finals contains 'no'? I believe we need clearer rules and once we do, a Case() calculation can handle it easily. 

            I made one assumption that, if Proceed To Finals contains a 'no' that the person made it clear through but was not selected so I filled the field with 'Complete'.   Case() calculations stop evaluating when they hit the first true so by starting at the END, we only need to continue testing if the first test in line fails.

            Case (
            proceed to finals = "no" ; "Complete" ;
            not IsEmpty ( proceed to finals ) ;
              "Finals " & proceed to finals  ;
            not IsEmpty ( proceed to finals ) ;
              "Finals " & proceed to finals  ;
            not IsEmpty ( proceed to semifinals ) ;
               "Semifinals " & proceed to semifinals  ;
            not IsEmpty ( secondary interview ) ;
               "Secondary Interview " & secondary interview  & " " & secondary interview Date  ;
            not IsEmpty ( initial interview status ) ;
              "Initial Interview Status " & initial interview status  ;
            not IsEmpty ( initial interview ) ;
            "Initial Interview " & initial interview & " " & initial interview date
            )

            Create this calculation and see what it gives you.  We can adjust it once we fine-tune the rules.