If you use merge fields, the empty lines will disappear automatically, something like:
<<City>>, <<State>> <<Zip>>
If Address2 is empty then city/state will side up. Otherwise, you can create calculation with:
List ( Name ; Address1 ; Address2 ; City & ", " & State & " " & Zip ]
... which will also remove blank lines.
Could I have turned sliding off somehow ?. I have used the layout wizard in FMP 11.0v3 and it has generated a layout just like yours above with merged fields.
It still prints NULL in the empty fields tho ?.
All of the merge fields should be within the same text box. Select first merge, hit return, insert second merge etc... If you are still stuck, let me know and I can send a sample.
Thanks Laretta, I think thats what I have done.
I have edited the initial post to show a screenshot.
I have just added a new record, the sliding works. All the other records were imported from another database via a excel file. Could that database have the fields down as null , meaning they dont show any text in the excel field , but FMP prints the null.
If so do i have to clean the databse by removing the NULLs ?.
FileMaker doesn't recognize NULL and Excel is usually pretty clean on imports. You might have a hidden character in the fields but I suspect a space or carriage return. Can you check one field?
Hmm , just gone back to the original file in excel after a few clean up steps . Thats where the Nulls are coming from. - Sorry. I need to fix it from excel I spose.
I have 415 records and most of them have at least one field blank in the address which has four fields.
Could I do it with a script in FMP ?.
"Could I have turned sliding off somehow"
Whoa. You said address labels. With address labels, you don't want it to slide up because you will lose the spacing when printing onto the label. What I suggested removes the blank space between the lines of the address so they don't display holes but they do not shrink the address to smaller body.
If Excel shows the fields empty then they should be empty. Could you upload your file somewhere and provide a link to it? Other that or private message me and I will send you my email address so I can test it personally?
Something is off somewhere.
I think I may have fixed it.
I have done a find and replace in excel. Found all the cells that had NULL in them. (454) , replaced them with empty cell .
Then took that file and dropped it on FMP.
Voila , new database. did the new layout - label thing for the addresses. Now the blank space has gone like you say.
Sorry to waste your time and hope someone sees this as help some day.
May I ask were the excel file and the FM solution on the same OS? Or was the Excel sheet created on PC and the solution in FMP on Mac?
Because yes, the PC will put a carriage return in there that UNIX like systems will not recognize (Mac). In cases like that it may be a good idea to add a specialized script to import the records, to prevent that in the future.
For example have a script import all the records, and then check the fields, if field is empty, then set field as "" so it removes any invisible carriage return and makes it blank or empty again.
Ah. Then the original source wasn’t Excel. The data originally came (most likely) from a SQL source? Even Access generates a pseudo-null and would import NULL into the field.
Strange that FileMaker recognizes this invisible character i.e. doesn’t consider the field blank for you. When in Excel, it is invisible and is null but as soon as you click into the cell, Excel itself removes the null.
“… records were imported from another database via a excel file. “
Did you perform straight import or did you use ODBC? I can work with the nulls in Excel but when I perform a straight import into FM, I lose it and the merge fields worked. I would love to find out what character was being recognized in your FileMaker file.
Can you create a calculation with Code ( fieldName ) and tell me what one of them says in FileMaker?
Hi rjlevesque, thank you for joining in! So the invisible character is a carriage return? I'm on Windows and I can identify the null in Excel but not in FileMaker.
Good Day LaRetta,
Yes I have run into a similar issue myself. The issue arose when importing an excel file created on Windows, but imported into FMP on Mac. See Mac is actually a window system now since they went to OS X series, it runs over the top of a flavor of UNIX called FreeBSD. Any UNIX OS or those that mimic UNIX (i.e. LINUX, IRIX, FreeBSD, OpenBSD, etc.) have a different type of carriage return than a Windows platform. The windows platform will actually leave a garbage character or NULL character, which the UNIX type OS will see.
You can see this easily using a simple text file, type a paragraph into the text file, double space lines between sentences. Now open it in a terminal editor on Mac or LINUX and you will see there is something actually there.
So...my thinking is this: In most cases you will want to actually write a script to perform an action as opposed to just a single command. So I am thinking if your import action is a script THEN...
STEP 1: import records
STEP 2: check fields for NULL
STEP 3: if check says yes, then set field space to "" (to clear out the NULL character and fill it with empty space)
What are your thoughts?
Thanks Guys , the data was produced by our IT dept probably from a SQL database to a excel file. The excel file had all the empty fields set to NULL when I opened it on my mac.
To create the database on the mac I just cleaned up the column headers and dropped the file onto FMP.
awesome! Glad you worked it out. =) (and as I suspected, created on PC, then transferred to Mac)