Export field contents into external file

[Toni]

Hello

  I am trying to automatically create subfolders in Windows which should be named year_month, like 2016_11, 2016_12 and so on. This will be done once the month has passed, probably around 10 to 15 of next month. For example, on the 15 of November 2016 a folder named 2016_10 has to be created.

  I have managed to create a calculated field which content is exactly what I need. Below is the calculated field:

"md"&" "&( If ( Middle( Get ( CurrentDate );4;2)="01" ; (Middle ( Get ( CurrentDate );7;4)-1)&"_"&"12" ; Middle ( Get ( CurrentDate );7;4)&"_"&Right(0&(Middle( Get ( CurrentDate );4;2)-1);2)))

  Then, a script, exports this field content into a file "date.cmd" in the folder I need to and is set to automatically open it. So, what I expect is command line file which simply has as content "md 2016_10" and when executed it should create the folder.

  Thing is, it doesn't. When I open cmd and execute from there the created file, I see that there is a black square character preceding "md", thus cmd responds with " '■m' is not recognized as an internal or external command". I suppose that is some character added in export, during translation from field content of FM to the text file of windows.

  Any idea how to solve this?

[Wim Decorte]

Export the cmd file like you have but don't set it to auto-open.  Use the Send Event script step to run it.  Use something like this for the command in Send Event:

cmd /c c:\path\to\your.cmd

 

 

 

[Toni]

Thanks Wim for your reply

  I tried it and it won't create the folder.

  See, this character is somehow inserted in the file. I can not see it when I open it with notepad, but when i try to execute it within cmd window it shows up. Even if try to execute the date.cmd file by double clicking it won't create the folder. Funny thing is that if I manually copy the content of the field from FM and paste it in the cmd window it will execute normally.

  This character is inserted during the export. I have defined the field, in the language option as unicode.

[Wim Decorte]

What format do you use for the 'export field content'?  That's probably a unicode character that gets in the way.  See if it makes a difference if you export from a one field, one record table and use the normal export instead of 'export field content'

You can make your life easier by using something like the free BaseElements plugin to do all of this...

[comment]

I suspect the problem is with the file encoding; when you export the contents of a text field, the resulting file is UTF-16 encoded. Apparently your OS does not know how to handle that.

Is it really necessary to export the command to an external file? I am not a Window user, but from what I have read you should be able to execute the command directly from the Send Event script step.

 

BTW, I would suggest you change your calculation of the folder's name to:

Let (
d = Get ( CurrentDate ) - Day ( Get ( CurrentDate ) )
;
Year ( d ) & SerialIncrement ( "_00" ; Month ( d ) )
)

That's not only simpler but - more importantly - does not depend on the date format being used by the file.

 

 

[Toni]

Thank you both, great contribution

I chose to export it into a cmd or bat file because obviously I am not that good to figure it out by the send event :). I will try some more and let you know, it's late around here and will try it tomorrow

[comment]

Just now, Toni said:

I am not that good to figure it out by the send event

This is why we have Google.

[Toni]

I think I am getting there.

  For the calculation of folder name, I used the formula suggested by @comment. Great tip

  Then searching and testing, after some time of both, managed to come with a script with send event in it which creates the folder from there. Script steps:

  set variable [$path; value:"D:\Docss\FileMaker\GestProduzione1\\"]

  # here "Table" is the table of FM and "Make_Folder_prev_month" is the name of the calculated field

  send event ["aevt"; "odoc"; "cmd.exe /C MKDIR " & Quote($path&Table::Make_Folder_prev_month)]

    One thing which I don't understand (and I hate it) is why I need to add a backslash at the end of the value of $path variable. Is the extra backslash to indicate end of the string? Shouldn't be enough a quotation mark?

  Thank you both for great input. Very happy with it.

[comment]

I don't see why you need the calculation field: you can calculate everything you need within the script.

[Wim Decorte]

Agreed.  And something is off somewhere with that last back-slash.  There should be no need for it.

As an aside, in case you need to run more than one command, Windows cmd allows you to string commands together with the & and && operators so you can make fairly complex 'strings' to send to cmd without the need to create a batch file.

[Toni]

On 11/12/2016 at 11:59 PM, comment said:

I don't see why you need the calculation field: you can calculate everything you need within the script.

Well, I am not gonna revisit this script again in a long time and probably will forget the logic behind it . Having a field which will show the result of the calculation will help me remind about it.

[webko]

That's why the Script editor allows you to have comments in it...