Export records from a crossreferenced table in a row by row way

[cheshire-cat]

	A	B	C
1	Sep	2020	25
2	May	2022	15

Hello, please forgive me the use of wrong terminology because I am rather new to Filemaker.

I need to export a table with variable number of records crossreferenced in my current table in a row by row way, meaning I want to add some information before and after the records in every row and be able to choose the separator between the records and between the rows. To make it easier to understand I gave an example of the table above.

If I use the following function it gives me only the first row ignoring all other rows:

List(Table::A;Table::B; Table::C)

I can get individual values from the row X using the following function:

GetValue ( List(Table::A) ;X)

So I can get the records from the row X one by one using this function, but I don't know If there is a way to get the whole row X as a list.

 

Next, I am considering making a loop based on the above expression going through all the rows exporting the following tab separated file:

 

Date (TAB) Sep (TAB) 2020 (TAB) 25 (TAB) End

Date (TAB) Sep (TAB) 2020 (TAB) 25 (TAB) End

 

There are more (variable number!) rows in the table and the expressions Date and End are more complex combinations of other records but I made it simple for the purpose of the question.

Is there an easier way to do that than using a loop? If no, how would the loop look like?

Any help would be much appreciated. Many thanks in advance!

 

[comment]

4 hours ago, cheshire-cat said:

Is there an easier way to do that than using a loop?

I can think of two, actually:

1. Use the ExecuteSQL() function to generate the contents of your export file. In your example, that could be something like:

ExecuteSQL ( "SELECT 'Date', Monthfield, Yearfield, Dayfield, 'End'
FROM YourTable" ; Char(9) ; ""  )

Then use the TextEncode() function to encode the result into a file and place it in a global container field. Finally, export the container field's contents.

However, this will export the entire table, ignoring your current found set. If that's not what you want, you would need to replicate your find within the SQL statement - which may be not be easy or even possible.
 

2. My preference would be to export the records as XML and use a custom XSLT stylesheet to transform the result into the format you want. This has the advantage of not requiring any additions to your file other that the exporting script. The XSLT stylesheet could be simply:

<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:fmp="http://www.filemaker.com/fmpxmlresult">
<xsl:output method="text" encoding="UTF-8"/>

<xsl:template match="/fmp:FMPXMLRESULT">
	<xsl:for-each select="fmp:RESULTSET/fmp:ROW">
		<xsl:text>Date&#9;</xsl:text>
		<xsl:for-each select="fmp:COL">
			<xsl:value-of select="fmp:DATA"/>
			<xsl:text>&#9;</xsl:text>
		</xsl:for-each>
		<xsl:text>End&#10;</xsl:text>
	</xsl:for-each>
</xsl:template>

</xsl:stylesheet>

You just need to make sure the fields are exported in the order you want them.

 

Edited August 14, 2020 by comment

[cheshire-cat]

1 hour ago, comment said:

I can think of two, actually:

1. Use the ExecuteSQL() function to generate the contents of your export file. In your example, that could be something like:

ExecuteSQL ( "SELECT 'Date', Monthfield, Yearfield, Dayfield, 'End'
FROM YourTable" ; Char(9) ; ""  )

Then use the TextEncode() function to encode the result into a file and place it in a global container field. Finally, export the container field's contents.

However, this will export the entire table, ignoring your current found set. If that's not what you want, you would need to replicate your find within the SQL statement - which may be not be easy or even possible.
 

2. My preference would be to export the records as XML and use a custom XSLT stylesheet to transform the result into the format you want. This has the advantage of not requiring any additions to your file other that the exporting script. The XSLT stylesheet could be simply:

<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:fmp="http://www.filemaker.com/fmpxmlresult">
<xsl:output method="text" encoding="UTF-8"/>

<xsl:template match="/fmp:FMPXMLRESULT">
	<xsl:for-each select="fmp:RESULTSET/fmp:ROW">
		<xsl:text>Date&#9;</xsl:text>
		<xsl:for-each select="fmp:COL">
			<xsl:value-of select="fmp:DATA"/>
			<xsl:text>&#9;</xsl:text>
		</xsl:for-each>
		<xsl:text>End&#10;</xsl:text>
	</xsl:for-each>
</xsl:template>

</xsl:stylesheet>

You just need to make sure the fields are exported in the order you want them.

 

Thanks! Both are very elegant solutions. Concerning the second one I am looking for a Filemaker-only solution that directly exports the file to the assigned location on the server, so I really want to avoid additional converting steps.

I never used the ExecuteSQL function before, so I need to try it and see whether it exports corresponding records and not the whole table as you describe.

I am afraid I will end up doing the loop, but I have no idea about Filemaker syntax unlike R or Python which are quite intuitive.

[comment]

34 minutes ago, cheshire-cat said:

Concerning the second one I am looking for a Filemaker-only solution that directly exports the file to the assigned location on the server

It is a Filemaker-only solution. Filemaker has a built-in XSLT processor that allows you to perform an XSL transformation during the export. There are no "additional converting steps".

 

41 minutes ago, cheshire-cat said:

I never used the ExecuteSQL function before, so I need to try it and see whether it exports corresponding records and not the whole table as you describe.

ExecuteSQL() returns data from all records in the table, unless qualified by a WHERE clause. This operates independently from Filemaker's native find mechanism.

 

 

49 minutes ago, cheshire-cat said:

I have no idea about Filemaker syntax unlike R or Python which are quite intuitive.

I guess it's a matter of perspective: if you had started with Filemaker, you would not find the other syntaxes intuitive.

 

[cheshire-cat]

48 minutes ago, comment said:

It is a Filemaker-only solution. Filemaker has a built-in XSLT processor that allows you to perform an XSL transformation during the export. There are no "additional converting steps".

 

Ok, thanks for the explanation, it seems to be a very powerful export tool! I am not acquainted with the XSL syntax, so it would be hard to modify it if I need to, but I might give it a try.  Can I just export xml using

Export Records [No dialog, "PATH", "FMPXMLRESULT"]?

[comment]

18 minutes ago, cheshire-cat said:

it seems to be a very powerful export tool!

It certainly is - and sadly, also one of the most under-utilized. 

 

19 minutes ago, cheshire-cat said:

I am not acquainted with the XSL syntax,

XSLT can be complex - but the subset required to handle tabular data produced by a database is rather basic;  you should be able to handle most tasks after spending an hour or so with a tutorial.

 

23 minutes ago, cheshire-cat said:

Can I just export xml

You can. But will you do with it?

 

[cheshire-cat]

2 minutes ago, comment said:

You can. But will you do with it?

I meant to export XML with XSTL converting option you suggested?

[comment]

Oh, I see. But then your script step would look something like:

Export Records [ File Name: “$filePath”; Grammar: "FMPXMLRESULT"; XSL (from file): "$xsltPath"; Create folders:No; Character Set: “Unicode (UTF-8)”; Field Order: YourTable::A YourTable::B YourTable::C ][ No dialog ]

Where $filePath and $xsltPath are variables holding the path to the target file and the XSLT file, respectively. You can also hard-code the paths into the Export Records script step.