4 Replies Latest reply on May 4, 2013 1:20 PM by JohhnyHilly

    Calculations between portals



      Calculations between portals


           PhilModJunk, I am directing this question in your direction as it is relating to the layouts and portal you help me set up from this thread http://forums.filemaker.com/posts/f0f8e6302b?page=4

           I am wanting to create a calculation field, that multiplies a sum value of a portal field, with a value from another portal. Now normally this would be easy to do, however, with the way I have it setup it is easier said than done.

           If you remember, I have a layout based on Equipment_List table with two portals in, called MDR Input. The first portal is linked to Equipment_List|SameVessel where the vessel equipment list is, and the second portal is linked to Service History. When you click on the piece of equipment it makes it the active record and I enter service data into the second portal relating to that equipment.

           In the second portal, I am summing the number of times a radio button is selected as "Yes". I have this field sitting outside the portal and is working fine. What I then need to do is to take this field and multiply it by a field in another portal relating to the same piece of equipment. This other portal is on a layout (called Vessel Equipment Specs) based on Vessels and the portal linked to Equipment_List. In this portal there is the vessel equipment list with info/data about each item on the list.

           This is how the relationships look:

           Vessels - - - - <Equipment_List> - - - - Service History

                                                      | - - - - Equipment_List|SameVessel

           Vessels::__pkVesselID = Equipment_List::__pkEquipment_ListID

           Equipment_List::__pkEquipment_ListID = Service History::_fkEquipment_ListID

           Equipment_List::_fkVesselID = Equipment_List|SameVessel::_fkVesselID 

           So two main questions. The first, for a piece of equipment say Main Engine Port, how do I multiply the sum value of the radio buttons in the MDR Input layout with a field value in the portal on the Vessel Equipment Specs layout for the same piece of equipment?

           The second, is how do I then do the exact same thing for the next piece of equipment say Main Engine Starboard?

           I was able to do all of the above for the first piece of equipment, but then got stuck on the next, as I wasn't sure how to take the sum value for Main Engine Starboard and multiply it by the field value in the Vessel Equipment Specs layout  for Main Engine Starboard.

           Any help would be much appreciated. I know it's complex so let me know if any of it doesn't make sense. Thanks a lot.

        • 1. Re: Calculations between portals

               You'll need to get away from thinking in terms of portals and layouts. Instead, think in terms of tables and relationships.

               You have a relationship that matches Equipment LIst to Service HIstory that should match by an ID specific to a particular item of equipment. You need to use that type of relationship, one that matches to the correct item in the equipment List table to craft your calculation. How your layouts, portals etc. don't really affect how this calculation will work as long as the relationship is in place to make this work.

               If the Sum of a value in Service History needs to be multiplied by a value in the parent record in Equipment List, you can define a calculation field in Equipment List in this format:

               SpecValue * Sum ( ServiceHistory::valuefieldToSumHere )

               But make sure to select an occurrence of EquipmentList in the Context drop down that correctly matches by the correct match fields to get the total you want for the sum function.

          • 2. Re: Calculations between portals

                 Thanks Phil. Seems easier when you think of it that way. Using your explanation the calculation I needed was this:

                 SpecValue * ServiceHIstory::valuefieldToSumHere

                 This resulting value is the number of filters used for each equipment. Then in the Equipment_List table I did a Summary field for the above calculation to give the total number of filters used for ALL the equipments, i.e. for the whole vessel.

                 I have one more question though. If I update the ServiceHIstory::valuefieldToSumHere field, the Summary field does not update until I refresh window - flush cached join results. If I update the SpecValue in Equipment_List, the Summary field does update without having to refresh. Is there a way when I update the ServiceHIstory::valuefieldToSumHere field, the Summary field will automatically update?


            • 3. Re: Calculations between portals

                   Yes, good generalization! summary fields and sum functions can often be interchanged like this. (Sum() in the parent or summary in the child)

                   Usually, sum functions will update much more smoothly than the same calc that uses the summary field instead.

                   Instead of Refresh WIndow [Flush...], you might be able to use; Commit Records ; Refresh Window [], but I'd try using the sum function instead.

              • 4. Re: Calculations between portals

                     Ok yes you are right, the sum function works much better. No need for commit record or refresh window. Thanks!