1 2 Previous Next 15 Replies Latest reply on May 23, 2015 3:35 AM by keywords

    Is there anything wrong with my concatenation?

    docpanderson

      Concatenation problem.JPG

      I'm trying to combine the first and last name and address including Address line 2 if anything is in that field and it's not working.  Tried to back up do it more simple just doing first and last name and it won't do that.  It's got to be something I'm overlooking?

      Peter

        • 1. Re: Is there anything wrong with my concatenation?
          taylorsharpe

          Let ( [

           

          F1 = First & If ( Not IsEmpty ( First ) and Not IsEmpty ( Last ) ; " " ) & Last ;

          F2 = If ( Not IsEmpty ( Address1 ) ; Address1 & ¶ ) ;

          F3 = If ( Not IsEmpty ( Address2 ) ; Address2 & ¶ ) ;

          F4 = City & If ( Not IsEmpty ( City ) and Not IsEmpty ( State ) ; ", " ) & State and If ( Not IsEmpty ( State ) and Not IsEmpty ( Zip ) ; "  " ) & Zip

          F5 = F1 & ¶ & F2 & F3 & F4

           

          ] ;

           

          F5

           

          )

           

           

           

          =============

           

          Your concatenations look good to me...  First & ", " & Last   Other than I probably wouldn't have a comma in there. 

          • 2. Re: Is there anything wrong with my concatenation?
            erolst

            "It's not working" is not a really good error description (though it sounds better than "no luck"!)

             

            First&", "&Last looks syntactically valid (aka: it should work), though I suggest getting into the habit of using whitespace:

             

            First & ", " & Last (but isn't that bass ackwards?)

             

            Try this:

            Let ( [

              theFirst = Substitute ( List ( First ; Initial ) ; ¶ ; " " ) ;

              theName = Substitute ( List ( Last ; theFirst ) ; ¶ ; ", " ) ;

              theAddress = List ( street1 ; street2 ; Substitute ( List ( zip & city ; state ) ; ¶ ; ", " ) )

              ] ;

              List ( theName ; theAddress )

            )

             

            Not exactly up to snuff on US address format, so adapt as necessary; the main thing is to understand how to combine List() and Substitute() to save yourself a bunch of Case()s.

             

            EDIT: … or If()s …

            • 3. Re: Is there anything wrong with my concatenation?
              docpanderson

              Taylor,

               

              Thanks for the help.  I tried your method but had to change the names of some of the fields to match what I have in my table.  Here's what I put:

               

              Let ( [

               

              F1 = First & If ( Not IsEmpty ( First ) and Not IsEmpty ( Last ) ; " " ) & Last ;

              F2 = If ( Not IsEmpty ( Home Address 1 ) ; Home Address 1 & ¶ ) ;

              F3 = If ( Not IsEmpty ( Home Address 2 ) ; Home Address 2 & ¶ ) ;

              F4 = Home City & If ( Not IsEmpty ( Home Postal Code ) and Not IsEmpty ( Home State ) ; ", " ) & Home State and If ( Not IsEmpty ( Home State ) and Not IsEmpty ( Home Postal Code ) ; "  " ) & Home Postal Code = F1 & ¶ & F2 & F3 & F4

               

              ] ;

               

              F5

               

              )

               

               

              F4 not found.JPG

              Now its looking for F4?

              Did I mess something else up?

              Peter

              • 4. Re: Is there anything wrong with my concatenation?
                docpanderson

                I mistakenly put a comma in there because I was previously working on the "City, State" concatenation.  You are right.  I need it to say

                FirstName LastName

                Address1

                Address2 (if there is info in this field, otherwise leave it blank and go to

                City, State  Zip

                 

                What is this white space you speak of?  Is it just the spaces between the characters?

                Erolst.JPG

                Using your suggestion and changing a few of the field names I get the above result.  It looks better but is still not entering the City, State or Zip.  Here is what I did to change what you suggested.

                 

                Let ( [

                   theFirst = Substitute ( List ( First ; Initial ) ; ¶ ; " " ) ;

                  theName = Substitute ( List ( Last ; theFirst ) ; ¶ ; ", " ) ;

                  theAddress = List ( Home Address 1 ; Home Address 2 ; Substitute ( List ( Home Postal Code & Home City ; Home State ) ; ¶ ; ", " ) )

                ] ;

                List ( theName ; theAddress )

                )

                Any more ideas?  Danke!

                • 5. Re: Is there anything wrong with my concatenation?
                  mariaocheltree

                  I think that after the Home Postal code should be ;

                  theAddress = List ( Home Address 1 ; Home Address 2 ; Substitute ( List ( Home Postal Code ;  Home City ; Home State ) ;    " ¶ "; " " ) )

                  • 6. Re: Is there anything wrong with my concatenation?
                    erolst

                    docpanderson wrote:

                    Using your suggestion and changing a few of the field names I get the above result.  It looks better but is still not entering the City, State or Zip.

                     

                    I pasted the code you posted above into a table that uses your exact field names:

                     

                    AddressCalculation_eos.png

                     

                    So except for a missing space between zip and city, this should work exactly as advertised.

                     

                    docpanderson wrote:

                    What is this white space you speak of?  Is it just the spaces between the characters?

                     

                    Whitespace means adding blanks and carriage returns between the syntactical entities (operators, expressions, delimiters) in a calculation to make it (more) readable; compare these two expressions

                     

                    Let([myVal=4;res=Substitute(List(Case(not Mod(myVal;2);"add");Substitute(MiddleValues("m¶o¶r¶e¶w¶h¶i¶t¶e¶s¶p¶a¶c¶e";5;10);¶;" "));¶;" ")];res)

                     

                    to

                     

                    Let ( [

                      myVal = 4 ;

                      res =

                        Substitute (

                          List (

                            Case ( not Mod ( myVal ; 2 ) ; "add" ) ;

                            Substitute (

                              MiddleValues ( "m¶o¶r¶e¶w¶h¶i¶t¶e¶s¶p¶a¶c¶e" ; 5 ; 10 ) ;

                              ¶ ; " "

                              )

                           ) ;

                          ¶ ; " "

                        )

                    ] ;

                    res

                    )

                     

                    which, as far as the FileMaker calculation engine is concerned, are identical.

                    • 7. Re: Is there anything wrong with my concatenation?
                      keywords

                      You calc works for me:

                       

                      Let ( [

                          first = "Billy"

                        ; initial = "Q"

                        ; last = "The Kid"

                        ; Home Address 1 = "Ten Skeletons Row"

                        ; Home Address 2 = "Black Jack Ranch"

                        ; Home City = "Way Out West"

                        ; Home State = "WW"

                        ; Home Postal Code = "99999"

                        ; theFirst = Substitute ( List ( First ; Initial ) ; ¶ ; " " )

                        ; theName = Substitute ( List ( Last ; theFirst ) ; ¶ ; ", " )

                        ; theAddress = List ( Home Address 1 ; Home Address 2 ; Substitute ( List ( Home Postal Code & Home City ; Home State ) ; ¶ ; ", " ) )

                      ] ;

                      List ( theName ; theAddress )

                      )


                      … gives the result:


                      The Kid, Billy Q

                      Ten Skeletons Row

                      Black Jack Ranch

                      99999Way Out West, WW

                       

                      You obviously need to add a space between postal code and city.

                      • 8. Re: Is there anything wrong with my concatenation?
                        DavidJondreau

                        You dropped the last segment from the calculation that Taylor gave you.

                         

                        You need to add:

                         

                        ] ;

                         

                        F5

                         

                        )

                        • 9. Re: Is there anything wrong with my concatenation?
                          docpanderson

                          Erolst,

                           

                          Please review the following snips and see if you can see why I am getting the results I'm getting.  Thomas Jefferson displays name and address but not city,state and zip.  George Washington does not display any info.???

                          I appreciate you help!

                          Peter

                           

                          Full Name field.JPG

                          Jefferson.JPG

                          Washington.JPG

                          • 10. Re: Is there anything wrong with my concatenation?
                            erolst

                            Peter –

                             

                            Me (or someone) should have noticed this earlier: this seems to be not the Specify Calculation dialog for a calculation field, but for the auto-enter calculation of a regular text field.

                             

                            If this is the case (and it should be), make sure to uncheck the "Do not replace …” option for that auto-enter calc. Then modify and re-modify the contents of a referenced field to see the changes take effect.

                             

                            If it works as intended, use Replace Field Contents with the same calculation to update the field for the existing records.

                             

                            – Oliver

                            • 11. Re: Is there anything wrong with my concatenation?
                              docpanderson

                              Oliver,

                              That was it.  Thanks you are awesome!  Next problem...

                              I have a button on my contact sheet (Open New Referral) that runs a script to open the referral pad, start a new referral number and automatically transfers the patient demographics (thanks to you).  Now I want to be able to pick to whom the referral is going to.  For instance if the drop down box above indicates the referral is for massage then I would like to pick from the list of massage therapists.  If it is for physical therapy- the list of physical therapists, and if radiology a list of radiology centers.  I have a separate db of provider types broken into catagories based on the above.  I'm playing around with scripts that would allow me to access the providers db but if you have any ideas let me know.

                              Open new referral.JPG

                              that runs a script to open the referral pad, start a new referral number and automatically transfers the patient demographics (thanks to you).

                               

                              Need referral to button.JPG

                              Now I want to be able to pick to whom the referral is going to.  For instance if the drop down box above indicates the referral is for massage then I would like to pick from the list of massage therapists.  If it is for physical therapy- the list of physical therapists, and if radiology a list of radiology centers.  I have a separate db of provider types broken into catagories based on the above.  I'm playing around with scripts that would allow me to access the providers db but I don't know how to tell the script to look at the provider database and open what amounts to a drop down list which for the image above would be the massage therapists so that I could pick a particular therapist to send the patient to.

                               

                              Here is the form in layout view

                              Layout view.JPG

                              I have a relationship set up between my referral to field of the Referral table and the Provider Total field of the Provider table.  The Provider Total field is same as the Patient demographics field you helped me set up previously.  It will have the providers full name, address and a phone number.

                              Provider to Referrals.JPG

                              Any resources you could suggest on learning how to write scripts would be appreciated.  If see a better way of doing what I am trying to do I would accept alternate methods.  As always.  Thank you very much.

                              Peter

                              • 12. Re: Is there anything wrong with my concatenation?
                                keywords

                                Hi docperson. You don't need a script to accomplish that, only a related Value List. See attached demo file for the basic method.

                                • 13. Re: Is there anything wrong with my concatenation?
                                  erolst

                                  docpanderson wrote:

                                  That was it.  Thanks you are awesome!

                                   

                                  Glad to hear it …

                                   

                                  Next time you have another question/issue, please start a new topic (and maybe mark it as a question?)

                                  • 14. Re: Is there anything wrong with my concatenation?
                                    erolst

                                    keywords wrote:

                                    Hi docperson. […]

                                     

                                    1 2 Previous Next