12 Replies Latest reply on Jun 22, 2017 10:36 AM by barand

    Store counts of found sets

    barand

      Hello Filemaker users,

       

      I have a table in which there are three fields which are important for my question. These fields are: Category, Question, Answer. Each category has a button in the header part of the list-view layout. I want to be able to have the colour of a button change to green when all the questions have been answered.

       

      I tried to create a variable that keeps track of the amount of questions and amount of Answers that are answered. when these values are equal the button's colour changes to green. This works for the particular button that has been pressed on. When I press another button the previous button's colour gets reset to blank.

       

      Do I have to create a related table with the count values stored in fields? Or is there another way?

       

      Kind regards,

       

      Baran

        • 1. Re: Store counts of found sets
          Stigge

          Can that be that you only changed the Active/inFocus colour for example ?

          • 2. Re: Store counts of found sets
            fmpdude

            Couldn't you just do something like having a calculated field like this:

             

            ExecuteSQL("select count(*) where answered <> 1","',"")

             

            So, assuming you had a field for each question, and as the user answered each question, you set a field (maybe not on the layout) called "answered" = 1, you could do a SQL on the table for any questions where "answered" was not 1. If no questions exist with an answered field = 1, you can conclude all questions are answered.

             

            Then, define you custom formatting for the green to be based on that SQL's calculated field's result.

            • 3. Re: Store counts of found sets
              coherentkris

              ExecuteSQL("select count(*) where answered <> 1","',"")

               

              SELECT Count(*) FROM the_table becomes horribly inefficient when the_table grows in width ( more fields), height ( more records) or both width and height.

               

              Seems like it would be better to put an unstored calculation field in the table = Count ( field1: field2; fieldn) and use that as the pivot for conditional formatting that way your only operating on the record in focus NOT the entire table.

              • 4. Re: Store counts of found sets
                fmpdude

                In my pseudo-code reply, I was thinking there would be a short set of questions where ExecuteSQL would NOT be inefficient. Perhaps COUNT("field_name") would be better. I don't have any issues with COUNT(*) in other environments and I avoid SQL altogether in FMP when there is really any data beyond short little data sets.

                 

                Agreed ..your idea of a COUNT field is good. Calculation fields are super nice in FMP.

                 

                It's up to the OP to jump in here, try both solutions, and maybe others, and let us know.

                 

                Thanks,

                • 5. Re: Store counts of found sets
                  philmodjunk

                  There are other ways to get such a count. How you count completed questions depends on whether you have multiple questions in one record or whether each question is answered using a different record.

                  • 6. Re: Store counts of found sets
                    BruceHerbach

                    Does each button have it's own set of Variables?  What happens when you click the button?

                     

                    If you use a set of Global variables for each button and leave the values in place when you click the next button,  the conditional formatting/button color should stay.

                     

                    It might be helpful if you posted the scripts that the buttons use.

                    • 7. Re: Store counts of found sets
                      barand

                      Thank you for the responses. philmodjunk each answer is a separate row.

                       

                      BruceHerbach I have attached the script and layout in the attachmentbuttons and questions.PNG

                      Script.PNG

                      • 8. Re: Store counts of found sets
                        philmodjunk

                        "I tried to create a variable that keeps track of the amount of questions and amount of Answers that are answered. when these values are equal the button's colour changes to green. This works for the particular button that has been pressed on. When I press another button the previous button's colour gets reset to blank."

                         

                        Sounds like you are using one variable for all your button's instead of a different variable for each button.

                         

                        In addition to using SQL, you could also use a self join to get both a count of the user's records in a category and the number answered to be used with a conditional format to control button fill color.

                        • 9. Re: Store counts of found sets
                          BruceHerbach

                          Hi,

                           

                          Did you try setting up each button with its own set of Global  ($$Variable ) variables. 

                           

                          So the formula for button 1 could be:

                          $$AllQuestions = $$button1Answered

                           

                          Everytime they answer a question the script would increment $$buttonXAnswered variable.

                           

                          By having a variable for each button,  they should always display correctly.

                           

                          HTH

                          • 10. Re: Store counts of found sets
                            barand

                            Thanks to the filemaker hosting company I was able to fix the problem. This was done by doing the following:

                             

                            Relationships:

                             

                            Created a duplicate table of the child table for each category. Each table shows results based on 2 matches, which are: ParentKey - ChildKey and Category - Category.

                             

                            Found Sets.PNG

                             

                            Fields that were added:

                             

                            Table Formulieren

                             

                            Added fields in Formulieren.PNG

                            Table IngevuldFormulier

                            Added fields in Ingevuldformulier.PNG

                             

                            The result:

                            The result.PNG

                            Thank you for the help!

                             

                            Kind regards,

                             

                            Baran

                            • 11. Re: Store counts of found sets
                              BruceRobertson
                              Created a duplicate table of the child table for each category.

                              Really? A duplicate table?

                              Or do you mean; a duplicate table occurrence?

                              • 12. Re: Store counts of found sets
                                barand

                                Sorry, A duplicate table occurence