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

    Related counts

    glorifindal

      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
          beverly

          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)

           

          Beverly

           

           

          Beverly

          • 2. Re: Related counts
            glorifindal

            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 ?

             

            G

            • 3. Re: Related counts
              beverly

              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.

               

              OR..

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

               

              Beverly

              • 4. Re: Related counts
                glorifindal

                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
                  erolst

                  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
                    glorifindal

                    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,

                     

                    G

                    • 7. Re: Related counts
                      erolst

                      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
                        glorifindal

                        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