4 Replies Latest reply on Oct 31, 2012 6:16 AM by jbante

    How to select 1 of several shipping addresses from order layout




      In this question I will use 3 tables.


      Contact (pk_Contact) - parent - single contact

      Address (fk_Contact) - child - multiple shipping addresses

      Order (fk_Contact) - child - multuiple orders


      Using a layout based from the Order table (child) how can I select which address to use for shipping?


      I cant use a foreign key and value list to populate which address becuase the order table is not directly connected to the address table (i.e. no foreign key exisits). If I create a link with a primary key in address and a foreign key in order I then get to select from several hundered addresses not relating to the contact. I am not sure if there is an extra sort function or and entirly different way to be able to select an address for an order layout.


      Any help would be greatly appreaciated! Thanks!



        • 1. Re: How to select 1 of several shipping addresses from order layout

          Hi Josh,

          Just make sure that the Address records each get a unique ID. Then another field that concatenates that unique ID and the fk_Contact ID.

          In the Orders , which already has the fk_Contact, you can scroll through the related adresses and by selecting one you can "set" its unique ID into a field on the orders table - which in turn gets concantenated with the order's fk_Contact . Now you have a match field in both the orders table and the address table to form a relationship used to populate the ship to fields with the address selected.


          Contact ID = 545

          Addess ID(s) = 111 and 222 and 333 = 545111, 545222, 545333

          Orders based on address selection = 545222

          • 2. Re: How to select 1 of several shipping addresses from order layout

            Hi usbc


            Thanks very much! I think most of that makes sense but just to be sure...


            To select the correct shipping address I could use a button to open a new widow with the related addresses displayed in a portal. With a second button and script inserted into the portal rows I can then 'set' the unique address id into a special feild in the order table. This unique id will then populate my related address feilds.


            As I am a little new to scripts could you please also help with what the copy and paste script would look like? I.e. will I set a temporary variable with the address ID and then navigate back to the orders layout and then paste that varible into the address selection feild?


            I really appreciate your comments so far! Thanks!

            • 3. Re: How to select 1 of several shipping addresses from order layout

              I think you will find that it can be much more simple. The field which makes up the second part of the concatenation can use a Conditional Value list to drop down the Address IDs (displaying the address) and that will complete the match field, say "545222".

              You may or may not yet have your mind around conditional value lists. If not, you'll find lots of  info about it and it's worth the time to put them in your quiver.


              • 4. Re: How to select 1 of several shipping addresses from order layout

                usbc wrote:


                Just make sure that the Address records each get a unique ID. Then another field that concatenates that unique ID and the fk_Contact ID.

                I agree that the Address records each need a unique primary key, but I'm not sure why you'd want to concatenate that with the Contact ID — this method could eventually create a dicey situation with key collision.


                First, your Order table needs an Address ID field (which I'll call id_Address, in accordance with FileMakerStandards.org naming conventions), which is left empty when you first create an Order record. There isn't initially a valid relationship between an Order and an Address based on id_Address, but there is a valid relationship based on id_Customer: an Order and the Addresses for the Customer for the Order will match on id_Customer. You could create a value list to select an Address foreign key to populate Order::id_Address; the value list would be based on "Order » Address_byCustomer::id" rather than just "Order » Address::id".


                A pop-up picker window is often a better experience for selecting from multiple options of multi-line data, like addresses. If you're picker is based on a list view instead of working through a portal (which makes it easier to use for unanticipated use cases in the future), you could do something like this:


                Go to Related Record [From:Order » Address_byCustomer, Using Layout: Address: Select from List (based on Address table), New Window]

                # Pause for user selection, set variable $id_Address with ID of selected Address record

                Go to Layout [Original Layout]

                # "Original Layout" is presumably based on the Order table

                Set Field [Order::id_Address; Value:$id_Address]


                That said, I generally recommend a more sophisticated approach for handling order addresses. The problem is that customer addresses can change over time, but we don't addresses used for orders to change for historical accuracy. With this setup so far, changing a customer addresses also changes that address for all past orders that used it — exactly what we don't want. So here's what I do:


                The Address table includes a modificationTimestamp field (auto-enter modification timestamp) and an id_Address__modification, which has this auto-enter calculation that replaces existing values:

                Let ( ~trigger = modificationTimestamp ; UniqueID )

                ... where UniqueID is wrapper for whatever UUID function you're using. (You are using UUIDs for your keys, right?) This way, id_Address__modification creates a new key every time you modify an Address record.


                When you select an address for an Order record, the Order::id_Address field is populated using Address::id for one of the Address records where Order::id_Customer = Address::id_Customer using whichever method above you choose. This way, when you modify any of the customer's addressses before the order is finalized, that changed address will be reflected on the order.


                The routine to finalize an order includes these steps:

                1. Check if Order » Address::id_Address__modification matches Address::id for any Address records.
                2. If there isn't an Address::id match, duplicate the Order » Address record, set the new record's Address::id field to the original id_Address__modification value from the first Address record, and clear the Address::id_Customer field.
                3. Set the Order::id_Address field to the id_Address__modification value.


                This way, the order's address will be historically accurate when you modify the customer's addresses, you achieve that without duplicating Address data any more than necessary, and the historical Address records don't pollute any views of current customer addresses.