Export Script Variable Naming?

[sicSRT8]

I have an export script that I use on a weekly basis. Is there a way to a variable that would name the file with the info from a field.

So when I use script it will export the data and use the first date from a specific field?

[bcooney]

Yes, you can define a $exportPath. For example to the desktop would be:

Set Variable $exportPath ; "file:" & Get (DesktopPath) & yourfield

What do you mean by "first date from a specific field?"

[sicSRT8]

I made script to pull all records from getdate -7 and do this every monday. So it gives me the 5 days from last week for every record that was entered.

I export these records to .xlsx and would like to use the date from the first record in the search. They are weekly reports and name them using the first date in the report.

Right now the only way I can run it on auto is to name as a file but then would overwrite it every time I ran the script. I also don't want to rename the file each time using the dialog option.

I noticed there is a $variable command option but not the slightest clue how to use it.

[bcooney]

Set the $export path in the script step before the Export script step. Incorporate the date that you need in the path. Then specify $exportpath in the Export script step.

[sicSRT8]

Im still lost. Are you referring to Set Variable before the export. Im confused how this will name the xlsx file the first date? Can you explain in more detail. Sorry and thank you! :B

Edited January 14, 2010 by Guest

[sicSRT8]

Actually I got it working kinda. but how can I set the actual path?

STR-FR1CommonFactory Database2010 Records

[bcooney]

If you go to the first record before you set the export path to the $var, then you can include the date in the path. Remember, export path's are FM paths, not OS paths.

[sicSRT8]

Now im really confused. I though i was getting it but obviously not.

Attached is an image of the the script.

[bcooney]

You need to Go to Record first right after your sort.

You have FM10 advanced. Step thru the script, then you'll see what $export is set to, or use a Show Dialog for testing.

And...you need to add the extension for the export file type to the filename. You'll also need to convert the date to a string so that you end up with a filename that's acceptable, like 2010_1_14.txt. What filename do you require?

[sicSRT8]

Well I have it this far. I am able to pull the records but now its saving as $exportpath.xlsx which means I have the actual path wrong where I want it to export and or $exportpath is wrong.

Im still cloudy on how to set it to a specific folder on our network.

you were saying I had to set the date string which I guess I dont care how it reads just as long as its the full date.

[bcooney]

Set Variable [$exportpath = "file:" & Get (DesktopPath) & fieldDate & ".xls" ]

Export Records [ $exportpath]

I'll have time for a demo tomorrow.

If anyone has a demo or link, please post.

[sicSRT8]

I guess my issue is I dont understand where I tell the script where to place the export file.

[bcooney]

Hello again. If you look at the Specify Output File dialog, you'll see examples of the path formats that it will accept.

We're going for the $Variable. However, we need to set the $var to a Network path which must look like:

 filewin://computerName/shareName/directoryName/filename.ext 

So, given the path example that you posted above...



If you want the file name to contain a date that is in a field "Datefield", and if the value is 1/10/2010 in that field and you want it to look like 01_10_2010.xls when we're done:

Set Variable [$filename; Right ('00' & Month (Datefield), 2) & "_" &  Right ('00' & Day (Datefield), 2) & "_" & Year (datefield) & ".xls"]

Set Variable [$exportpath ; "filewin://STR-FR1/Common/Factory Database/2010 Record/" & $filename ]

Save Records as Excel ["$exportpath" ; Records being Browsed]

[sicSRT8]

OHHHH now I see what your saying. I have a few questions does it matter which one of these I use for the path or /

Does it matter which one you use "save records as excel" or "export records"

I am getting an two different errors. When I use save records as excel it says script has canceled and when I use export it says it cannot save as 2010 to drive?

[bcooney]

Yes, it does matter "/" or "". The first is acceptable as a FM path, the second is an OS path and is not accepted by the Export or Import script steps.

Save as Excel seems to give options specific to a workbook. Use .tab files and open in Excel. Tab files do not have row limits (which were 60K in older versions of Excel).

OK. To troubleshoot, why not IMPORT a file from the shared drive into a container as a reference. Then create a calc = GetasText(container). You'll see the path to the shared volume. Use that in your Set Var $export.

What is your filename? Do you have that working?

[bruceR]

I made script to pull all records from getdate -7 and do this every monday. So it gives me the 5 days from last week for every record that was entered.

I export these records to .xlsx and would like to use the date from the first record in the search. They are weekly reports and name them using the first date in the report.

Right now the only way I can run it on auto is to name as a file but then would overwrite it every time I ran the script. I also don't want to rename the file each time using the dialog option.

I noticed there is a $variable command option but not the slightest clue how to use it.

On the contrary, the usual place to look for such clues is FileMaker Help and this subject is covered explicitly.

[sicSRT8]

Yes the name comes up exactly how I wanted. However it refuses to save to drive?

[bcooney]

What is the exact error message? Can you save any file to the target directory? This is Windows, right?

Show me the script.

Edited January 15, 2010 by Guest

[sicSRT8]

Error Message and latest Script... Yes windows. I cant get it to save anywhere.

Btw You rock! Thank you for helping me...

Edited January 15, 2010 by Guest

[bcooney]

You cannot use . in the filename! As you'll see in my example, I've used underlines.

[sicSRT8]

Even with the underline I still get the same error. $export path cannot be saved to drive.

Curious if the drive I am trying to use is a network drive on a network computer and not local does that matter? I have the rights to the drive but this refuses to save.

[bcooney]

Let's go back a couple of posts. Insert a file from the shared volume into a container as a reference. Create a calc that equals GetasText (container) and it'll show you the path that you need for the shared volume.

[sicSRT8]

K I get the file directory and its the same as what I have in my script. I copied and pasted it in case I had something wrong and still getting the error message.

Edited January 19, 2010 by Guest

[bcooney]

Can you save to the desktop using the $filename?

[sicSRT8]

No its not saving anywhere. Is this right though? I have a feeling this is wrong for $exportpath

"filewin:" & Get (DesktopPath) & "filewin:/C:/Factory/" & $filename

[bruceR]

No its not saving anywhere. Is this right though? I have a feeling this is wrong for $exportpath

"filewin:" & Get (DesktopPath) & "filewin:/C:/Factory/" & $filename

Post a simple example file. There is no point in trying to help you until you do that. And - of course putting "filewin" twice in the path isn't going to work.

[bcooney]

He HAS posted his script. But now he's kinda back at square one.

The desktop path should be:

"filewin:" & Get (DesktopPath) & $filename

if that saves to desktop, then you just have the path to the shared volume wrong. This test is to prove that your filename is acceptable.

[sicSRT8]

Yep its the path. It saves to my desktop. Can I replace Get (Desktop Path) with the actual path?

Edited January 20, 2010 by Guest

[LaRetta]

It would look something like:

Set Variable [ $platform ; Case ( PatternCount ( Get ( SystemPlatform ) ; 1 ) ; "filemac:" ; "filewin:") ]

Set Variable [ $path ; "D:/Business/Invoice/" ]

Set Variable [ $FileName ; Value:Invoice::InvoiceID & ".pdf"

Set Variable [ $FullPath ; Value:$platform & $Path & $fileName ]

This would save the file to the BusinessInvoice folder

[sicSRT8]

LaRetta i tried your method but I wasnt even able to get that to save to desktop.

I can save to desktop with BCooneys method but for the life of me cant get it to this folder.

//Sac-dc1/Common/Factory Database/2010 Records/CA/Weekly Reports/01 Jan

[sicSRT8]

Got it finally! Thanks everyone and BC for all your help... I maybe slow at this but Ill get it eventually.

Also the "." also works for me in the $Filename which is how I actually save dated files.

Edited January 20, 2010 by Guest

[bcooney]

So, what did you change that made it work?

[sicSRT8]

To be honest im not sure. The code you posted is what I am using except for the "." as the seperator for the date and instead of save as excel I am using the export records so I can pull a select number of fields in a specific order.

I am using

[$ExportPath; Value:"file://STR-DC1/yada/yada/yada/" & $Filename]

I cleared all the Variables and redid it and finally got the dialog to save so I checked it with the shared drive and it worked. I think I was mixing the .xls and trying to pull .tab and vice versa. I made everything .xlsx and it worked. Either way your solution works as I was obviously doing something wrong. Thank you for being patient and sorry that took me so long to figure out but I did learned something from it.

Edited January 21, 2010 by Guest