8 Replies Latest reply on May 13, 2014 1:37 AM by glorifindal

    Related counts


      I am looking for a way to do some related counts in FM11:


      I have the following relationship: Admin -> Orders -> Services


      Where Admin is simply the context from where I run reports, etc


      I need to know how many Orders have the following...


      0 Services

      1 Service

      2 Services

      3 Services

      More than 3 Services


      I could of course create 5 fields in the data file, and run the script against each of the 5 fields.


      ... but I am unable at the moment to modify the data file (using seperation model)

      So, I need to do this per script.


      Does anyone have a suggestion?


      Many thanks in advance

        • 1. Re: Related counts

          can you create globals in the Admin for use as "filters" (in portal and/or for relationships)?


          Your layout would then be portals (you can simulate a quasi-list view, if needed) and based on the global fields relationships to your orders & services file(s)






          • 2. Re: Related counts

            Hi there,


            many thanks for responding...


            I can create globals in the Admin file - and have the needed relationship there already ... from Admin -> Orders -> JoinOrders_2_Services -> Services ...

            Admin -> Orders is based on a calculated field in Admin - "Active" and the Status field in Orders


            I could add relatiosnhips though if needed - but what would the new relationships be based on ?



            • 3. Re: Related counts

              I might create "CONSTANTS" (indexed calculated fields or globals that you know will be static).


              field0 = 0

              field1 = 1

              field2 = 2

              field3 = 3

              then do the relationship, including these constants.



              filtered portals is another way to limit the information seen. Add the calc to filter by your criteria of 0, 1, 2, 3, >3.



              • 4. Re: Related counts

                Ok - so, the filtered portal would not work as it's actually the count result I need - I should have said that at the begining of this thread - please excuse me.


                Using the CONSTANT approach, let me see...


                My relationship from Admin -> Orders =


                c_Active = "Active" -> Status = "Active"


                If I were to use a CONSTANT field of 1 in the Admin table to count the Orders that have only 1 Service - then this Count would need to be stored in the Order table also - woud it not?


                Please excuse me if my english sense is off track.

                • 5. Re: Related counts

                  If you have a fixed number of queries, here's an alternate approach, using a repeating field, so you can easily read a specific or all values.  


                  Note the use of a Count() calculation field in Orders; my SQL isn't good enough (yet :-) to count the related records via a JOIN, which should be possible.

                  • 6. Re: Related counts

                    Excuse the late reply - been out.


                    Many thanks indeed for the file - but as mentioned :


                    [quote] I am looking for a way to do some related counts in FM11 [/quote]



                    So, no SQL available to do this ... Only have FM11 on this project


                    Thanks anyway,



                    • 7. Re: Related counts

                      glorifindal wrote:


                      [quote] I am looking for a way to do some related counts in FM11 [/quote]


                      That should teach me …


                      Without eSQL you have a little problem, since – IIUC – a countOfServices field would be calculated & unstored, which is no good on the far side of a relationship.


                      Not a problem with eSQL, but kind of a showstopper here. This means you need to convert this calculation into a regular number field and update it via script whenever you add or delete a service to/from an order – which of course requires you to modify the data file.


                      Try this different take on the problem – in an honest-to-deity .fp7 file , with a nice little counting script.

                      1 of 1 people found this helpful
                      • 8. Re: Related counts

                        Good morning ...


                        Many thanks for this - it works as I need it - and that is enough for the moment.


                        I am currently trying to use CustomList for this - may be a bit more elegant.


                        But many thanks for your help