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...
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.
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.
Thank you. I will look that over and hopefully get a better understanding.
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.