2 Replies Latest reply on Jan 23, 2014 2:35 PM by RobertMcIntyre1616

    Portal Help

    RobertMcIntyre1616

      Title

      Portal Help

      Post

           I am using FileMaker Pro 12 Adv (MAC)

             I am attempting to create 2 portals on a layout. The first portal is not a problem here is the table and the portal layout

           My Table fields:

           milagelog_id        Number                Indexed, Auto-enter Seral, Required Value, Unique, numeric Only

           vehicle                Text                       Indexed

           startmilage          Number

           endmilage           Number

           totalmiles            Calculation           from MilageLog, = end milage - start milage

           milagesum          Summary              = Total of totalmiles

            

            

           Data entry, Portal 1:

            

      Vehicle      Start Milage   End Milage    Total Miles

           Truck1        0                       10                10

           Truck2        0                       15                15

           Truck1      10                       20                10

      Truck3        0                         5                  5               

            

                                                    Total Miles:   40

            

            

           I would like to make a second portal that will show the following on the same layout 

            

           none editable Portal 2

            

      Vehicle        Total Miles

           Truck1         20

           Truck2         15

           Truck3           5

            

           How do I accomplish this?

           Thanks in advance!

           Robert

        • 1. Re: Portal Help
          philmodjunk

               The most straight forward method if you are using FileMaker 12 or newer is to use a single calculation field that uses ExecuteSQL to produce the needed subtotals for each vehicle: FMP 12 Tip: Summary Recaps (Portal Subtotals)

               Without using ExecuteSQL, there are two other options:

               1) Put a 1 row filtered portal to your mileage table  for each vehicle that you have. Put mileagesum in the portal row of each such portal. Set up a portal filter expression to filter the records for a different vehicle for each portal. There are two main drawbacks to this approach: 1) The total miles in each one row portal are "display only", you can't access these totals in a script or calculation. 2) If you add more vehicles to your fleet, you have to edit the layout to add more one row portals. (But you can copy and paste an existing portal and then just edit the filter expression to add more portals.)

               2 This option requires a number of specific elements in your relationship chart in Manage | Database.

               If you do not have a table with exactly one record for each vehicle, you'll need to create one and add a record for each vehicle. Then you'll need to add a global Field, gSelectedLogID to this table to help narrow the totals to just those for a specific milagelog_id value.

               This enables you to set up this relationship: (I am naming your layout's table as "Mileage" here.)

               Mileage-----<Vehicles-----<MileageLog|VehicleTotals

               Mileage::anyField X Vehicles::anyField   ( the X operator matches any record in mileage to all records in Vehicles)

               Vehicles::vehicle = MileageLog|VehicleTotals::vehicle AND
               vehicles::gSelectedLogID = MileageLog|VehicleTotals::milagelog_id

               Then you can define a calculation field in Vehicles, cTotalMiles, as Sum ( MileageLog|VehicleTotals::totalmiles ).

               Now you can place a portal to Vehicles on your Mileage layout that uses the cTotalMiles to show the subtotals. But you also need to set up the OnRecordLoad trigger in Layout Setup... to perform this script:

               Set Field [ Vehicles::gSelectedLogID ; Mileage::milagelog_id ]

               In order to update the global field and thus get totals from the correct set of mileage totals for each vehicle.

               Note: MileageLog|VehicleTotals is my name for a new Tutorial: What are Table Occurrences? of your first portal's table.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          • 2. Re: Portal Help
            RobertMcIntyre1616

                 Thank you! SQL works like a champ.

                 Bunch of awesome people here that really help!

                 Robert