11 Replies Latest reply on Jul 9, 2009 2:12 PM by comment_1

    Using substitute to adjust format of addresses.

    ErichWetzel

      Title

      Using substitute to adjust format of addresses.

      Post

      We have a field which contains a manually entered list of places in the following format:

      Example = Place One, 123 address street, Philadelphia, PA 19111 or Place Two, 456 address street, Camden, NJ 08026.

       

      I am trying to change the format to

       

      Place

      123 address street

      Philadelphia, PA 19111

      or

      Place Two

      456 address street

      Camden, NJ 08026 

       

      Using the Substitute function, I have been able to replace all instances of ", " with a carriage return.  However that results in the city and state being put on separate lines.  

       

      Philadelphia

      PA 19111

       

      This looks odd when reading the form we put this information on.

       

      The state abbreviation is always in capitals.  Is there any way to have substitute for ", " only happen when not followed by two capital letters?  I have not been able to figure out how to test for that.

       

      I have a different way I can handle this but due to the nature of our structure, it is much more involved than the substitution I am hoping to accomplish. 

       

      Thanks

      -Erich 

        • 1. Re: Using substitute to adjust format of addresses.
          philmodjunk
            

          You can do this, but it'll take a fairly complicated substitute function.

           

          Step 1:

           

          Set field [table::address field; Substitute (table::address field; [", CA"; ":: CA"]; [", PA"; ":: PA"]....)]

          (You need a bracketed expression for every state used in one of your addresses)

           

          Then use

           

          Substitute ( table::address field; ",", "¶")

           

          to put in your line breaks.

           

          Finally use

           

          Substitute ( table::address field; "::", "," )

           

          To put back the comma.

           

          You could nest all of this into one massive substitute function, but it seemed clearer to set this up one step at a time.

          • 2. Re: Using substitute to adjust format of addresses.
            mrvodka
              

            Well if the commas are always stable then you can use something like this.

             

             

            Let ( raw = Substitute ( YourAddressField; ", "; ¶ );
                   Left ( LeftValues ( raw; 3 ); Length ( LeftValues (raw; 3 ) ) -1 ) & ", " & RightValues ( raw; 1 )
                 )

            • 3. Re: Using substitute to adjust format of addresses.
              etripoli
                

              Familiar with Regular Expressions?  Here's a regex plug-in for FM:

               

              http://jensteich.de/regex-plugin/

              • 4. Re: Using substitute to adjust format of addresses.
                ErichWetzel
                  

                Thanks for the ideas.

                PhilModJunk - yours is probably best solution for the problem though I hate to put in a substitute for every state plus DC.

                mr_vodka - I like the idea but the content of the field we are substituting in is usually but not necessarily static.

                etripoli - I have a little experience with regexp but not enough to try to use it right now.  I'll take a look at the plug in as a follow up.  Thanks. 

                 

                Erich 

                • 5. Re: Using substitute to adjust format of addresses.
                  philmodjunk
                    

                  I had a new thought that might reduce the complexity. Keep in mind that my earlier suggestion will break if there's an extra space or other characther between the comma the state abbreviation or if the state abbreviation isn't all caps.

                   

                  Is the "state" comma always the third comma?

                   

                  If so,

                   

                  set field [addressfield; let (x = Position(addressfield; ",", 1, 3); Left(addressfield; x - 1) & "::" & Right(addressfield; x + 1))]

                   

                  would avoid the 51 bracket expression substitute.

                  • 6. Re: Using substitute to adjust format of addresses.
                    comment_1
                       I think the best solution would be to normalize your data: have one record per address, with separate fields for StreetAddress, City, State and ZIP.
                    • 7. Re: Using substitute to adjust format of addresses.
                      raybaudi
                        

                      Another possible calculation:

                       

                      Let([
                      L = Length ( place ) ;
                      rest = Middle ( place ; Position ( place ; "," ; L ;  - 1 ) ; L ) ;
                      address = Substitute ( place ; [ rest ; "" ] ; [ ", " ; ¶ ] )
                      ];
                      address & rest
                      )

                       

                      where "place" is the name of your field.



                      • 8. Re: Using substitute to adjust format of addresses.
                        ErichWetzel
                          

                        PhilModJunk and Raybaudi

                        Thanks for the further ideas.  I have already implemented the 51 item substitute, but I'll keep these ideas for streamlining down the road.

                         

                        comment

                        Thank you for your consideration.  I was looking for a solution to the specific problem addressed here.  

                        As I mentioned I have an alternate method to resolve this which is not convenient based on our current structure.  That method is what you have suggested here.  The source of address content for this field is a related address book table where what you are suggesting is already done.  This field is a very special case and can often contain information and formatting beyond the basics I have outlined here.

                         

                        -Erich 

                        • 9. Re: Using substitute to adjust format of addresses.
                          comment_1
                            

                          ErichWetzel wrote:
                          The source of address content for this field is a related address book table where what you are suggesting is already done. 

                          I am confused: if the source is normalized, why not get the data directly from there? It seems like you might have a redundancy issue here, and "the specific problem addressed here" is only a symptom.

                           

                          In any case,  if you really must process the data as given, there are simpler alternatives - but a lot depends on whether there always will be exactly two addresses. You should also state your exact version, so that we know whether a custom function is a viable suggestion.


                          • 10. Re: Using substitute to adjust format of addresses.
                            ErichWetzel
                              

                            Comment

                            I am adjusting the text format and paragraphing, not the content of this field.  I do not want to get into a debate about the semantics of our system and its setup as it would take too much time to write and not be productive use of your time and anyone else who happens to read it.

                             

                            Yes I am really processing the content of this specific field.  Yes I am doing it this way for a reason.  Yes there is a potential redundancy issue in relation to the master address in the address source table.  Yes there are a variety of potential problems with what I am doing from a fundamental design standpoint.  Yes this method is exactly what we need and what we want to do.

                             

                            All of that being said, there may be one to as many as four addresses along with a variety of other pieces of information in the field.  This field does not have a static volume of content, but it does have a static format to the addresses and other content entered.

                             

                            We are using FileMaker Pro 10 and FMSA 10. 

                            • 11. Re: Using substitute to adjust format of addresses.
                              comment_1
                                

                              <shrug>  You don't need to convince me (and anyway you'd have a hard time convincing me that putting a needle in a haystack, then looking for it is a good idea).