11 Replies Latest reply on Mar 2, 2015 10:22 AM by Jason_Farnsworth

    Keeping Score via Relationships

    Jason_Farnsworth

      I need to make a calculation based on (2) separate relationships

       

      What I have....

       

      Table-Customer (1) ==> Table-Jobs (Several) ==> Table-Inspections (Several) ==> Fields (7) that score an inspection item, the score will be from 1-5.

       

      What I want...

      I would like to analyze the composite of all of one customers - Jobs & Inspections to get a total score of the the inspection item.

      So for example...

       

      (1) Customer - (2) Jobs - (3) Inspections per job == totaling for this (1) customer (6) Inspections per Item creating a total score from 6-30 for that Item keeping in mind I have (6) other items also to score.

       

      Is it possible to calculate this score dynamically via the relationship? I know that I can script the result but was looking for a way to keep it dynamic as possible as I have several customers with thousands of inspections.

       

      Thanks in advance, let me know if I need to provide more information for a cleaner view of the issue.

       

      Jason Farnsworth

        • 1. Re: Keeping Score via Relationships
          siplus

          dynamic + thousands of inspections = definite no, if interface speed has importance > 0...

           

          ...but with FM 13 you could have a button "Situation" which triggers a popover having all the sums on it. This way the unstored sums will only fire on request.

          • 2. Re: Keeping Score via Relationships
            DanielShanahan

            CUSTOMER --< JOB --< INSPECTION

             

            When setting the INSPECTION::score, you can look up through the relationship and see the unique Job and Customer that are related to this Inspection since the perspective is a many to one relationship.

             

            One option is to set an onObjectSave trigger on the INSPECTION::score field.  In that script you could use the Execute SQL function to get the new total for JOB::inspectionTotal and set that field and then again to get the CUSTOMER::inspectionTotal and set that field.  It might look something like this:

             

            Commit Records

             

            Set Variable $jobTotal =

             

            ExecuteSQL ( "

              SELECT SUM (score)

              FROM inspection

              WHERE jobID =  "

              ; "…" ; "" ; JOB::ID

            )

             

            Set Field [JOB::inspectionTotal] = $jobTotal

             

            Set Variable $CustomerTotal =

             

            ExecuteSQL ( "

              SELECT SUM (inspectionTotal)

              FROM job

              WHERE customerID =  "

              ; "…" ; "" ; CUSTOMER::ID

            )

             

            Set Field [CUSTOMER::inspectionTotal] = $CustomerTotal

             

            Commit Records

             

            This presumes the script context is the INSPECTION table/layout but should also work from the JOB table/layout if the INSPECTION::score is update via a portal.  This would dynamically change the JOB::inspectionTotal and CUSTOMER::inspectionTotal whenever an INSPECTION::score changes.

             

            You will need to check for record locking and have a rule to determine what to do in the event of a lock.

            • 3. Re: Keeping Score via Relationships

              Yes, this can be done quite easily using FileMaker's built in tools. No need for anything complicated.

               

              Any relationship created via a TO has the abilit to draw totals from one or more records in that relationship.


              Sum(Related table::numberfield) is the simplest method. However, summing a table two levels down might fail due to the linking fields.

               

              Build in layers of results.

               

              Your inspection table has its own value fields which are manually entered.

               

              Your job table is linked to this table so you can use calculated fields to total the inspection tables values.

               

              Your customer table is linked to the jobs table so you can use calulcated fields to total the fields in the job table.

               

              If the inspections are finished records never to be changed, then you can treate the totals in the job table as concrete numbers and set them using a script so that the values never change (my preferred method).

               

              Then comes the final question of whether the numbers are real time or non-changing. If they are real time then you need to use a calculated fields set to update everytime you look at the record. If the results are static, then you can set the values once using a script and forget it.

               

              Any relationship created via a TO has the abilit to draw totals from one or more records in that relationship.

               

              Inspection table: Num1, num2, num3

              Job Table: Calc1 =Sum(num1), Calc2=Sum(num2), Calc3=Sum(num3)

              Company table: CalcA=sum(calc1), CalcB=sum(Calc2), CalcC=Sum(calc3)

               

              Nothing difficult to set up. This uses the relationships you have established. This is why we like FileMaker.

               

              Summary fields could also be used but too early in the AM to think about that.

               

              I would create a script performed after entering an inspection that would set fixed results in the job table and then in the company table.

               

              Script:

              New record

              enter stuff

              commit record

              Perform script update job record

              Perform script update customer record

               

              This would set real numbers into the fields above rather than making them calculated fields.

              • 4. Re: Keeping Score via Relationships

                Why not simplify things and just use FileMakers built in calculated fields and summary functions? I do this all the time and never use SQL. I realize that many new developers coming in from other applications bring their tool set with them and don't utilize FileMaker's tools.

                 

                In the job table a calc field sum(inspectionfield) would do the trick and save all that code time.

                 

                No need to worry about record locking since calc fields are real time, if so designated, but a window refresh might be necessary.

                 

                Also, this would be dynamic and put in place the instant he finished the calculated fields and would apply to all records and not require any updating script, etc. There might be a slight delay as the fields are saved when added to an existing table of thousands of records.

                • 5. Re: Keeping Score via Relationships
                  wimdecorte

                  jackrodgers wrote:

                   

                  Why not simplify things and just use FileMakers built in calculated fields and summary functions? I do this all the time and never use SQL. I realize that many new developers coming in from other applications bring their tool set with them and don't utilize FileMaker's tools.

                   

                   

                   

                   

                   

                  Except that SQL now is one of the FM tools, and one of the better ones too.

                   

                  The issue with calc fields and summaries is that can bog the solution down if used where not strictly necessary.  It's one of the "easy to use" habits that can be difficult to figure out where it becomes a problem.  Especially where summaries depend on unstored calculations.

                   

                  In general, if data needs to be calculated ones (like a score total, invoice total,...) it should be done as part of the workflow that adds the score, adds an invoice line item so that the total becomes just another number field.  That makes reporting on them very fast.

                  • 6. Re: Keeping Score via Relationships
                    DanielShanahan

                    jackrodgers wrote:

                     

                    Why not simplify things and just use FileMakers built in calculated fields and summary functions? ...

                     

                    Hi Jack,

                    You certainly could use the calculation fields and summary fields.  The OP hasn't mentioned number of concurrent users, if the solution is hosted or not, and the size of the file.

                     

                    Using number fields instead of calc/summary fields scales better, so my default is to use triggers/scripts.

                    • 7. Re: Keeping Score via Relationships

                      Actually I have avoid using calculation fields for things like an invoice and might use them for something like if date is late, "past due" else "OK".

                       

                      I use set field to make a 'concrete number'. The use of triggers, as pointed out, now makes this simple. One simple script can be called whenever certain fields are modified and yes this function was way late in arriving.

                       

                      However, this does create the problem of how to update the invoice if a line item is deleted or goes missing in action.

                       

                      Sometimes both might work together:

                      A set field that totals the number of invoice lines, dolars etc. at the time of finalizing the invoice.

                      A calculated field that does the same real time calcs.

                      A third calcualted field that compares others and in case of error and flashes red and sounds a siren... 

                      • 8. Re: Keeping Score via Relationships

                        My issue is that Set Field is simple to use and would accomplish the same thing, would it not.

                         

                        The essence of FileMaker that makes it attractive is the ability to bypass typing long buggy strings of code and that SQL certainly looks just like that.

                         

                        Go to customer record

                        Set field (jobtotal; sum(whatever field in related table) 

                        commit record

                         

                        Seems to perform the same tasks without introducing a new language and its problems with debugging.

                        • 9. Re: Keeping Score via Relationships
                          Jason_Farnsworth

                          DanielShanahan wrote:

                           

                          Set Field [JOB::inspectionTotal] = $jobTotal

                           

                          Set Variable $CustomerTotal =

                           

                          ExecuteSQL ( "

                            SELECT SUM (inspectionTotal)

                            FROM job

                            WHERE customerID =  "

                            ; "…" ; "" ; CUSTOMER::ID

                          )

                           

                          Set Field [CUSTOMER::inspectionTotal] = $CustomerTotal

                           

                           

                          Daniel, This process seems to work and is rather smooth, However I am not sure I understand the script entirely.

                           

                          I can get the needed result by using..

                           

                          I am running the script at the Contractor lvl so, "CTR" is my table reference. The Child lvl is "JOB" or via "CTR_JOB"

                           

                          ExecuteSQL ( "SELECT AVG (list_tot_sc)

                                                  FROM CTR_JOB

                                                  WHERE zwf_CTRID = 35 " ; "..." ; "" ; CTR::__wp_CTRID   )

                           

                          In your example you have not specified what "WHERE" should equal, which is I think why you have added the argument to the end "CUSTOMER::ID"

                           

                          I have tried to capture the CTR::__wp_CTRID with a variable at the beginning of the script and I do not get value

                           

                          ExecuteSQL ( "SELECT AVG (list_tot_sc)

                                                  FROM CTR_JOB

                                                  WHERE zwf_CTRID = $CTRID " ; "..." ; "" ; CTR::__wp_CTRID   )

                           

                          It would appear that ExecuteSQL does not like the use of variables.

                           

                          What level is the data that I should be referencing in SELECT (Parent or Child)?

                          What level is the data that I should be referencing in FROM (Parent or Child)?

                          What level is the data that I should be referencing in Where (Parent or Child)?

                           

                          Do you have any insight on the arguments, I have read through the documentation a couple of times and it doesn't seem to see much on it.

                           

                          Thanks, for the advise,

                           

                          Jason Farnsworth

                          • 10. Re: Keeping Score via Relationships
                            DanielShanahan

                            Jason,

                             

                            To use a field reference or a variable within the ExecuteSQL () function, you need a place holder.  The question mark "?" (no quotes) is the place holder.  It appears that I forgot to include it.  It should read:

                             

                            ExecuteSQL ( "

                              SELECT SUM (inspectionTotal)

                              FROM job

                              WHERE customerID = ? "

                              ; "…" ; "" ; CUSTOMER::ID

                            )

                             

                            Or, with a variable:

                             

                            ExecuteSQL ( "

                              SELECT SUM (inspectionTotal)

                              FROM job

                              WHERE customerID = ? "

                              ; "…" ; "" ; $customerID

                            )


                            Where $customerID is defined earlier in the script.


                            HTH

                            • 11. Re: Keeping Score via Relationships
                              Jason_Farnsworth

                              Daniel,

                               

                              Thank you, that worked perfectly. I have not used this command in the past, but can quickly see myself becoming a junkie of it. It seems like a very simplistic approach to methods I would normally over complicate.

                               

                              Thanks again,

                               

                              Jason Farnsworth