1 2 Previous Next 16 Replies Latest reply on Jan 10, 2013 1:59 PM by sailingadrift

    Filemaker 6 - Multiplying a Summay Field in Portals

    sailingadrift

      Hi

       

      I am limited to Version 6 of Filemaker at work so maybe the answer to my question is to spend the money for an updated version but.... I am trying to use a portal to summarize data from a second database. I want to multiply a field called "yield" on each of the portal lines to summarize for a final total yield. . In effect I want to calculate Total Yield = Yield 1 * Yield 2* Yield3 * etc to get a final total yield. The stahdard summary fields available though are things like average and standard deviation and they do not multiply each line. As an alternative, I can do it with a Script but that requires me to use a trigger to initiate the calculations each time a new record is created. I don;t know how to automatically trigger a calculation. once a portal lookup is initiated. Any help would be appreciated.

       

      Ron

        • 1. Re: Filemaker 6 - Multiplying a Summay Field in Portals
          comment

          I got somewhat lost in your description. Would it be correct to say you want the parent record to calculate the product of all related child records? I am confused by your mentioning of summary fields, when you probably mean aggregate functions?

          • 2. Re: Filemaker 6 - Multiplying a Summay Field in Portals
            Vaughan

            sailingadrift wrote:

             

            Hi

             

               I am limited to Version 6 of Filemaker at work so maybe the answer to my question is to spend the money for an updated version but....

             

             

            IMHO you should go no further with FMP 6. It's now 2 major file format revisions behind (fp7 and fp12) and OS compatibility will soon become a real issue. Getting people who still remember FM6 code is also going to be a problem.

            • 3. Re: Filemaker 6 - Multiplying a Summay Field in Portals
              LyndsayHowarth

              It is not so much a problem remembering code... but more a matter of missing the new features and functions.

               

              I have an old FMPU 5 server which back-ends a lasso site. I cannot match the speed with any of the new versions...

               

              I have been keeping a machine at 10.6 so I can maintain legacy systems. As was pointed out to me recently by a Linux guy... in Mountain Lion I can virtualize any OS... so legacy versions of FMP shouldn't be a problem.

               

              I think that if you are just dealing with FMP, though, you are going to save the cost of the upgrades on the time it will take to do what you need. FMP12 lightens the load considerably...

              • 4. Re: Filemaker 6 - Multiplying a Summay Field in Portals
                sailingadrift

                Hi Michael

                 

                 

                 

                Thank you for responding. Yes you are correct.    Sorry if I did not phrase it properly.  We  lack the funds at this stage to upgrade all our users to Version 12 and were hoping to be able to get by with a solution to this problem to help us in manufacturing to report on yields.

                 

                 

                 

                Ron

                • 5. Re: Filemaker 6 - Multiplying a Summay Field in Portals
                  gdurniak

                  Please ignore all the "latest version" snobs on this forum. There is nothing wrong with FileMaker 6. We still use it

                   

                  In ANY version,  in order to see a total on a portal line, you must define that as a Calculation field in the 2nd database

                   

                  Please give a real example,  for let's say 3 portal rows

                   

                  If the 3 yields are all in the same record, the calculation is easy

                   

                  if you need yields from other records, you would need to define a Relation in the 2nd database to ITSELF,  then use the aggregate functions

                   

                  greg

                   

                  > In effect I want to calculate Total Yield = Yield 1 * Yield 2* Yield3 * etc to get a final total yield

                  • 6. Re: Filemaker 6 - Multiplying a Summay Field in Portals
                    sailingadrift

                    Thank you Lundsay,

                     

                     

                     

                    We just don’t have the funds here at this time to upgrade all of our users to Version 12 as much as I want to.  My understanding is the data base changed at Version 7 and also again at Version 12 so we would have to update all users and that would be costly to us.   Trying to get by with what we have.

                     

                     

                     

                    Ron

                    • 7. Re: Filemaker 6 - Multiplying a Summay Field in Portals
                      sailingadrift

                      I understand and appreciate your reply. We just do not have the funds to update all our users at this time to Version 12. If it was a matter of just buying one seat it would not be a problem but to update all our users at this time is not on the budget until next year.

                       

                       

                       

                      Thanks though for the reply

                       

                       

                       

                      Ron

                      • 8. Re: Filemaker 6 - Multiplying a Summay Field in Portals
                        sailingadrift

                        Hi  Greg,

                         

                         

                         

                        Thanks.  I love Filemaker and would update in a heartbeat if we could afford to upgrade all our users at this time but it is not in the budget. 

                         

                         

                         

                        I have two databases.  The raw data which forms the child records in the portal does have a field that calculates yields for that one line item.  My problem when I do a portal though is that no aggregate functions exist ( at least in Version 6) that can multiply each of the separate child records together at the end of the portal.  There are aggregates for certain functions such as averages but not for multiplication of each child record field.  I have been stumped at this step for a few weeks now and can’t seem to get around it.

                         

                         

                         

                        Thanks again for the encouragement. I was a little hesitant to go on the forum to say I was still using Version 6.  It works great though.

                         

                         

                         

                        Ron

                        • 9. Re: Filemaker 6 - Multiplying a Summay Field in Portals
                          gdurniak

                          it may be possible, even in 6

                           

                          please give an example, with a few child records,  with Yields,  and what the "total" at the bottom of the portal should be

                           

                          greg

                           

                          > My problem when I do a portal though is that no aggregate functions exist ( at least in Version 6) that can multiply each of the separate child records together at the end of the portal.

                          • 10. Re: Filemaker 6 - Multiplying a Summay Field in Portals
                            sailingadrift

                            Ok

                             

                             

                             

                            I will try later today to work you up a simple example after some meetings here.

                             

                             

                             

                            Ron

                            • 11. Re: Filemaker 6 - Multiplying a Summay Field in Portals
                              comment

                              Here's a little trick you could try:

                               

                               

                              1. Define a calculation field cLnYield in the Child table (result is Number) =

                               

                              Ln ( Yield )

                               

                               

                              2. Define a calculation field in the Parent table (result is Number) =

                               

                              Round ( Exp ( Sum ( Child::cLnYield ) ), 0 )

                               

                               

                              You may want to experiment with the rounding precision until you get the right balance between the desired precision on the one hand and quantization errors on the other.

                              • 12. Re: Filemaker 6 - Multiplying a Summay Field in Portals
                                sailingadrift

                                Hmm – that’s interesting. I am stuck in meetings today but will try that later. Clever idea

                                 

                                 

                                 

                                Ron

                                • 13. Re: Filemaker 6 - Multiplying a Summay Field in Portals
                                  sailingadrift

                                  Hi  Greg

                                   

                                   

                                   

                                  Here are two related data bases in Filemaker 6.0 that show what I am trying to do. The first data base called raw data is data collected on a job for each manufacturing step which calculates how many good and how many bad and what the yield is for that step.

                                   

                                   

                                  The Summary data base I call Report Summary is where you can call up a job by job number and see a portal with the steps in that job or process that shows each of the yields per step.  What I want to do is to have a grand total that multiplies each of those yields in the portal.

                                   

                                   

                                   

                                  Just so you know we work 4 day work weeks and are closed on Fridays so I may not be responding until Monday if you do come up with something.  I appreciate you looking at this for me.

                                   

                                   

                                   

                                  I did have another  person  (Michael Horak ) offer the following trick which looks clever but I haven’t tried yet.

                                   

                                  Here's a little trick you could try:

                                   

                                  1. Define a calculation field cLnYield in the Child table (result is Number) =

                                   

                                  Ln ( Yield )

                                   

                                  2. Define a calculation field in the Parent table (result is Number) =

                                   

                                  Round ( Exp ( Sum ( Child::cLnYield ) ), 0 )

                                   

                                   

                                  Thanks

                                   

                                  Ron

                                  • 14. Re: Filemaker 6 - Multiplying a Summay Field in Portals
                                    sailingadrift

                                    Hi Michael

                                     

                                     

                                     

                                    I think you got it Michael.  I have attached a simple database (Filemaker 6.0)  to try it out.  Never thought of summing logarithmic numbers.  Very Clever.  Can’t thank you enough for your help.

                                     

                                     

                                     

                                    Ron

                                    1 2 Previous Next