7 Replies Latest reply on Feb 10, 2012 10:01 AM by SueMaddock

    Portals

    SueMaddock

      Title

      Portals & Lookup

      Post

      Hi All,

      I am new here and fairly new to FileMaker - I have FileMaker Pro 11. I am trying to develop a database for our business and have a couple of problems which seem to both be issues with Portals - one I've managed to get around (although would rather do it better) and one I am struggling with.

      In the part I am struggling with there are four tables involved:
      Customers (includes all their contact details etc.)
      Vehicles (includes details on the vehicles a customer owns - one Customer to many Vehicles relationship)
      VehicleOrder (includes details on an order for a vehicle - one Vehicle to many Vehicleorders relationship)
      DeliveryDetails (includes delivery type and contact information for delivery - one VehicleOrder to many DeliveryDetails relationship)

      I have a Customer layout with a tab to the Vehicles which I put in a portal. 

      The first issue I had was to be able to take the VehicleID from a line on the Vehicle portal into the VehicleOrder layout - I click an arrow at the end of the portal line and it takes me to the VehicleOrder layout and places the Vehicle::VehicleID from the line I clicked on in the VehicleOrder::VehicleID field. I tried using SetField in a script, this did not work for some reason and I ended up using a copy and paste in the script, which works fine but I am aware is not the best way to do this.

      The issue I am really stuck on now is with a portal on the VehicleOrder layout allowing me to enter data into the DeliveryDetails table. What I want to happen is to be able to select the delivery type (invoice / delivery / collection - hence I could end up with up to three records in DeliveryDetails for each VehicleOrder record) and then the contact details to be automatically populated with the customers current contact information, which I can then amend or leave as it is (I need to copy the information over so it remains static for that VehicleOrder even if the Customer changes their address in the future).

      On the DeliveryDetails table I have used a Lookup for each of the contact information I want automatically populated, and set it to the equivalent information in the Customer file. If I go into the DeliveryDetails layout and input a VehicleOrderID this works fine, however in the portal on the VehicleOrder layout it does not come up with anything. Is there something silly I am missing?

      Any help would be very much appreciated.

      All the best,
      Sue

        • 1. Re: Portals & Lookup
          philmodjunk

          Customers---<Vehicles---<VehicleOrder----<DeliveryDetails     (---< means one to many)

          Item 1, Set field will do the job, but you also need a set variable step first:

          Set Variable [$VehicleID ; value: Vehicles::VehicleID]
          Go to Layout [VehicleOrder]
          New Record/Request
          Set Field [VehicleOrder:::VehicleID ; $VehicleID]

          Think of $VehicleID as a "bucket" your script puts the ID number in the bucket, travels over to the VehicleOrder layout and then set field takes teh ID out of the "bucket" and puts it into the correct field.

          On your second issue, the contact info should be appearing in the portal rows. Looked up value field options are used in Invoice LineItem portals to look up unit prices in exactly this way. I'd check the relationship used for your portal and also double check to be sure the fields in the portal that are supposed to show the looked up contact info are from the correct table occurrence. Also, the look up won't work if the "chain" of related records in each in between table is broken in some way. In order for a record in DeliveryDetails to look up data from the parent record in Customers, there must be related records in Vehicles and VehicleOrder--so that's another detail to check as well.

          • 2. Re: Portals & Lookup
            SueMaddock

            Hi PhilModJunk,

            Thank you so much for the reply. I have amended my script as you suggested (I had not realised I needed to set a variable) and it is now working beautifully.

            With regard to the second issue I have now found how to get it to work, but it is not a way that is reasonable for it to work.

            The DeliveryDetails::VehicleOrderID >--- VehicleOrder::VehicleOrderID is populated with the correct value as soon as I enter the first field in the DeliveryDetails portal.

            The VehicleOrder::VehicleID >--- Vehicles::VehicleID is the one that is passed by the script to the VehicleOrder layout, and is correct in the layout and on the portal, even before entering the first field of the portal.

            The Vehicles::CustomerID >--- Customers::CustomerID is also displaying correctly on the VehicleOrder layout and within the portal, even before entering the first field of the portal.

            Now I have found that if I go to the VehicleOrder layout through my script (which passes the VehicleID over) then enter a value in the first field of the portal the customer contact information does not appear. However, if before I enter the first field in the portal I flick back to the Customers layout and then return to the VehicleOrder layout then the information will be displayed. Even if, when I go to the Customers layout, I switch to a different customer and click on one of the fields, when I return to the VehicleOrder layout it will not change the customer and remains with the correct information entering it into the portal when I fill in the first field.

            It is baffling me why I would need to come out of the layout in order for the information to come across. Any ideas would be gratefully appreciated. 

            All the best,

            Sue

             

             

            • 3. Re: Portals & Lookup
              SueMaddock

              I have now fooled it into working all the time by adding onto the end of my Script for passing the VehicleID over, Go To layout Customers then Go To Layout VehicleOrder again, and it works fine, always bringing up the correct data in the portal from the Customers table.

              • 4. Re: Portals & Lookup
                philmodjunk

                It sounds like you have some redundant data fields here.

                Example:

                You do not need a customerID field in vehicleOrders. Given a valid relationship between each of the individual tables, you can refer directly to any field in the related Customer record, you do not need to copy the values over to fields in the Vehicle orders record.

                • 5. Re: Portals & Lookup
                  SueMaddock

                  Hi PhilModJunk

                  I don't have a CustomerID field in the VehicleOrders file (I just got it to display on the layout to make sure I was picking up the correct data).

                  In the Customer table I have a CustomerID which links to the CustomerID in the Vehicles table.

                  In the Vehicles table I have a VehicleID which links to the VehicleID in the VehicleOrder table.

                  In the VehicleOrder table I have a VehicleOrderID which links to the VehicleOrderID in the DeliveryDetails table.

                  Hence, from the DeliveryDetails table the CustomerID is picked up through the relationships above - each field only exists twice, once in the parent table and then again in the immediate child table in order to define the relationship.

                  All the best,

                  Sue

                  • 6. Re: Portals & Lookup
                    philmodjunk

                    When you have a chain of related tables like this and related data from the far end of the chain does not appear, there are three possibilities to check:

                    1. The value in your current layout's foreign key field is missing or incorrect.
                    2. There is a missing record in one of the tables in between or it has missing or incorrect data in a key field.
                    3. The layout needs to be refreshed. A Refresh window, (sometimes needs a commit record) is sometimes needed to get FileMaker to "tunnel" all the way from the current layout's table to one that is several relationships away from it.

                     

                    From your description of what got it to work, I suspect #3 may be the issue.

                    • 7. Re: Portals & Lookup
                      SueMaddock

                      Thank you so much for all your help and patience. Refresh Window would not get it to work but Commit Record did - obviously this is what I was doing by coming out of it and going back in. So all sorted now, thank you ever so much. Smile