9 Replies Latest reply on Feb 13, 2012 11:03 AM by MarcMcCall

    Address description based on 3 fields



      Address description based on 3 fields


      Ok, so I've been working on this one for a few days with no luck.


      I need to make an address description field based on 3 fields.  FirstName, LastName, StreetAddress1.

      Not a easy as it looks, (for me anyway) I only want to use the FirstName field if it has something there, and the LastName field if it has something there, otherwise I would just use: FirstName & " " & LastName & " " & StreetAddress1.  I can't have the extra spaces if the FirstName and LastName fields are empty.

        • 1. Re: Address description based on 3 fields

          Case(IsEmpty(FirstName) and IsEmpty(LastName);StreetAddress1;
               IsEmpty ( FirstName ) ; LastName & " " & StreetAddress1 ;
               IsEmpty ( LastName ) ; FirstName & " " & StreetAddress1 ;
               FirstName & " " & LastName & " " & StreetAddress1

          Field Naming Convention Suggestion.


          Field names listed alphabetically - these are easier to find and read.

          David Anders
          The Computer Guy, Seattle

          • 2. Re: Address description based on 3 fields

            Thanks David..

            I appreciate the info on naming conventions.  I typically would name my fields about like that, but this project is not really being used as a storage database.  I'm using it as a programatic way to export data from our back office database and directly import it into this project, then directly export it out into a formatted template for upload too to the new e-commerse webstore for the company I'm working for.  Basically it's just going to save me months of exporting data in one XLS format that our system exports and reconfiguring the data to an XLS template that the website company has setup to import data directly to the website.  When I'm done i will just have to click a button and my data is ready for upload.  Thanks to all of the more knowledgable folks on here, my project will be up and running in just a couple more days...


            Thanks again..

            • 3. Re: Address description based on 3 fields

              Just for fun, here's a simpler expression:

              Trim ( Trim ( Firstname & " " & LastName ) & " " & StreetAddress1 )

              • 4. Re: Address description based on 3 fields


                Substitute ( List ( Firstname ; lastname ; streetaddress1 ) ; ¶ ; " " )

                • 5. Re: Address description based on 3 fields

                  Thanx Phil.


                  I have another question that relates to this.

                  The formula is working great to create the address description just the way I want for that table.  It is on the address table.  I have another table of contacts. When I upload to this website I'm working on, I upload all of the addresses to the address section of the website, and at checkout the customer has a box which they select the shipping address from which is going to display the description I am setting up with this formula.  My question is, for my contacts, when I upload them to the website, I have to use an address description that will select the default shipping address from one of the addresses I uploaded that will be for that contact, and it has to be one of the ones I set up for the addresses.  My problem is, When I export the contacts from the back office system it only has the address, and nothing that is going to look like one of the descriptions I am formatting.


                  Our back office system use these fields for contacts:

                  First Name, Last Name, Street1, Street2, City, State, Zip.


                  It uses theses fields for the shipping addresses.

                  First Name, Last Name, Street1, Street2, City, State, Zip.


                  The contact would look like this:

                  FirstName: Joe  LastName: Contact

                  Street1: 1234 street


                  city,state zip


                  The address may look like this:

                  FirstName: Joe Contact/Bld  LastName: 200 PODS 29s

                  Street1: 1234 Street

                  Street2:  Bld 200

                  City, State zip


                  The formula I used will create a description Like this: Joe Contact/ Bld 200 PODS 29s 1234 Street

                  I want to try to have a formula look for the description using the names IF they are present because not all of the addresses I have use the first and last name field just the street fields.  If the name and address don't match, I just want to use the address that matches.

                  • 6. Re: Address description based on 3 fields

                    I'm not sure I understand the problem completely. Is the example for the second address example deliberately sabotaged to illustrate how two different addresses might produce an identical line of text? Only way I can think to avoid that is to insert delimitter text between each field to produce something like:

                    Joe $$$ Contact $$$ 1234 Street

                    When the input is from the first address example.

                    • 7. Re: Address description based on 3 fields

                      I came up with a solution, but it is just a little time consuming, but not too dificult.  I set up a value list based on the calculation field I created for the address_description, and made a dropdown list on the contacts page that uses that value list. I just have to choose the best fit description for each contact.  I wish I could figure out something a little more automated, but I think there may be too many variables for me to get a correct  answer, or explain.  Yesterday I made a look up field that returned an aswer and had the correct address for the ones that has the same name somewhere in the description, but just filled all the other ones in with a first and last name, but now i can't seem to recreate that solution to tell you what I did.

                      • 8. Re: Address description based on 3 fields

                        You might want to experiment with a filtered "selection portal". This is a portal with a global text field, a filter that uses that global filed and a script trigger controlled script that updates the filtered portal.

                        There are several examples in this demo file: http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html

                        • 9. Re: Address description based on 3 fields

                          That would be a nice feature, I think it may take me a few weeks just to figure out how to make it work.  I'm having a meeting later this week, and having the VP request all of the new additions to the back office system get filled out according to my script setup, and update any existing ones as they use them on a daily basis.  We arent going to be uploading our whole DB at one time, so it will give them time to fill in some of the data, and make it easier for me in the future.