5 Replies Latest reply on Jan 15, 2015 11:17 AM by frank929rr

    One to many to many dynamic portal update



      One to many to many dynamic portal update


      I have "Customer" table linked to "Address" by CustID.  No Problem there.  Each customer can have multiple addresses with multiple services.

      I'm trying to figure out a way to link the "Services" table to the "address" table but here is the issue.

      I want all the available "services" to show up in a portal in the "address" layout.  I've linked them by using a "address_lineitem" table, since the price is different for each "customer"

      Is there a way to have that portal of all available services dynamically update if we add a new service or change the name of a service in the "services" table?  

      Do I have to script it or is there an easier way?  I feel like there should be an easier way, but I'm just not seeing it.



        • 1. Re: One to many to many dynamic portal update

          It seems like you have or need these relationships:


          Customer::__pkCustomerID = Addresses::_fkCustomerID
          Addresses::__pkAddressID = ServiceLineItems::_fkAddressID
          Services::__pkServiceID = ServiceLineItems::_fkServiceID

          Your "portal of available services" on and Address layout should be a portal to ServiceLineItems that includes a "serviceName" field from Services. Changing the name of the service should be a case of editing one record in Services and all the places where this name appears should update automatically as they should all refer to this one field in Services.

          I suspect that some detail of what I described does not match your actual design...

          • 2. Re: One to many to many dynamic portal update

            Thanks.  I have that set up that way now.  When I create a new address, I want the portal to automatically show all the available services.  Currently, when I add a new address, I have a "add default services" button on the addresses layout which runs the script that adds all the services to the line item table using the current address ID. 

            When I add a new service in the services field, I suppose I have no choice but to run a script to cycle through and add the new service to the line item table for each AddressID.  Is this the correct best practices?  I'll put a flag_logged field in the services table so the script will check to see if this is a new item or one that has been added already.  

            Is there a way to do this  after a new record is created or do I have to hide the status bar and remove the "new record" option from the toolbar? I wouldn't mind removing it if it only removed it from the services layout. Then I would just make a new record button with the add to line item script attached.  

            Actually, I'd need that script to run after the record is filled out, so the script running on the new record button would not work.  It would have to be a save record button, which of course no one will bother pressing when they are done.



            • 3. Re: One to many to many dynamic portal update

              I don't quite see the purpose for that script. Why not set up a second portal that lists all available services (or a sub set of them) dynamically with a button to click that adds the selected service to the LineItems table?) Then this portal can update automatically without needing such an update script to update your lineitems table.

              A relationship like this:

              Addresses::anyField X Services|All::anyField

              will allow you to set up a portal to Services|All (a new Tutorial: What are Table Occurrences? of the Services table) that lists all services. A different relationship such as:

              Addresses::constDefault = Services|Default::Default

              could be used to dynamically display all Services marked as a "default" service if you don't want to list all of them. (or you can use the first relationship and set up a portal filter to limit the list to those so "marked".)

              You might be interested in "Adventures in FileMaking #2 - Enhanced Value Selection". It demonstrates several different ways that you can use a portal in place of the standard value list for selecting values.

              • 4. Re: One to many to many dynamic portal update

                Thank you.  I will look that over and hopefully get a better understanding. 

                • 5. Re: One to many to many dynamic portal update

                  The reason I didn't want to add the button to click to add the selected service from the second portal to the LineItems table is twofold.


                  1.  Every time a new service is added to the Services table, about 90% of the people will be getting it and I don't want to leave it up to the data entry person to select who gets it and who does not.  My solution would be to add the service to every address in the LineItem table each time a new service is created in Services table.  Each customer's price for this service would have to entered in manually.  I realize this would leave quite a few empty line items soI would solve this by sorting the portal first by ascending and then not blank.

                  2.  I can't be certain the date entry person would actually click this button, that's why I wanted it to trigger the action as soon as the new Service is added to the service table.  

                  I hope I'm explaining myself clearly.