Jump to content

Convert Portal to Field


Zonsopgang

This topic is 999 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

I have four tables (Projects, Customers, Products, and Users) and a Join table.

All four tables have relationship with Join table, configure to be a many to many relationship.

A record in layout based on Project have one Client, one Product, and multiple Users (using portal).

I need to export each record to a csv file, and need to be a one liner.

My issue is if I export based on Project table, I can not get one liner result if I have multiple Users in the portal. And also the second user (User 02) will have no information about Project, Client, and Product field, and sometimes users can be more than 5.

Project Title Customer 1 Product A Bruce
      Clark

But if I export based on Join table, it will looks like I have two records with the same name in my Project.

Project Title Customer 1 Product A Bruce
Project Title Customer 1 Product A Clark

I am thinking make a new table just for exporting purposes and if there are multiple users in my portal, it will convert it to multiple fields with sequential field name (ex: User01, User02, User03, etc.)

What is the best approach to make my csv file is one liner?

 

Thank you.

 

 

 

Link to comment
Share on other sites

I believe the best approach would be to export as XML and use a custom XSLT stylesheet to transform the result to the exact output you want. However, I am not sure I understand what exactly is the output you want. To take a simplified example, suppose you have 2 projects, one titled "Project Alpha" and the other "Project Bravo" and that "Project Alpha" is related to users "Adam" and "Anna" while "Project Bravo" is related to users "Benjamin", "Britney" and "Brad". What should the exported file look like?

I am also not sure what you want to happen if a project has more than one customer and/or product. You say they are all many-to-many relationships; if you list each table's related records on the same line, how will the recipient know where one table ends and another starts?

 

 

Edited by comment
  • Thanks 1
Link to comment
Share on other sites

  • Newbies

Thank you for mentioning XML.

Probably I gave to little information, and yes you are correct, one Project related to multiple Users, and also to multiple Products. If you have spare time, please have a look to my Filemaker.

 

After trying with XSLT, I do not think I am smart enough to learn what I need for few days.

So I tried exporting without stylesheet to get the XML.

Quote

You say they are all many-to-many relationships; if you list each table's related records on the same line, how will the recipient know where one table ends and another starts?

I think my best approach to this is make fields that contain only for separator to separate a portal from other fields. And then to achieve the one-liner csv file, I made a script to convert the XML (without XSLT) to a CSV. With separator fields, it will looks like this, and I am fine with this CSV format :

1,1,1,1,Project01,1,Customer A,product_start,1,2,Product A,Product B,product_end,user_start,1,2,4,3,Bruce Wayne,Clark Kent,Lex Luthor,Jack Napier,user_end

My next issue is to import the same format CSV with multiple lines (one project per line) back to my Filemaker. Probably if I can make my way to export XML with XSLT in the first place, I can make import as well. Can you give me some hints with XSLT?

Database.fmp12

Link to comment
Share on other sites

I am afraid I am even more confused than before. Did you just invent a format as a response to my question? Usually, the format you want to achieve as a result of the export is dictated by the target application. If you are at liberty to design your own format, then what exactly are the limitations?

Ideally, you would use one of the already existing formats designed to handle this type of data - e.g. XML or JSON. If you absolutely must use a CSV format, with one line per project and all the data from the related tables included in this line, I would suggest using a different separator (e.g. the | vertical bar character) for the related tables' fields. Using the data from your example file, the result could look something like this:

ID_PROJECT,Project_Name,ID_CUSTOMER,Customer_Name,Customer_Type,ID_PRODUCT,Product_Name,ID_USER,User_Name,User_Title
1,Project01,1,Customer A,Coorporate,1|2,Product A|Product B,1|2|3|4,Bruce Wayne|Clark Kent|Lex Luthor|Jack Napier,Supervisor|Sales|IT|IT
2,Project02,2,Customer B,Individual,2|3,Product B|Product C,5|6|7,Victor Stone|Harvey Dent|David Hyde,IT|Supervisor|Sales

This, though far from being ideal, is at least something one can work with. I would not like to be on the receiving end of a file using your format shown above.

 

3 hours ago, Zonsopgang said:

My next issue is to import the same format CSV with multiple lines (one project per line) back to my Filemaker.

Again, that's something that would best be avoided. Even using the format I suggested, you would not be able to import it directly. You would have  to import it into a temp table, then have a script parse the data and create individual records in each target table.

 

Link to comment
Share on other sites

  • Newbies

To answer your first question. I have two other one-liner databases, generated using shell script. These databases does not have repeating fields like portal in Filemaker, always have same column amount, so I have no issue to import that CSV files. For those databases, I use Filemaker only for viewing, and sometimes in workstation (Mac, Linux, or Windows) that doesnt have Filemaker, I use terminal/shell app, this is the main reason I need one-liner.

Those two databases, also the database I am currently working on it will have relationship in project name.

My goal is, to combine all these three databases, putting all together in one line (matching by project name), first and second group column will be my two first database, and the third group column will be my current database since it contain various amount of fields. That is why I ask about exporting one-liner, because my other two databases are one-liner as well. And this way, its easier for me to import it back as combined one-liner to Filemaker for viewing (on another layout or another Filemaker).
I hope this will answer your question about limitation, or probably you have another sugestion?

About CSV, is not a mandatory, even I can accept simple text file, as long as I can detect what is the separator between fields, but I need it without field/column names. And I think pipe symbol is good idea.

Since you mention about importing to a temporary table, I will try it tomorrow.

I believe there are many easier and more efficient ways than mine, but since I have zero knowledge about XML and other format, I think I will stay using shell script for more longer.

Link to comment
Share on other sites

Well, if the format I suggested is acceptable to you, you can generate it very easily by exporting as XML using the FMPXMLRESULT grammar and the following XSLT stylesheet:

<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">
	<!-- header -->
	<xsl:text>ID_PROJECT,Project_Name,ID_CUSTOMER,Customer_Name,Customer_Type,ID_PRODUCT,Product_Name,ID_USER,User_Name,User_Title&#10;</xsl:text>
	<!-- data -->
	<xsl:for-each select="fmp:RESULTSET/fmp:ROW">
		<!-- start row -->
		<xsl:for-each select="fmp:COL">
			<!-- start field -->
			<xsl:for-each select="fmp:DATA">
				<xsl:value-of select="."/>
				<xsl:if test="position()!=last()">|</xsl:if>
			</xsl:for-each>
			<xsl:if test="position()!=last()">,</xsl:if>
		</xsl:for-each>	
		<xsl:text>&#10;</xsl:text>
	</xsl:for-each>
</xsl:template>

</xsl:stylesheet>	

Apart from the hard-coded header (which I guess you do not need and can remove), this is quite generic: it will automatically detect any field that has multiple DATA elements - that is either a repeating field or a field from a related table having more than one related record - and place a | separator between the values of that field. 

If you do keep the header, you must export the fields in the same order as listed in the header; otherwise you can use any order you like, as long as the recipient uses the same order when consuming the result. It is also possible to have the stylesheet generate the header dynamically, using the table/field names from the original file.

Caveat: fields are not quoted; it is assumed that none of your fields will ever contain a comma, a quotation mark, a carriage return or the | vertical bar character. If that's not a valid assumption, then this needs a little more work.

 

Edited by comment
  • Thanks 1
Link to comment
Share on other sites

This topic is 999 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.