Help with creating the script I need.....

[philipcaplan]

Please can I throw myself on the mercy of this wonderful forum again....

I need to script the following steps, but I struggle to create the correct script.

So could some kind soul give me the script that I need....

(There are approx 2000 records, and each has a value in a Field called "Search Key").
(I could if needed make a textfile or similar containing all the Search Key values)

I want to : (1) search for the first value in Search Key (for example: ABC_101);
and then (2) Sort according to the value of a Field called "Quantity";
and then (3) Export the contents of a Field called "Output Final" for all Found Records and save it as UTF-8/tab-delimited in a File called by the name of the Field "Search Key" in a  Folder called "OUTPUTS" in my "downloads" folder;
and then (4) repeat for the next value of Search Key, until all are done.

[Ocean West]

Hopefully this will work....

in the search i added "==" so that it searches on exact terms. 

Search.fmp12

[philipcaplan]

Thanks Ocean West.

I have copied-and-pasted your code and changed what I could see needed changing. (I couldn't see how to copy the amended Script's text, so I attach a screenshot of it).

I created a folder called OUTPUT on my desktop, which is where I think the file is supposed to be written.

The script Runs (very quickly!!) with no error messages, and gets to the end where it speaks "Finished ha ha ha", but there is no file inside the OUTPUT folder.

Can you help me with this???

 

[Ocean West]

Since you are on FM 16 there isn't an option to 'create" directories - so you will have to manually make sure there is a folder called OUTPUT on the desktop otherwise it will fail.  

If you need to stay in FM16 you can use BaseElements Plugin or an AppleScript to create a the OUTPUT directory. 

Also on FM16 you don't have the UniqueValues function, a custom function may be required to obtain a unique list of values ( line 7 is commented out ) 

 

Calculation for lIne 7:

ExecuteSQL ( "SELECT Distinct SearchKey from PVC_Banners" ; "" ; "" )

 

[philipcaplan]

Thanks for your reply. I did already create the folder called "OUTPUT" on the desktop, so that shouldn't have been part of the problem.

As you say, I am on FM16, which cannaot be changed.

Can the script be altered to work in FM16 without using the unavailable "UniqueValues" function???

What does that function do?  Why was Line 7 needed?

 

 

[Ocean West]

you said you have 2000 records of which there may be only 10 unique SearchKey items we need to reduce that list of 2000 down to that list so when the script is looping it only has to iterate (search) for the 10 items producing the 10 exports.

by using the ExecuteSQL function you can derive a return list of values.

 

[philipcaplan]

2000 records, but maybe 100 unique SearchKey values. Some of them will have only a few records, some of them 30 or more.

I tried copying the ExecuteSQL from your reply, but it doesn't seem to work. So should I try typing it in by hand as a replacement Line 7??

[Ocean West]

The field name and the table name must match exactly  -  since it's a string if you change the table name or field name it will fail.

 

Search.fmp12

[philipcaplan]

I am still getting no file written into OUTPUT folder.

Shouldn't "Set Error Capture [On]" cause an error message if a variable isn't set or a Search fails?

Also, I have tried changing the new Line 9 as attached, hoping that would set $list to LH_101 (which is definitely one of the values in the field) in order to see if that worked, but it still didn't work.

=================================================================

=============================================

[philipcaplan]

Is it worth adding a line (for debugging purposes) which displays the value of $list to help debug what is going on??

If yes, what would be and where could it go??

And perhaps the same for $file

[Ocean West]

What is the value of LH_101? is it a return separated list such as 

dog¶cat¶mouse¶horse¶fish

[philipcaplan]

No. It's a text file of about 26000 characters. It is HTML and other text.

[philipcaplan]

Wait -- the "LH_101" I have just answered about (26K chars) is the content of one of the text-files which are to be saved in (exported to) "OUTPUT", and named "LH_101.tab" or "LH_101.txt" (it doesn't matter which).

Or are you are asking about one of the 100 or so possible values of the field "SearchKey". They could be LH_101, LH_102, CS_101, CS_204, etc. But there is no return-separated list.

If it helps I could make (by hand) such a text-file containing a return-separated list of all the possible values of "SearchKey", and that file could be called "value-list.txt".

Does that help?

[Steve Martino]

7 hours ago, Ocean West said:

...Also on FM16 you don't have the UniqueValues function, a custom function may be required to obtain a unique list of values ( line 7 is commented out )...

 

Didn’t UniqueValues originate in 16?

 

https://fmhelp.filemaker.com/help/17/fmp/en/index.html#page/FMP_Help%2Funiquevalues.html

 

[philipcaplan]

Hold it!!  First the good news, I realized (by turning "dialog on" in the Export script step) that the Field Export Order that was to be exported was missing!! So, I edited the script to add it, and the Export worked.

Now the bad news: the file that is created is not of use, because it's content is different from the file that would be created by doing a "manual" Export.

A "manual" Export makes a file with all the contents of the Field included, one after the other, so the file contains all the Field contents for every Record in the Found set.

The "scripted" Export makes a file containing Field contents for a different SearchKey!!!!

Help, please.

[Ocean West]

Please provide some sample data with matching field names and table names.

 

 

[philipcaplan]

Here's the whole database. I got nuthing to hide!!!

Reliaprint1.fmp12.zip

[Ocean West]

Reworked the script for you  

 

Script.pdfReliaprint1.fmp12.zip

[philipcaplan]

Thanks Ocean West. I tried it and it looks like it works perfectly! Yay!

I will do some full testing, but if the first ".txt" file I tried works perfectly when uploaded to my website, I am hopeful they all will!!

But before I can do a full test, each file needs to have 2 search-and-replaces done, and preferably the extension ".txt" removed. I am using BBEdit to handle my text files, and I found that I can use its "Scripting" capabilities to create and run an Applescript. By "recording" my actions I got the following Applescript:

tell application "BBEdit"
    activate
    open find window
    replace "§§§" using "\\r" searching in text 1 of text document "BC_102.tab" options {search mode:grep, starting at top:true}
    replace "\\x{0B}" using "" searching in text 1 of text document "BC_102.tab" options {search mode:grep, starting at top:true}
    save text document 1
end tell

The above script works for one [selected] file, but I need it to: (1) perform as above on EVERY file in a selected folder;
(2) "save as" with file-extension removed, then delete the original file.

So, are you also able to help me in doing this with Applescript??
I thought I'd ask before looking anywhere else, just in case!!  Don't worry if it's not your thing! You've been very helpful to me, and I am most grateful.

I said ".txt" file but of course I meant ".tab" file.

I think FMPro might always Export UTF-8 files with a ".tab" extension, whatever you tell it to do......

[comment]

Caveat: I haven't followed this thread and I have only now looked at your file.

I recommend you explore the possibility to export records as XML and use a custom XSLT stylesheet to transform the export to HTML. That should put all the required logic in the stylesheet and save you the hassle of producing snippets of HTML in calculation fields as well as post-processing the exported file. 

I also suggest you re-examine your structure, as having numbered fields is a strong indication you should use a related table.

[Ocean West]

I am not certain why you'd need to do a search and replace externally why not have the data formatted out as needed?

I agree with @comment that there could be much more easier way to structure the data reduce the number of fields.

Alternatively build a fully formed HTML Template and put in place holders @@FIELDNAME@@  then output a calculation that smartly replaces the placeholders with real data.

 

Even look at using BaseElements Plugin to create files encoded as needed and write out lines to the file as you are looping thru records. vs using the Export records option.

[philipcaplan]

To answer: "why not have the data formatted out as needed?"...... it's because I find that FMPro's Export produces results that don't behave well when used as HTML, and those 2 search-and-replaces cleans everything up nicely.

As regards the structure, the numbered fields are needed because I need the Exported textfiles to match my website's requirements for the files it uses. Each time I need to update the contents of my site (a PHP static ecommerce site) I do an Export from the FMPro database [in which I keep track of price changes and new/changed products] and then  FTP the complete folder to my webserver, rename it, and all the pages of my website get updated immediately with all my changed details, prices, etc, because each page is set to "get" a textfile named the same as the product code.

A more automated approach to generating the website would not work, as the products I sell are not under my control (it is a service of printing leaflets, books, etc for which I have to use a different supplier for each one, and so prices/sizes/quantities/specifications/speed-of-delivery vary wildly from product to product). Therefore my home-grown FMPro database is just good enough to do the job!!

I really appreciate your help. Both you and "comment". Many thanks,  Philip Caplan

[comment]

6 minutes ago, philipcaplan said:

I find that FMPro's Export produces results that don't behave well when used as HTML

That is correct. If you export as tab-delimited, you should not expect it to behave well when (mis)used as HTML. This is precisely why I recommended you export as XML and use XSLT to transform it to HTML.

 

8 minutes ago, philipcaplan said:

As regards the structure, the numbered fields are needed because I need the Exported textfiles to match my website's requirements for the files it uses.

IMHO, this is not the right approach. Your file should be structured in the best way possible to accommodate the data you track.  Once you have the optimal structure, ask how to export it to fit the target application's requirements. Subordinating your solution structure to the requirements of the web site will result in having a suboptimal solution.