Retrieving Info from Filemaker with Header and Footer information

If you need to do this on a repeating basis then you need to use a Filemaker plugin such as Troi File Plug-in. You can get this from Troi Automatisering at http://www.troi.com. It allows you to create a text file one line at a time rather than trying to export the data directly from Filemaker.

You would create a script with the following steps
1) Create the header info and store it in a global. eg. "data     version1     " & DateToText(Status(CurrentDate)) & "_"& TimeToText(Status(CurrentTime)) & "¶"

Then write the header global out to a file using the plugin commands.

2) create a loop to start at your first record, write that data, and go to the next record, continuing the process to the end of the found set.

3) Create the footer info and store it in global. eg. "data     version1     " & DateToText(Status(CurrentDate)) & "_"& TimeToText(Status(CurrentTime)) & Status(CurrentFoundCount) & "lines¶"

Then write it out as the last line to the file.

This will give you what you're looking or something real close.

I am not sure if you are telling me I have  to do this one line at a time.  We are going to be sending groups of records at a time so I wanted to do my search and then export with the header and footer added automatically.   I am not sure how to create a footer field that will tell me how many lines are in the export.  They keep referring to this whole thing as the envelope.  I am not a programmer so I do not know what they mean.

I'm sorry if I wasn't clear. You create a script in ScriptMaker that does all of the steps I had mentioned. All you have to do is run your search which will return a number of records.

Then you run the script either from the menu or from a button on your layout. It will create an empty text file and will write out the header first to that file, looping through all of your found records, appending each record out one at a time to the file, and finally will append the footer to the same file.  A calculation field defined as status(CurrentFoundCount) will return the number of lines or records.

I have to be honest and say that I don't know what that they mean by 'envelope' either and I am a programmer. Sometime people just use words in the wrong context.

PS the number "6" that showed up in my previous message was supposed to be the paragraph or new line symbol. Copy/paste onto html forms doesn't always work correctly.

Do I still need the Troi plug in?

Yes, you still do. The two main scripts "Export setup" & "Export Records" are as follows:

--Export setup--
Allow User Abort [ On ]
Enter Browse Mode
Set Field [ g_Number, External("Troi-Set Default Separator", "|") ]
Set Field [ g_Number, "" ]
Set Field [ g_TheFileSpec, "" ]
Set Field [ g_ErrorCode, "" ]
Set Field [ g_XplatformReturn, "P " ]
Set Field [ g_TheFileSpec, External("TrFile-Save FileSpec Dialog", "Where should I save the file?|dataexp.txt")) ]
If [ Left(g_TheFileSpec, 2) <> "$$" ]
     Set Field [ g_Number, External("TrFile-SetDefaultFileSpec", g_TheFileSpec) ]
     Set Field [ g_ErrorCode, External("TrFile-CreateFile", g_TheFileSpec) ]
     If [ g_ErrorCode <> 0 ]
          Set Field [ g_ErrorCode, External("TrFile-DeleteFile", g_TheFileSpec) ]
          Set Field [ g_ErrorCode, External("TrFile-CreateFile", g_TheFileSpec) ]
     End If
Else
     Exit Script
End If

--Export Records--
Allow User Abort [ On ]
Perform Script [ "Export setup" ]
[ Sub-scripts ]
Set Field [ ic_exp_num, 0 ]
Loop
     Set Field [ ic_exp_num, ic_exp_num + 1 ]
     If [ Left(g_TheFileSpec, 2) = "$$" ]
          Go to Record/Request/Page
          [ First ]
          Exit Script
     End If
     Set Field [ g_ErrorCode, External("TrFile-AppendContents", data_to_export& "P ") ]
     If [ g_ErrorCode <> 0 ]
          Beep
          Show Message [ Buttons: "OK", "Cancel", ""; Data: "An error occurred" ]
     End If
     Go to Record/Request/Page
     [ Next, Exit after last ]
End Loop
Go to Record/Request/Page
[ First ]
Refresh Window

I am going to try your suggestion with the trial version of Troi.  I will let you know if I can get this to work.  

I am having a hard time.  I have managed to create the global header and footer fields (had to pull out the filemaker bible just to do that much).  However, when I exported the global field just to make sure the format was correct, there were no tabs.  The header and footer must have tabs between each item.  For example:

Job_Number (tab) Version (tab) mm-dd-yyyy (space) hh:mm:ss (new line)

Instead I have spaces which I guess will not work when they upload.  

I am still trying to understand the scripts you gave me.  What does g_number mean?  Also, what does ic_exp_num mean?  Are those fields I must create?  I am still reading the user guide for Troi and it mentioned that I had to create some of the fields in my database.  

Please Help!

I am having a hard time.  I have managed to create the global header and footer fields (had to pull out the filemaker bible just to do that much).  However, when I exported the global field just to make sure the format was correct, there were no tabs.  The header and footer must have tabs between each item.  For example:

Job_Number (tab) Version (tab) mm-dd-yyyy (space) hh:mm:ss (new line)

Instead I have spaces which I guess will not work when they upload.  

I am still trying to understand the scripts you gave me.  What does g_number mean?  Also, what does ic_exp_num mean?  Are those fields I must create?  I am still reading the user guide for Troi and it mentioned that I had to create some of the fields in my database.  

Please Help!

I separated all the items I mentioned in the previous post to individual global fields.  Can I still put them on one line for a header and footer?

It can be confusing at first. I use a naming convention for my field definitions - anything that is a global starts with "g_" ie 'g_number', internal calculations - "ic_" 'ie ic_exp_num' short for 'intenal calc exported number.'

if you try to use FileMakers regular export, any tabs in the fields will get stripped out. There is no way around this other than using Troi-File to create that line in a file

Use the Troi createfile.fp5 as a sample. Create" Job_Number (tab) Version (tab) mm-dd-yyyy (space) hh:mm:ss (new line)" in wordpad or such with the correct tabs and spaces. Paste this into the troi sample and check out the file it creates. It should have all the tabs and spaces in place.

The next step would be to create a copy of your existing database and define the following text globals:

g_ErrorCode
g_exp_num
g_footer
g_header
g_newline
g_Number
g_TheFileSpec

define the following calculation field in your database copy:

data_to_export * Note: this would consist of all of your data fields that have to exported

Create two scripts called "Export setup" and "Export records"

"Export Setup" is as follows:

Allow User Abort [ On ]
Enter Browse Mode
Set Field [ g_Number, External("Troi-Set Default Separator", "|") ]
Set Field [ g_Number, "" ]
Set Field [ g_TheFileSpec, "" ]
Set Field [ g_ErrorCode, "" ]
Set Field [ g_newline, " " ]
# Note: the space is newline character.
Set Field [ g_header, Job_Number& "     "&Version& "     "&DateToText(Status(CurrentDate))&" "&TimeToText(Status(CurrentTime))&g_newline ]
# Note: the first and second spaces are tab characters.
Set Field [ g_footer, Job_Number& "     "&Version& "     "&DateToText(Status(CurrentDate))&" "&TimeToText(Status(CurrentTime))& "     "&Status(CurrentFoundCount) &g_newline ]
# Note: the first, second and fourth spaces are tab characters.
Set Field [ g_TheFileSpec, External("TrFile-Save FileSpec Dialog", "Where should I save the file?|File.txt")) ]
If [ Left(g_TheFileSpec, 2) <> "$$" ]
     Set Field [ g_Number, External("TrFile-SetDefaultFileSpec", g_TheFileSpec) ]
     Set Field [ g_ErrorCode, External("TrFile-CreateFile", g_TheFileSpec) ]
     If [ g_ErrorCode <> 0 ]
          Set Field [ g_ErrorCode, External("TrFile-DeleteFile", g_TheFileSpec) ]
     End If
Else
     Exit Script
End If


"Export records" is as follows:

Allow User Abort [ On ]
Perform Script [ "Export setup" ][ Sub-scripts ]
Set Field [ g_exp_num, 0 ]
Set Field [ g_ErrorCode, External("TrFile-AppendContents", g_footer) ]
Loop
    Set Field [ g_exp_num, g_exp_num + 1 ]
    If [ Left(g_TheFileSpec, 2) = "$$" ]
         Go to Record/Request/Page [ First ]
         Exit Script
    End If
    Set Field [ g_ErrorCode, External("TrFile-AppendContents", data_to_export&g_newline) ]
    If [ g_ErrorCode <> 0 ]
         Beep
         Show Message [ Buttons: "OK", "Cancel", ""; Data: "An error occurred" ]
    End If
    Go to Record/Request/Page [ Next, Exit after last ]
End Loop
Set Field [ g_ErrorCode, External("TrFile-AppendContents", g_header) ]
Go to Record/Request/Page
[ First ]
Refresh Window

This should solve your problems although you may have to make some minor changes to get the output exactly right.

Mike

Is the data_to_export a global field that I should use a script to populate?

Nope, not a global. It's a calculation field that consists of your data fields all concatenated or joined together - first name & last name & address & city etc or whatever it is your trying to export.

I am really not as dense as I must seem but is the following the correct global header field script calc?

"JOB_TICKET_UPDATE" & "     " & "Version: 1.0" &"     " & DateToText(Status(CurrentDate)) & " " & TimeToText(Status(CurrentTime)) & "¶"

I copied the tab spaces into the calculation even though they do not show up.

I have now created a data_to_export calc field as follows:

JOB_TICKET_ID &"     "  & STATUS

I only used two fields to keep it simple for now.

Am I going in the right direction?

By the way, I really appreciate your help!

Oops, the 6 is the paragraph symbol.  

I see where you put the header script into the export calc.  However, I am getting "$$-4222" in the file spec field when I run the export setup script.  What does it mean?

Yes, you are definitely headed the right way.

Just a few notes though.

If JOB_TICKET_UPDATE is the name of a field then you should remove the quote marks around it, if not then leave as is.

The paragraph symbol is used if you're using a Mac but if you're using Windows then the paragraph symbol should be followed by a linefeed. I had inadvertently left that out when I had previously defined 'g_newline'

Thank you, I don't mind helping out.

Mike

Am I suppose to put something in the following fields:

g_Number
g_newline
g_TheFileSpec
g_ErrorCode

I have them all set up as global text fields and I have added the export scripts but I am not getting anything but a "P" in the new line field and "$$-4222" in the file spec field.

The error code $$-4222 is "The demo plug-in stopped working because you have used the plug-in too long. (kErrTryOutTimeOver)".

The solution is quit and relaunch FileMaker..

The error code $$-4222 is "The demo plug-in stopped working because you have used the plug-in too long. (kErrTryOutTimeOver)".

The solution is quit and relaunch FileMaker..

I is partially working!  However, here is what I got:

JOB_TICKET_UPDATE     Version: 1.0     5 lines     4/6/2003 10:35:36
1234P5678P5796P


I got the footer line where the header should be and I did not get a header.  The data should be as follows:

Header
1234(tab)Waiting(next line)
5678(tab)Waiting(next line) and so on
Footer

What did I do?

I noticed that I had flipped the order of the following in the "Export records" script
Set Field [ g_ErrorCode, External("TrFile-AppendContents", g_footer) ]
and Set Field [ g_ErrorCode, External("TrFile-AppendContents", g_header) ] .

It should be header first and footer second.

Also 'Set Field [ g_exp_num, g_exp_num + 1 ]' is redundant for your needs. I used it in my scripts to determine the exact number of lines that were exported based on a flag setting.
 

Now I am getting a footer and no header.

Here it is:

1234P5678P5796PJOB_TICKET_UPDATE     Version: 1.0     5 lines     4/6/2003 10:58:07

The data fields are not coming out right but I will see if I can figure it out.  I am having to learn the syntax.   I did switch the header and footer fields and I removed the line that you mentioned (g_exp_num).  

I get the following on my system.

JOB_TICKET_UPDATE     Version: 1.0     4/6/2003 12:02:59
1234     (tab)Waiting
5678     (tab)Waiting
5796     (tab)Waiting
JOB_TICKET_UPDATE     Version: 1.0     3 lines     4/6/2003 12:02:59

Is this what it is supposed to look like?

Yes.  How did you do that?  

Yes and thank you so much!  

What's your email address?

tammyfleming@mindspring.com

I am very impressed with the help I have gotten from this website.