3 Replies Latest reply on Aug 23, 2012 11:21 AM by comment

    Report of FIND records that are "yes"

    theturk15

      I have a simple set of N records. Rows (records) are projects, columns are a series of surveys administered in the conduct of the project. The user adds a new record and then clicks a 'yes' for a series of checkboxs for the particular surveys used in that project. If a survey was not used for any particular project, the field is left blank (i.e. the NO checkbox option is not checked. If that's an issue, it can be changed).

       

      Project mgr would like a report of what projects used which surveys. So the report would be ProjectName and then a list of the survey instruments that have the YES checkbox checked for that project (result could be from only 1 to upwards or maybe a dozen). Project mgr also wants the reverse - each survey name and what projects used that particular survey.

       

      Seems like it's an application of an OR search. Conceptually, it's sort of like 'if survey1=yes OR survey2=yes , , , OR surveyN=yes" results in a list of surveys for that particular item.

       

      Think I see three problems: 1. can't seem to figure out how to FIND which records have the YES checkbox checked. 2. I'm assuming a button is the preferred route to execute the search (the users are less Filemaker savvy than I, so this needs to be simple for the user-click a button, execute the search) and 3. I have 61 survey instruments.

       

      Full disclosure-I'm not a developer, just a casual user who is a novice at scripting.

        • 1. Re: Report of FIND records that are "yes"
          comment

          theturk15 wrote:

           

          1. can't seem to figure out how to FIND which records have the YES checkbox checked.

           

          You would look for records where the field contains the text "YES" (assuming that's what your value list contains).

           

           

           

          theturk15 wrote:

           

          2. I'm assuming a button is the preferred route to execute the search

           

          That's largely a matter of preference. Perhaps you should use global fields formatted as checkbox set, where the user would indicate which projects/surveys to include.

           

           

           

          theturk15 wrote:

           

          Rows (records) are projects, columns are a series of surveys administered in the conduct of the project.

          ...

          If that's an issue, it can be changed).

           

          It is an issue, because adding another survey should be in the province of the user, and not require adding another field to the schema (among other things). I would suggest a table of Projects, a table of Surveys and a join table for associating the two - see an example here:

          http://www.fmforums.com/forum/showpost.php?post/246136/

           

          This would allow you to produce practically any type of report one could think of. In a pinch, you could settle for two tables, and use a checkbox field in the Projects table to record which surveys were administered. However, this approach is limited in many ways.

          • 2. Re: Report of FIND records that are "yes"
            theturk15

            Michael

             

            Thanks for the prompt reply. I opened the JoinDemo.fp7 and the example seems to fit my needs. 

             

            I struggled with conceptualizing it until I attempted to add a new record. So what it looks like I need to do is generate a new value list that includes each of the surveys and let the user choose (from that long list of 61 surveys)

             

            One followup question I ask in advance of making this revision to the structure. Currently, one enters the affiliations one by one. Is it possible to choose multiple items (command-click) or is one by one entry the best option?

             

            Thanks for the help. Hope you dont' mind if I get back to you if (and when) I get stuck.

             

            Don

            • 3. Re: Report of FIND records that are "yes"
              comment

              theturk15 wrote:

               

              let the user choose (from that long list of 61 surveys)

               

              Well, that is the basic demo. The important thing here is the data structure; the user interface can be fancied up - see, for example:

              http://fmforums.com/forum/showpost.php?post/355429/

              and:

              http://fmforums.com/forum/showpost.php?post/233897/

              or even:

              http://fmforums.com/forum/showpost.php?post/340683/