Trouble exporting formatted calc field

[George Swisher]

I have created a calc field the formats data to export to a VCF file. The calc field gets all the data exactly how I want it with returns. I viewed it in a another look up field to see if the data format stayed when moved to a non calc field and works great. If I copy and paste the data to another program, keeps the returns perfect. When I use the export function, thr returns get removed regardless of which format I use, Tab-delimited comma-spereated, etc.

Calc------------------

Here is a sample of what the calc and result

"BEGIN:VCARD" & "RETURN SYMBOL" &

"VERSION:3.0" & "RETURN SYMBOL" &

"N:" & AccountContactLast & ";" & AccountContactFirst & ";;;" & "RETURN SYMBOL" &

Returned Field Value---------------------------

BEGIN:VCARD

VERSION:3.0

N:LastName;FirstName;;;

Return Export file--------------------------------

BEGIN:VCARDVERSION:3.0N:LastName;FirstName;;;

Does anyone know how I can export and keep the returns? The returns must be in place to import the VCF file.

thanks!

[Reed]

I believe most people use XML export to do this...

But I'm not sure there was much XML export in FMP until v5.5 or v6.

In v7, you could check out Filemaker Business Tracker. I'm pretty sure there is VCF export of contact data built into that solution, and I think it uses XSLT

(Another option, in v5, might be to use some other character in place of returns and then just do find and replace in the resulting file with a text editor. You could probably automate the process from within filemaker using applescript)

[George Swisher]

I am not too sure about using XML.

How can I create the find and replace applescript. I am getting better at scripting and may be able to accomplish that way. Any suggestions?

[Fenton]

You can also use the do shell script command, which exports the file with the correct returns, for some reason (you'd have to ask BruceR, who came up with this :-). FileMaker uses ASCII 11, Vertical Tab, internally, as it must, since a return is a record separator. Other exports preserve that ASCII 11, in order to integrate with spreadsheets, etc., but the shell script doesn't, neither does the clipboard.

The fields would need to be on the current layout for this, or on a specified layout (which I won't go into, 'cause it's different for 5 than 7). Put the following in a Perform AppleScript step. Adjust for your field names, and destination.

Paths must be Unix POSIX paths. You can put them together either in FileMaker calculations, or in AppleScript steps. The "quoted form" takes care of spaces, etc..

copy cell "filename" of current record to fileName

copy cell "Text_" of current record to exportMe

do shell script "echo -n " & quoted form of exportMe & " > ~/desktop/" & quoted form of fileName

-- -n prevents a final line feed from being added

[George Swisher]

The shell command sounds like the best option because I have a layout that has that field only and could be moved. But I need a little more hand holding to do this. I am new to the shell commands. I will try to do what you posted. Will I have to loop this for mutliple records?

You mentioned BruceR. Is there a post where I can see how to do this exaclty?

[George Swisher]

I tried this but having a little trouble.

Here is what i have but not sure if it is right because I am getting an Object not found.

copy cell Clients.fp5 of current record to fileName (am I supposed to have another file created to cop to?)

copy cell VCF_Export of current record to exportMe(am I suppsed to use this exportMe?)

do shell script "echo -n " & quoted form of exportMe & " > ~/desktop/" & quoted form of Clients.fp5

[Fenton]

I guess we're going to have to start from scratch :-] First, the word - cell - in AppleScript refers to a FileMaker field, in a found set, often specifically "of current record".

The term - field - is also valid in AppleScript, but tends to be all the values of the field in the whole table. So we're using cell.

The name of the cell (field) is the field name, but in quotes. It's just a text value in AppleScript. It is NOT the name of the current file. You don't have to tell AppleScript the FileMaker file, when you're running a Perform AppleScript step dealing with the current file (it's the default; it's in it).

So: copy cell Clients.fp5 is wrong. It would be more like:

copy cell "FileName" to filename

"filename" (without the quotes) is an AppleScript variable, which is created on-the-fly simply by naming it and copying something to it. The variable is just a convenient way to hold the data from FileMaker until we use it later. Variables can hold practically anything, and don't need quote marks around them.

"FileName" (with the quotes) is the name of the FileMaker field, which has some text that you want to use as the name of the new text file.

The "do shell script" (without quotes) AppleScript command is used to run a Unix command line.

This is the "echo " command (within quotes, with a space), which takes the input, then ">" (without quotes) creates a new file, at the path specified (by filename).

ExportMe (without quotes) is an AppleScript variable also. You are correct to copy "VCF_Export" to it, but you left off the quote marks around the cell (field) name.

You can use whatever names you want for the variables. It's very hard to write about this, because the darn quote marks (or not) are important, but we use them differently when writing English as we do in programing. If you look at my original code again, you'll see the correct form.

Just as ">" creates a file, ">>" (neither with quotes) appends to a file. So one could use that in a Perform AppleScript step, in a FileMaker Loop to add the text from each record as one went.

I've attached an example, but it is FileMaker 7. You didn't specify your version earlier, so that's what I worked on. If you need a FileMaker 5-6 file, let me know. I have one, but I'll have to modify the scripts. Or you could download a FileMaker 7 demo.

My file does it with either 1 record, or the found set, with either plain AppleScript, or with the shell script.

You could also just export the entire file as a regular FileMaker export, and run the AppleScript replace for ASCII 11 on the entire text afterwards. I don't know which would be best. Different ways to get the same result.

ExportTextfile7.zip

[George Swisher]

worked like a CHAMP! thanks for the help!!!!