Looping script in FM12

[philipcaplan]

Hi.  I hope this is in the correct forum:

I would be very grateful if anybody can give me a script I can use to achieve the following in FMPro 12 on a Mac.

-- I have a table with fields "serial_number" and "export"

-- I want to export a separate tab-delimited file containing just the content of the field "export" for every "serial_number" in the table, named "serial_number.php", all going into a folder called "EXPORTS"

-- The serial numbers are all unique to each record in the table, starting from 1 up to an unknown value (new records can be added at any time) but some numbers are missing from the sequence where records have been deleted and so we need to skip those and not create a file.

If you can save me many hours of novice headscratching by giving me a script I can copy-and-paste into a script in "ManageScripts..." I will be most grateful. 

Philip Caplan

[rwoods]

Hi Philip

Your requirement seems very simple, so excuse me if I have missed something. Enclosed are some steps that would do the job, but there would be other ways to achieve the same thing.

The first line asks the user to select their output directory, they would choose the EXPORTS directory, wherever that is held. You don't need to do anything special to skip the deleted records. Since they are not there, they will not be exported!

Get Directory [ Allow Folder Creation; $path; Dialog Title: "Choose a directory"; Default Location: Get ( DesktopPath ) ]

Set Variable [ $path; Value:$path & "serial_number.php" ]

Show All Records

Sort Records [ Keep records in sorted order; Specified Sort Order: Export::serialNumber; ascending ] [ Restore; No dialog ]

Export Records [ File Name: “$path”; Character Set: “Macintosh”; Field Order: Export::export ] [ No dialog ] 

I tested this on a simple file (enclosed) and I got the file requested.

I would note that the file is not a valid PHP file, you would need to do some work to add HTML headers, PHP tags and all the rest of it depending on what you actually wanted to use the data for.

Export.fmp12

[philipcaplan]

Thank you rwoods.

I tried the FMP file you sent but the first line:

Get Directory [ Allow Folder Creation; $path; Dialog Title: "Choose a directory"; Default Location: Get ( DesktopPath ) ]

came out as "<unknown>" and the script wouldn't complete when I tried to run it under it FMPro 12.0v5.  : (

[Wim Decorte]

That's because the "Get Directory" script step was added in FM14.  Just remove that step, and add the full path to where you want the export to go to the Set Variable $path step.

[philipcaplan]

Thank you Wim. I thought it was most likely to be a command not in FMPro12!!

I have now got the script to work, but now I realize that I had over-simplified my description!

I need some text (the PHP headers etc -- will be the same for every record) to be added at the start of each line of the output, with the contents of my "export" field following after it on the same line, then some more text of PHP footer, all in one line ending with the newline.

So, perhaps it needs to be a "looped" script -- and not a simple "Export Records[ ]"

More help will be much appreciated!

And please remember I'm very much a novice!!

[comment]

40 minutes ago, philipcaplan said:

I need some text (the PHP headers etc -- will be the same for every record) to be added at the start of each line of the output, with the contents of my "export" field following after it on the same line, then some more text of PHP footer, all in one line ending with the newline.

We need a better description of what the exported file should look like. If you're only exporting one field, and you want each row to have a constant prefix and suffix, you could define a calculation field along the lines of:

"prefix text " & ExportedField & " suffix text"

and export this field instead of the ExportedField field. There are more elegant solutions than that, but it will do.

 

Edited February 21, 2017 by comment

[philipcaplan]

Perhaps we should start from the beginning!

My original description was:

-- I have a table with fields "serial_number" and "export"

-- I want to export a separate tab-delimited file containing just the content of the field "export" for every "serial_number" in the table, named "serial_number.php", all going into a folder called "EXPORTS"

-- The serial numbers are all unique to each record in the table, starting from 1 up to an unknown value (new records can be added at any time) but some numbers are missing from the sequence where records have been deleted and so we need to skip those and not create a file.

rwoods replied with a script which I now realize DID NOT fit this description. I needed "a separate file...for every serial_number in the table" he gave me a script which dumped everything into one file called 'serial_number'

Is my description above sufficient?  I need the script to put into a folder hard-wired into the script, one file (containing contents of field 'export) named 'serial_number'.php [where 'serial_number' is the content of the field 'serial number'] FOR EVERY record in the table.

[rwoods]

Ah yes, that only originated in FileMaker Pro 14, sorry! You could just change it to the following, and in line 1, edit the path to match the location of your folder. My code below assumes it is on the desktop.

All that has changed is the old line 1 is deleted, and the new line 1 is modified. The other three lines are unchanged.

 

Set Variable [ $path; Get ( DesktopPath) & "EXPORTS/serial_number.php" ]

Show All Records

Sort Records [ Keep records in sorted order; Specified Sort Order: Export::serialNumber; ascending ] [ Restore; No dialog ]

Export Records [ File Name: “$path”; Character Set: “Macintosh”; Field Order: Export::export ] [ No dialog ]

 

[comment]

If you want a separate file for each record, you will have to loop among the found records - and you will have to isolate each record in turn so that it's the only record in the found set when it's exported.  So your script will look something like:

Go to Layout [ “YourTable” ]
# FIND THE RECORDS TO EXPORT
Show All Records
Go to Record/Request/Page [ First ]
Loop
  # ISOLATE CURRENT RECORD
  New Window [  ]
  Show All Records
  Omit Record
  Show Omitted Only
  # EXPORT CURRENT RECORD
  Set Variable [ $path; Value:"/YourHardDisk/path/to/the/Export/" & YourTable::serial_number & ".php" ]
  Export Records [ File Name: “$path”; Field Order: YourTable::export ] [ No dialog ]
  Close Window [ Current Window ]
  Go to Record/Request/Page [ Next; Exit after last ]
End Loop 

For simplicity, we are assuming that no one will be creating new records in this table while the script is running.

 

Edited February 21, 2017 by comment

[rwoods]

Sorry, when I replied I hadn't noticed all the interim replies! Wim beat me to it!

[philipcaplan]

Thanks to comment. I've got it almost working!!  But it reports it cannot create each of the files (1.php, etc). I suspect I've got my filepath (which I obtained by dragging the EXPORTS folder into the Terminal window in MacOS10.10) misdescribed or in the wrong place or something.

I'm attaching a PDF with screenshots of (1) my script (2) the "Set Variable" window (3) the "Specify Output" window.

I'd be grateful if you can see what I've got wrong.

3screenshots.pdf

[comment]

A Filemaker path will begin with the drive's name - see: http://www.filemaker.com/help/15/fmp/en/#page/FMP_Help%2Fcreating-file-paths.html%23

If the target folder is located within your Documents folder, then you can define the $path variable as =

Get( DocumentsPath ) & "WORK/oea/EXPORTS/" & housewinner::serial_number & ".php"

and let Filemaker worry about the rest.

--
P.S. Do note the edit I have made to my previous post. It makes no difference to your current script, but if you ever decide to start by performing a find instead of exporting all records in the table, it will.

[philipcaplan]

Thank you 'comment' -- your "Get(DocumentsPath)" did the job perfectly.

All files happily created. Only one small problem: they're in "Western (ISO Latin)" format, so all my £ symbols get scrunched into diamond-?'s.  I can deal with it by adding Substitute( ) in my FMPro fields to change them to '&pound;'

But if you know how I can easily change the format of my output files to "UTF-8" that would be even better.

Thanks once again.

[comment]

In the Export Record [] script step, click `Specify export order` and select "Unicode (UTF-8)" from the `Output file character set` popup menu.

 

 

Edited February 21, 2017 by comment

[philipcaplan]

Thank you, thank you, thank you, to comment.  I now have my output in Unicode (UTF-8). <happy smile>

So that's good.  But I still have a problem!!  <sad smile>

The final working script **sometimes** runs OK to the end, but other times (about 50%) it causes FMPro 12.0.5 to quit during the process, leaving some of the files created but others not.

Do you have any thoughts on the cause and possible cure for this quitting??

[comment]

19 minutes ago, philipcaplan said:

Do you have any thoughts on the cause and possible cure for this quitting??

Not really. How many records do you have? An what happens if you insert a short pause (say 1 second) into the loop?

[philipcaplan]

Less than 100 records.  I also thought maybe a pause after each export might help.

But I've now run the script 3 times without a Quit, and it's not fatal if I have to check after each run that I got the "final dialog" saying it's all done that I put in it, as it will not be something I'll have to do very often as I am the only person who will change values in the database requiring a reupload of the files.

Anyway, it's now midnight here, so I'm going to leave this until the morning, when I've had some sleep!

Thanks once again for all your help.

[philipcaplan]

Morning has arrived, I've added a "Pause" of 0.33seconds at the end of each loop of the script, and the script has run a few times without quitting. So, job done it seems.

Once again many thanks to 'comment' for reading my question and giving me the perfect answer!!

[philipcaplan]

I think I may have found why the script that 'comment' has kindly given me works often the first time, and then on further runs my FMPro hangs and then quits. I seem to have no choice but to then restart my Mac, after which I get one or maybe two runs of the script, and then the hang/quit happens again. <sad smile, shrug shoulders>

I am running FMPro 12 on a Mac running MacOS 10.10, and FMPro 12 is (according to the FM website) only certified up to 10.9.

My problem is that there is no official upgrade path from 12 (or 13 come to that) to 14 or 15. My only option [assuming that this is the cause of my problem!!] is to pay £280 (about $350US) for a complete new copy.

Unless someone knows of any other alternative?????

[comment]

26 minutes ago, philipcaplan said:

I am running FMPro 12 on a Mac running MacOS 10.10, and FMPro 12 is (according to the FM website) only certified up to 10.9.

That is a possibility - but before you jump to conclusions, I would suggest you see if you can reproduce the problem in a brand new file. One that has never crashed before.

[philipcaplan]

Hi again comment. I will try what you suggest in the morning (it's after midnight again here in the UK!).

I assume that what you mean is that I create a completely new db with just enough fields to fit the script, then import the script and try running that to see if I can get it to hang/quit.

[comment]

I wouldn't import anything from your current file. The purpose of this exercise is to see if it's your file that causes the problem, or the system. If you use any parts of the suspected file, you can't be sure.

[philipcaplan]

I have done this (created new FMPro file with just a single field, and a newly-typed copy of the script) and on 2nd run it did the Quit <sad smile>

Near the top of the Crash report were the following lines, which as far as I can remember are identical to those from the Quits I got with the original database/original script:

Crashed Thread:        0  Dispatch queue: com.apple.main-thread

Exception Type:        EXC_BAD_ACCESS (SIGSEGV)
Exception Codes:       KERN_INVALID_ADDRESS at 0x0000000000000000

Any thoughts???

[Wim Decorte]

The next test is to try it again with a brand new file but in another account on the same machine.  Sometimes these crashes are caused by something in your account's profile.

[philipcaplan]

I have tried what Wim suggested. I did a shutdown of my Mac, and re-started as "Guest User". I ran the small test script (one field, one script) and it Quit at the beginning of its 1st run, with the Errors as listed in my post of '6 hours ago'.

So, any more thoughts, other than the fact I am running MacOS 10.10 and FMPro 12 is only supported up to MacOS 10.9!!??

[Wim Decorte]

Any plugins installed in FM (even if you don't use them in that file)?

There could be some more clues in the complete crash log but in the end it may very well be that you are running an unsupported config.

[philipcaplan]

I downloaded a Trial of FMPro15 onto a MacBook I have just bought secondhand, and copied across my database to it, and in this environment the script has just run 10 times in a row without a single crash!! <hooray>

So, I guess the MacOS version seems to have been the problem. And now I have no choice but to purchase (within 14 days) a new copy of FMPro for more than 50% of the price I paid for the whole MacBook, just so I can run this script!!!

Anyway, thanks to Wim and comment for their help.  Courteous, thoughtful and helpful, as always.

[comment]

The nitpicker in me cannot resist telling you do have a choice: downgrade your OS.

The scientist in me cannot resist telling you that your test is inconclusive. Why haven't you used the trial version on the same system? What if the real cause is a faulty memory module?

[philipcaplan]

Hi comment.  Feel free to 'nitpick' -- you're often right on the money!!

Downgrading my OS looks to me like a tricky idea. I currently do my work on a 4-year-old Mac Mini running 10.10, which I thought was OK to run FMPro 12 but now realise wasn't!!

I want to move my work to a laptop so I can be more mobile than my desktop-bound MacMini, and I realized that MacBooks have now become so expensive yet only have limited-sized SSD's instead of 500Gb or more hard disks, and a limited range of ports. So I decided to buy a 'refurbished' 3-year old MacBookPro which came with MacOS 10.11 for about £450. I'm very satisfied with it, and I think it has given me another 3 to 5 years before I need to look ahead for my next machine, by which time the MacMini would have been getting stuffed with stuff I no longer need and too far behind the curve by then.

I have always had a belief that "upgrading the OS of a machine which is vital to use every day" is a risk not worth taking, better to wait till you get a new machine and then you'll get the most-up-to-date OS with it. (I've been buying Macs since the 1980's, and that mantra has served me well through about 50 machines, back to the days when I had 10 at a time in my printing business, and dialup modems at 14.4kb/sec was the cutting-edge technology that connected me to Compuserve for about $100 a month).

However, I'd never thought of DOWNgrading an OS!!  The drawback seems to me is that a few years down the line from now I'll be **so far** behind the curve that some other vital piece of software (browsers mostly) would need a more up-to-date OS and not run properly.

So, having made the decision to buy the £495 MacBook, it looks like the decision to spend another £260 on a version of FMPro that will also last me until that machine's end-of-life is unfortunate bur unavoidable.

I'm really complaining that FILEMAKER IS TRYING TO SQUEEZE AS MUCH JUICE OUT OF ITS USERS AS IT CAN, and there's nothing much I can usefully or safely do about that.  <resigned shrug>

You ask "Why haven't you used the trial version on the same system?" and my answer is "you can't run FMPro 15 on 10.11 let alone 10.10" or have I misunderstood something?

[comment]

15 minutes ago, philipcaplan said:

You ask "Why haven't you used the trial version on the same system?" and my answer is "you can't run FMPro 15 on 10.11 let alone 10.10" or have I misunderstood something?

Where do you get this information? My understanding is that you can run it on both:
http://help.filemaker.com/app/answers/detail/a_id/4701/kw/system requirements

 

[philipcaplan]

Correct, I had forgotten that FMPro15 **was** able to run under 10.10.

The reason I had decided NOT to try it on my MacMini was for fear that it might interfere with my FMPro12 (overwrite preferences or stuff like that).

I thought it better to start with a clean download and installation on the hard disk on my new MacBook, and if it still didn't solve the Quitting problem, at least my FMpro12/MacMini setup was unchanged.

After all, it does everything I needed of it, up to the point when I needed this looping script!!!

[Wim Decorte]

21 hours ago, philipcaplan said:

The reason I had decided NOT to try it on my MacMini was for fear that it might interfere with my FMPro12 (overwrite preferences or stuff like that).

If you are worried about there are many options.  The easiest is to create a sparse bundle disk image and install FMP15 on that disk.  That keeps everything related to that app on that disk image  If you don't like iFM15 just throw away the disk image and there will be no interference with other apps preferences.

21 hours ago, philipcaplan said:

it looks like the decision to spend another £260 on a version of FMPro that will also last me until that machine's end-of-life is unfortunate bur unavoidable.

I'm really complaining that FILEMAKER IS TRYING TO SQUEEZE AS MUCH JUICE OUT OF ITS USERS AS IT CAN,

 

That's a whole other discussion.  That argument is really all about your perception of the value.  Given that FM12 was released in April of 2012 and we're now almost 5 years later.  The 260GBP translates to roughly 50GBP per year for software cost.  If the solution / data is not worth it then I would look for a development platform that is cheaper.

Spending 50GBP yearly on average for a platform that runs your business doesn't strike me as "squeezing as much juice out of its users". 

[philipcaplan]

Hi Wim.  Don't be upset with me, please!

This reminds me so much of the discussions I saw by QuarkXPress users on Compuserve forums in the late 1980's, way before the internet arrived, complaining the program cost $1000 a copy, when competing programs (PageMaker, ReadySetGo, Xerox Publisher) were nearer $350. The owners of Quark (Tim Gill, who wrote it, and Fred Ebrahimi, who was seen as the hard-headed businessman) had an answer: "if you find it too expensive, please feel free to use a different product". They knew that Quark was the only answer for proper, professional-level work, and that the other programs were "toy like" in comparison.

They made the same comments about "running your business on our product" as you have just said, and of course they (and you) were/are correct!

But I am not using FMPro to run my business!  My use of it here is for a small not-for-profit sideline, to massage and output data in a way that I could do with a spreadsheet, but which I believe is easier and more robust when done with FMPro. I am not skilled enough in database design and construction to earn money using it for that.

So, please go easy on me.

[comment]

25 minutes ago, philipcaplan said:

I am not using FMPro to run my business!  My use of it here is for a small not-for-profit sideline

That's a perfectly legitimate use case. And so is not upgrading to the latest version. But then you do want the latest version of the OS - and that's where your requirements begin to conflict.

BTW, I am curious about something: earlier you reported that inserting a pause in the script solved the problem. Was that report not true?

 

[philipcaplan]

Inserting a pause enabled me to run the script 3 times without a problem.

So I thought (hoped? wanted to think???) that the problem was solved!

But it wasn't. Further runs caused Quits most times it was run.

The script has only worked reliably since I moved it to FMPro15 under MacOS 10.11