1 2 Previous Next 18 Replies Latest reply on Dec 27, 2012 4:42 PM by philmodjunk

    Contacts with multiple addresses

    EP

      Title

      Contacts with multiple addresses

      Post

           Do most of you always create a new table just for addresses?  I have a solution where the address fields are within the Contacts table.  Most contacts have 1 address, on rare occasions there are 2-3.  Not sure if I should create a new table for addresses or the best way to porceed.  I want to be able to mark 1 as primary.  Any suggestions?

           Thanks

        • 1. Re: Contacts with multiple addresses
          davidanders

               I have found it best to design for worst case. "Most" is not a very high bar for design considerations.

               You can add a NOTES Table and let that collect the debris.

               I have often added a Jr | Sr  namefield and a Phd | MD | DDS namefield  - people that have them, think they are important. If they are donors or clients....

          • 2. Re: Contacts with multiple addresses
            philmodjunk

                 I generally use a related table for all the phone numbers, email addresses and social networking URLs, one such entry per related record with a "type" field to identify the type of contact entry. That works very well for the plethora of contact numbers, etc that can be associated with a single contact.

                 But in many contexts, one, occassionally two addresses (Shipping and Billing, for example) may be all that you need. The need for a related table is thus much less compelling unless you find that you have multiple contacts with the same address. A tab control with a home and a business tab may be an acceptable alternative to that related table, or the fields on those two tabs might very well link to different records of that related table...

            • 3. Re: Contacts with multiple addresses
              EP

                   Thanks I'm going to go with 2 address fields and a tab control.  Is there any way that I can set one as primary and be able to toggle between which if the 2 is primary via a script?  Reason I ask is that I created a search portal that includes name and address, and I need to show the primary address (which can sometimes change). Thanks

              • 4. Re: Contacts with multiple addresses
                philmodjunk

                     A field can designate which address is the primary. If the address records come from a related table, a find performed on your contact record specifying address info will find a contact with that address whether the address is or is not the primary address.

                     You can give tab control panels object names in the Name box at the top of the Inspector's position tab. Once you have named the panels, you can use the go to Object script step to put the focus on a specific tab panel--which pops it to the front.

                • 5. Re: Contacts with multiple addresses
                  EP

                       Ok, this is where I'm at:  Contacts table with 2 address fields.  When orders are taken, the items are selected and then the contact is selected for the invoice.  I have a name search portal that displays the contacts name and address; when clicked, the contacts name and address appears on the invoice.  If the user wants to use address 2, I can't figure out a way to select address 2 on the onvoice (the calc field I use on the invoice recognizes Address 1).  Any thoughts?

                  • 6. Re: Contacts with multiple addresses
                    philmodjunk

                         Use calculations with If or case functions to select the correct addresses. These can be unstored calculation fields in Invoices or auto-enter calculations on text fields in invoices. (Or you put calculation fields in the contacts table and look up from the calculation fields). I recommend that you NOT use unstored calculation fields for this. It's often a good idea to preserve the contact info that was current at the time the invoice was created and not have this info automatically update on past invoices when this data is updated for a given address.

                         IF ( AddressSelect ; Address1 ; Address2 )

                         Put a 1 in addressSelect to see the first address field, put 0 or leave it blank to see the second address field. Define AddressSelect in Invoices if you want to specifically select an address on each new invoice, otherwise, define AddressSelect in the contacts table.

                    • 7. Re: Contacts with multiple addresses
                      EP

                           I got this working.  Thank you. 

                           The only thing I'm having difficulty with is making the calc fields "Stored."  I keep getting an error message: 

                           "The calculation “PatientAddressSelect” cannot be stored or indexed because it references a related field, a summary field, an unstored calculation field, or a field with global storage."

                           My calc field references a field from another table.  Any advice?

                      • 8. Re: Contacts with multiple addresses
                        philmodjunk

                             If your calculation references data in another table or a field with global storage it cannot be a stored calculation. Do you really need it to be stored?

                             There are work arounds, but aren't ones I recommend that you use unless you have a compelling reason to keep this in a stored field.

                        • 9. Re: Contacts with multiple addresses
                          EP

                               The address fields are part of the Contacts table.  On the invoice I use a calc field Invoices::Address = Contacts::Address.  How do you do it? This allows me to show the address in a portal based on the invoice table.

                          • 10. Re: Contacts with multiple addresses
                            philmodjunk

                                 I put the Contacts::Address field on my invoices layout and thus do not need any calculation field..

                            • 11. Re: Contacts with multiple addresses
                              EP

                                   The problem I'm having is that I set this upto allow the user to pick between 2 addresses to add to the invoive.  I created a field called AddressChoice and when an address is clicked from the name search portal a script runs and sets the AddressSelect field to "0" or "1."  Then, AddressChoice (calc field) updates based on the following statement IF ( AddressSelect ; Address1 ; Address2 ).  This changes the address on all other invoices asociated with the contact.

                              • 12. Re: Contacts with multiple addresses
                                philmodjunk

                                     Which is note what you described in your post immediately previous to this one.

                                     Why does the fact that this calculation field is unstored create an issue for you?

                                • 13. Re: Contacts with multiple addresses
                                  EP

                                       I want it to be stored so I can review old invoices and the information will be accurate to that invoice.  Currently, when a new invoice is created and a different address is selected, say Address2, the old invoice changes to Address2 as well (old invoice had Address1 used).

                                  • 14. Re: Contacts with multiple addresses
                                    philmodjunk

                                         This would be the case with both stored and unstored calculation fields.

                                         But try using an auto-enter calculation on a text field instead of a calculation field.

                                    1 2 Previous Next