1 2 Previous Next 28 Replies Latest reply on Aug 20, 2015 3:11 PM by gremlin9297

    Unique ID count not using ExecuteSQL

    gremlin9297

      Title

      Unique ID count not using ExecuteSQL

      Post

      Hey there,

      Really simple, I need to go from A and make it look like B.  Well not easily done but hopefully easily enough explained.

      Thanks,

      Eric

      A_to_B.png

        • 1. Re: NoFields
          schamblee

          You can easily create a summary report in FM.   You may have to create some summary fields but just start by  creating a new report layout and answer the questions on screen, FM will handle most of the work.  

           

          • 2. Re: NoFields
            philmodjunk

            And this is a report that you would use list view instead of table view in order to view and to combine the data from multiple records into a single row, remove the body layout part and just keep the sub summary layout part. Finally, don't forget to keep your records sorted by Fund or such a layout will be blank.

            Here's  a tutorial on summary reports you can work through if you need to learn more about this report option: Creating Filemaker Pro summary reports--Tutorial

            • 3. Re: NoFields
              gremlin9297

              That did help but the problem is the count. Its counting all the names. I need a count of just the unique names for each fund as in the example I provided, for Fund 1 has 4 unique names but each name shows up multiple times for each fund. Right now the summary is counting all the names instead of just showing me 4. How can i fix this?

              • 4. Re: NoFields
                philmodjunk

                For a count of unique values, this gets a bit more complicated.

                Best method is to use ExecuteSQL with the DISTINCT keyword: A new way to count unique values in FileMaker 12

                without ExecuteSQL, the rather arcane "sum the reciprocal" method may be used: How to count the number of unique occurences in field.

                It's also possible to define a conditional value list of all names with the same fund and then you can use the valuecount and valueListItems functions to count the number of values in the resulting value list. (Such value lists automatically omit duplicate values so this then also provides a count of unique values for the same value of Fund.

                • 5. Re: NoFields
                  gremlin9297

                  Ive used the ExecuteSQL originally, the problem is the query took almost 2 + hours to produce a result. Unfortunately that would not be beneficial at this time. The conditional value list would be problematic as the list of names may change from report to report, so it would have to be constantly edited. 

                  The Sum of the Reciprocal I believed I tried to use before but wasn't working exactly as what I was looking for... though I may give it another shot and hope that with some tweaks gets me the result I'm looking for. Thank you for the help with this one.. its been driving me crazy! If you can think of any other possible solutions please let me know im all ears. The sooner I can put this to bed the sooner my head will stop hurting.

                  Thanks again

                  -Eric

                  • 6. Re: NoFields
                    philmodjunk

                    The fact that the list of names changes from report to report does not mean that you can't use the conditional value list method, though sum the reciprocal may be the most flexible option to use. A conditional value list of this sort is a "use values from field" value list and thus changing the data in your table will change the values in the value list.

                    "I tried this before and couldn't get it to work" isn't surprising as this is a very unusual method for getting a unique count. But none-the less, it does work to produce the desired unique count if implemented correctly.

                    If you are using FileMaker 13 or newer, there might be a way to get the same count using a "list of" summary field in combination with a calculation field that uses getSummary to produce a match field of return separated values to sue for getting a count that will omit the duplicates. Don't know how fast it will be to use without some testing, however, as this method relies on both a summary field, and an aggregate function in order to produce the needed value.

                    • 7. Re: NoFields
                      gremlin9297

                      Hmm... i'll give it a shot. Can't hurt to try at least. At the very least you can't say i don't give interesting problems sometimes lol

                       

                      Thanks again for the help. Im going to try some of these and see if I can't get something to work out. Right now i'm trying the sum of the reciprocal but i'm not getting any values from c_fraction... and not entirely sure. Ive copied the calculations from the example and ive had it work in the past but now im not getting anything. Still trying to figure out what the hiccup is.. of course I changed the fields to match mine... but yea i'll get back to you if I can figure this out or if i'm still banging my head against a wall.

                      • 8. Re: NoFields
                        gremlin9297

                        Hmm... i'll give it a shot. Can't hurt to try at least. At the very least you can't say i don't give interesting problems sometimes lol

                         

                        Thanks again for the help. Im going to try some of these and see if I can't get something to work out. Right now i'm trying the sum of the reciprocal but i'm not getting any values from c_fraction... and not entirely sure. Ive copied the calculations from the example and ive had it work in the past but now im not getting anything. Still trying to figure out what the hiccup is.. of course I changed the fields to match mine... but yea i'll get back to you if I can figure this out or if i'm still banging my head against a wall.

                        • 9. Re: NoFields
                          philmodjunk

                          GetSummary will most likely fail to return a value for one of two reasons:

                          a) The found set is not currently sorted by the break field specified in the getsummary function call
                          b) The break field is not from the same table as the summary field and the calculation field.

                          Les common would be to mistakenly define a number field and give it an auto-entered calculation as that would fail to update with different values as the found set changes.

                          • 10. Re: NoFields
                            gremlin9297

                            Perfect! That fixed it. You are the Filemaker Wizard! Well as it fixed the problem with the values... the only thing is that its not giving me the unique count per fund.. but instead giving me the unique count throughout all the funds. *sighs* Sooooo close

                            • 11. Re: NoFields
                              philmodjunk

                              Sounds like either the wrong break field or the wrong sort order...

                              • 12. Re: NoFields
                                gremlin9297

                                Awesome! Thank you for all the help. That was the last piece!

                                • 13. Re: NoFields
                                  gremlin9297

                                  Hey I have a follow up question, so i got the unique count to work for each fund so that was great. I was wondering if it is possible to do the same unique count, but instead of each fund, to do a unique count of all the names. Not sure if I explained that well... 

                                  • 14. Re: NoFields
                                    philmodjunk

                                    If memory serves, you can simply put the same summary field in the footer or grand summary layout part at the bottom of the screen.

                                    1 2 Previous Next