1 2 Previous Next 16 Replies Latest reply on Dec 18, 2015 9:04 AM by Extensitech

    Selecting contact/address data


      I am stuck on how to pass through or select data from my Contacts and DeliveryAddresses tables.

      My process is that we have accounts which may have many contacts and also the accounts can have many addresses. To start an enquiry I am passing the AccountId through to the enquiry which allows me to display the correct account information.

      But also I want to be able to choose a contact that is related to that account and a delivery address, does anyone have any idea's how I can approach this?




      Relationships Image


        • 1. Re: Selecting contact/address data

          Short (?) answer is that, on the portal of Delivery Addresses, you give the user a way to select a contact from the Contact table. Quickest way would be to make it a popup menu, with a value list showing ID and name from Contact, but show only related from Account. Show only the second value (the name).


          A strategic consideration, though: In reality, addresses are often shared. Family members, employees at a work location, etc. The challenge with this is that if you have an address that's linked to two entities (a husband and wife, or a company and an employee) it's difficult to walk the user through the difference between one entity moving and both entities moving. Without a lot of potentially confusing interface help, the user is just going to change, say, the employee's address, not realizing that they just move the employer's address, as well, it being the same record. Likewise, sadly enough, if the wife moves, that doesn't mean the husband moved with her.


          For these reasons, we link addresses (and phones, faxes, emails and websites) to one and only one "owner". This results in some duplicate addresses, but we see the address as a record of a certain entity at the address, not a unique representation of the location itself. We try to make it easy to duplicate an address (so that, for instance, we can create a duplicate of the husband's home address and make it the wife's address), and also make it easy to see related contacts' addresses (so that, for instance, from John Smith, I can see the address of his employer, ABC Company).


          In this way, an address only gets updated within the context of one "owner", and the user doesn't inadvertently move several contacts, accounts, etc. at once.


          Just food for thought. The first paragraph may be all you need.


          Chris Cain


          • 2. Re: Selecting contact/address data

            The user chooses a contact first at the enquiry stage. I have tried to use a portal but its not displaying the correct information as I only want to show the Contacts which are related to an Account. Then the user will specify a Delivery address related to an Account.

            • 3. Re: Selecting contact/address data

              I'm not sure how to relate what you are saying to my prior response.


              I understand that the contact is chosen first. Now you are trying to add delivery addresses, and link them each to one of those contacts? Or have I misunderstood?


              The portal that you tried that is "not displaying the correct information": I'm assuming this is on the Enquiry layout? What TO are you using for the portal? What "incorrect" information are you seeing?


              Trying to help. Sorry that I need more information.


              Chris Cain


              • 4. Re: Selecting contact/address data

                Link the delivery address to the enquiry. As the delivery addresses are stored in a seperate table linked to the Accounts.


                Well before they were only showing the first record within the tables but I want the ability to select from the related records.


                I'm not sure how to link this all up and do I need another table? I need to create a link where the Contact is displayed but only from the correct related Account.

                • 5. Re: Selecting contact/address data

                  OK, so you already have delivery addresses, linked to accounts. You're not trying to add new delivery addresses.


                  You want to link specific delivery addresses, selected from those belonging to the related account(s?) to this specific Enquiry.


                  Is this correct?


                  Chris Cain

                  • 6. Re: Selecting contact/address data

                    Yes, I have a portal on Accounts that I can manually type in delivery addresses.

                    I'm just trying to think of a way to select one of these addresses or contacts in the enquiry. Yep

                    • 7. Re: Selecting contact/address data

                      OK, so are you familiar with multiline keys in FileMaker? For example, if Enquiry::DeliveryAddressesFK contained






                      and you related Enquiry::DeliveryAddressesFK to EnquiryDA::DeliveryAddressesPK, then from Enquiries you'd be linked to DA's 10, 11 AND 12. This is how, in FM, you can have simple M:M relationships without always having to have a join table.

                      So, if you add Enquiry::DeliveryAddressesFK and the relationship, the next question is how to have the user select addresses and put their PK's into this new FK field.

                      Would this work?:

                      Create a new portal for EnquiryDA, to show the actual DA's linked directly to this Enquiry


                      Create a "picker" portal:


                      • Show a portal on Enquiries of the existing DeliveryAddresses TO. Conditionally format the fields in the portal to highlight when DeliveryAddresses::PK is included in the list in Enquiry::DeliveryAddressesFK.
                      • Make the fields also a button that will set Enquiry::DeliveryAddressesFK to either add or remove the DeliveryAddresses::PK from the clicked row.
                      • Consider putting this in a popover button with a label like "select", so that it's clear which ones are "all available for selection" and which ones are already "selected"


                      Does this make sense to you? If so, what do you think? Will this do what you want/need?


                      Chris Cain


                      • 8. Re: Selecting contact/address data

                        No I'm not sorry.


                        Wouldn't it be Accounts::DeliveryAddressFK as the only link the enquiry has us to the Accounts?

                        Is EnquiryDA a separate instance of the Enquiry Table? Like creating a copy of the Enquiry table in the relationships?


                        I understand the portal part but not so much the logic on creating the link.

                        • 9. Re: Selecting contact/address data

                          Here is a reference to my tables


                          • 10. Re: Selecting contact/address data

                            EnquiryDA would be a separate instance of DeliveryAddresses. It would show deliveryaddresses selected for this enquiry, whereas the existing DA table would show available (all those belonging to existing the accounts on this enquiry).


                            Does this help?


                            Chris Cain


                            • 11. Re: Selecting contact/address data

                              Kinda, So basically I need to add a DeliveryAddressFK to the Enquiry table, create a duplicate table of DeliveryAddress? Called EnquiryDA But have this table linked to Enquiry instead of Accounts?

                              • 12. Re: Selecting contact/address data

                                Yes, that at least sounds like what I'm suggesting.


                                Then you could use that portal, to show the da's specific to this enquiry, and a popover with a "picker" portal to have the user indicate which da's should go on the list. (refer back to more details on this a few posts ago.


                                Are we on the right track now? Sorry this is taking so much back and forth.


                                Chris Cain


                                • 13. Re: Selecting contact/address data

                                  Yes we are, I get the logic. Got the portal and the portal picker. Not too sure how to insert the ID from the picker to the Enquiry Address portal though.


                                  And the same for the conditional formatting.

                                  • 14. Re: Selecting contact/address data

                                    Done it, just need help on the conditional format part haha.


                                    Cheers for the help btw.

                                    1 2 Previous Next