13 Replies Latest reply on Jan 28, 2016 10:57 AM by siplus





      Just getting to grips with Filemaker and need a little assistance.


      I have created a Calculated field as follows


      Title & " " & First Name & " " & Middle Name  & " " & Last Name & ¶ &

      Business Street& ¶ &

      Business Street 2& ¶ &

      Business Street 3& ¶ &

      Business City& ¶ &

      Business State& ¶ &

      Business Postal Code& ¶ &

      Business Country_Region& ¶


      Now I would like result shown without the empty fields, will the ISEMPTY function achieve this and how?


      Thank you

        • 1. Re: ISEMPTY

          IsEmpty() can be used to help, but it won't do much for you by itself.

          One solution is to set up a series of If statements, with the query being whether or not a field is empty, and the results being that if true, nothing is added, if false, the contents of the field and the trailing space are added.


          Something like:
          If(IsEmpty(Title);"";Title & " ")


          If(IsEmpty(First Name);"";First Name & " ")


          etc., etc., etc.

          • 2. Re: ISEMPTY

            Try this:



            Substitute( List( Title; First Name; Middle Name; Last Name; ; " " ) );

            Business Street;

            Business Street 2;

            Business Street 3;

            Business City;

            Business State;

            Business Postal Code;

            Business Country_Region


            • 4. Re: ISEMPTY

              +1! List() does not return the blank values.





              • 5. Re: ISEMPTY

                Sounds to me like you want the carriage returns to not appear if the row is empty.  I would just take the result and substitute multipul ¶ for one.  Substitute ( result ; [ "¶¶¶" ; "¶" ]; [ "¶¶" ; "¶" ] ) like so. then there would be no blank lines.  You could add a 4 to 7 instances to cover if there is almost no data.  I figure isEmpty is a great function but if your fields are empty anyway they are not displaying but the returns are.  Alternately you can just replace the ¶'s in your original calculation with 'Case ( not isEmpty "myField" ; "¶" )'

                If each line is going to have a text label ByteTheBullet would have the correct solution. I would maybe do

                Case ( not isEmtpy( filedName1 ) ; "Title1: " & filedName & "¶" )&

                Case ( not isEmtpy( filedName2 ) ; "Title2: " & filedName2 & "¶" )&

                but really its the same I just try to avoid "" when possible.

                just saw the other post

                ohhh nice one by Erik Wegweiser nice use of List I like that even more

                1 of 1 people found this helpful
                • 6. Re: ISEMPTY

                  No. Problem already solved. Use the List function as described by Erik.

                  • 7. Re: ISEMPTY

                    Wow I am overwhelmed with the swift replies!


                    Just one final question on this subject as the list function does work, hooray, but is it possible to have just the


                    Title, First Name, Middle Name, Last Name

                    on one line instead of listed? I would like this so that I can just copy/paste into a form without further editing

                    • 8. Re: ISEMPTY

                      thats why there is a substitution fiction wrapped around that.  it changes it to one line.

                      Substitute( List( Title; First Name; Middle Name; Last Name; ; " " ) );

                      1 of 1 people found this helpful
                      • 9. Re: ISEMPTY

                        How are you going to use your calculated field? If it is for a print out like labels you can use merged fields on the layout to get the same result but without the extra calculation field.


                        <<Title>> & " " & <<First Name>> & " " & <<Middle Name>> & " " & <<Last Name>>

                        <<Business Street>>

                        <<Business Street2>>

                        <<Business Street3>>

                        <<Business City>>

                        <<Business State>>

                        <<Business Postal Code>>

                        <<Business Country_Region>>

                        • 10. Re: ISEMPTY

                          Oh cool, didnt know you could just use merge fields. Thanks

                          • 11. Re: ISEMPTY

                            Merge fields will "slide" (or can slide) when printed. The List() calculation can be used and the "sliding" is done for you automatically.



                            1 of 1 people found this helpful
                            • 12. Re: ISEMPTY



                              The merge fields approach will however leave TWO spaces when the middle name is empty

                              There are pedants who can spot this from 100 paces....


                              And it should look like

                              <<Title>> <<First Name>> <<Middle Name>> <<Last Name>>

                              • 13. Re: ISEMPTY

                                IMHO it's important to specify what are you going to do with the result. Too many times we overload our tables with lots of calculation fields which are only used to - say - print a label on a Dymo printer, but are hanging around forever adding useless bytes to the data, just in case. To sum it up, sometimes it's better to calc a $$Variable on the fly, go to a layout having just that var as a merge field, do your print and bye-bye. In the printing script you build the $$var using all the tools, (list, substitute, trim, etc) but that's it, no need to store.