Subscribe Bookmark RSS Feed

JSL: How can I get IDs of selected lines and export them in a text file

claude_bellavan

Community Trekker

Joined:

Nov 5, 2014

Hello,


I would like to program a script that would create a small text file. This file is actually a snapshot link used by FileMaker to open a set of saved records. This is a XML file, but with an file extension recognized by FileMaker ( ".fmpsl"):

Snapshot link is very simple file. A first text string is fixed, it is followed by the database's ID list (stored in a column of the JMP table) and a third part consisting of a fix text string as the first.:

<? Xml version = "1.0" encoding = "UTF-8"?>

<SLMF> <UIState> <UniversalPathList> fmnet: /99.129.6.76/my_database.fmp12 </ UniversalPathList> <Rows type = "nativeIDList" rowCount = "2" baseTableId = "142"> <! [CDATA [!

5935

6597

]]> </ Rows> <Layout id = "48"> </ Layout> <View type = "form"> </ View> <SelectedRow type = "nativeID" id = "5935"> </ SelectedRow> <StatusToolbar visible = "true"> </ StatusToolbar> <mode value = "browseMode"> </ mode> <SortList Maintain = "True" value = "false"> </ SortList> </ UIState> </ SLMF>

The IDs (the only variable part) in this example are the 5935 and 6597.

I would also like my action triggers the opening of the Snapshot Link file created. So I could access my database related records with a simple clic in JMP.

Is this project is achievable with JSL? The solution could be cross-platform or run only on Mac.

I'm new to JSL.

Thank you for your help.

CB

10 REPLIES
ian_jmp

Staff

Joined:

Jun 23, 2011

Something like this, perhaps?

NamesDefaultToHere(1);

// Example table with selected rows

dt = Open("$SAMPLE_DATA/Big Class.jmp");

dt << selectRandomly(0.5);

rs = dt << getSelectedRows;

// Define conmtents of .fmpsl file

fileHeader =

"<? Xml version = \!"1.0\!" encoding = \!"UTF-8\!"?>

<SLMF> <UIState> <UniversalPathList> fmnet: /99.129.6.76/my_database.fmp12 </ UniversalPathList> <Rows type = \!"nativeIDList\!" rowCount = \!"2\!" baseTableId = \!"142\!"> <! [CDATA [!\!n";

fileRows = "";

For(r=1, r <=NRow(rs), r++,

fileRows = fileRows || Char(rs[r]) || "\!n";

);

fileFooter =

"]]> </ Rows> <Layout id = \!"48\!"> </ Layout> <View type = \!"form\!"> </ View> <SelectedRow type = \!"nativeID\!" id = \!"5935\!"> </ SelectedRow> <StatusToolbar visible = \!"true\!"> </ StatusToolbar> <mode value = \!"browseMode\!"> </ mode> <SortList Maintain = \!"True\!" value = \!"false\!"> </ SortList> </ UIState> </ SLMF>";

// Write the file

SaveTextFile("$DESKTOP/myFile.fmpsl", fileHeader || fileRows || fileFooter);


Not sure exactly what you need for the 'action' part of your question. Perhaps you can please clarify?

claude_bellavan

Community Trekker

Joined:

Nov 5, 2014

Thanks a lot Ian.

This is a very good starting point !

The action is simply: tell the OS to open that snapshot («.fmpsl») FileMaker file.

Claude

ian_jmp

Staff

Joined:

Jun 23, 2011

OK. So check out the 'RunProgram()' command (via 'Help > Scripting Index').

claude_bellavan

Community Trekker

Joined:

Nov 5, 2014

2 more questions:

1- Is that «Run Program» command is only working with an executable file (.exe on Windows or .app on Mac OS)? After export JMP data to an Excel file, how the JSL script can tell the OS to automatically open that file (not only Excel)? Maybe a second parameter is needed?

2- The middle section of your script write the selected row numbers to the file. How can I write a list with the content of a normal data column (my database internal ID) for selected lines?

As you may see, my understanding of JSL is very basic (and my English too).

Thanks again!

CB

ian_jmp

Staff

Joined:

Jun 23, 2011

The reference I gave for 'RunProgram()' in the Scripting Index has four examples, and if you inspect the code and run each, you should build up a better picture. There's also a 'Topic Help' button.

To get get the value of a cell, rather than the row it resides in, you can use the syntax 'Column(dt, myCol)[myRow]. So if you want to write out the heights in the selected rows, you would need:

Char(Column(dt, "height")[rs[r]])


instead of:


Char(rs[r])

claude_bellavan

Community Trekker

Joined:

Nov 5, 2014

Thank you Ian.


I managed to adapt the script. The file is created and it works !!


But I still do not understand how to tell the operating system to open the text file. I have carefully studied the scripts of the index and help. I've been running these 4 examples, but none seems to fit my needs.

As I wrote earlier, my situation is like this:

Create a script that would export in a file an excerpt from the JMP table and who would tell the operating system to open this file (by Excel, not by JMP).

CB

ian_jmp

Staff

Joined:

Jun 23, 2011

At the top of the thread you mention FileMaker, which although I've never used it, I assume is: http://www.filemaker.com). But immediately above, you mention Excel. It will make a difference to the details of a feasible approach I'm sure.

As the documentation states 'RunProgram()' controls 'an external program using stdin and stdout'. I assume that, having written out the xml file containing the data, the jsl would also need to write out a second file that tells FileMaker or Excel to consume this data, and this second file would be invoked via 'Run Program()'. But it's probably also worth saying that (depending on whether it's FileMaker or Excel you need), there may be a better way altogether.

claude_bellavan

Community Trekker

Joined:

Nov 5, 2014

Well. I was giving Excel as a similar situation where JMP doesn't need to interact with the application. Just tells the operating system to open the created file with the default application.

Anyway, I've found a workable solution this morning. I wrote a MacOS script (in fact, it's an Automator action) which monitors a folder and open any file that appears inside. There is a delay (0,5 - 2 seconds), but it works.

In closing, let me thank you warmly again for your assistance. That will help me greatly!!

CB

ian_jmp

Staff

Joined:

Jun 23, 2011

Happy to help, Claude. Glad you found a solution that worked for you.