Exporting data to CSV across two rows

[tynedragon]

Hello,

This is my first forum post so please excuse the lack of detail, but I'll explain as much as I can, as best as I can.

I am building a database to manage data input into apples GSX service network. It can have its data uploaded as an excel file, or a tab delimited CSV file. This is fine, however the file must have the data structured in the following way with regard to the rows of data:-

Customer 1 details

Part details for customer 1

Customer 2 details

Part details for customer 2

Part details for customer 2

I am really looking for guidance on how best to structure the database to allow export of details onto two separate rows. Is it best to have two databases and a script to collate the data into one exported file?

I'm a complete filemaker newbie but I've used databasing software before.

I've included the template file, this shows the format the file should be in. (its a tab delimited txt file)

RepairUpload.txt

[comment]

Can there be multiple parts per repair?

[tynedragon]

Yes, there could possibly be multiple parts to each repair, up to a maximum of four parts if that helps matters. but it wouldn't be a huge inconvenience if it could only be one part, the rest could be added manually since most repairs require only one part.

[comment]

I am really looking for guidance on how best to structure the database to allow export of details onto two separate rows.

IMHO, unless the only purpose of your solution is to produce the exported file, the structure should reflect the real situation - i.e. a parent table of Repairs and a child table of Parts.

Now, the described output format is not a standard tab-delimited file (and certainly not CSV). Still, there are several ways it could be produced:

1. Export as XML and use a custom XSLT stylesheet to format the output;

2. Use Applescript to write the file (or to "fix" a file produced by a standard tab-delimited export);

3. Use a Filemaker script to write out the entire report into a global field, then export the field's contents (provided the target application will accept a UTF-16 encoded text file).

the rest could be added manually

I am not sure what you mean by that. Added manually at what point?

BTW, I really should have asked:

Can there be multiple parts per repair - and can there be a repair with no parts?

[tynedragon]

When I said the rest could be added manually, there's also a web interface with which you can do things, but its very long winded at times.

There can be multiple parts for the repair, but there cannot be repair without parts.

I had considered using an AppleScript to put a return in where I want the first line to end, however I'm very interested in your third point. Could you perhaps elaborate on this more, or give guidance on where to find further details on doing something like this please?

[comment]

I had considered using an AppleScript to put a return in where I want the first line to end

It's a little more complex than that, I'm afraid, because you must also remove the repair fields (or their tab placeholders) from the subsequent parts in the same repair.

I'm very interested in your third point. Could you perhaps elaborate on this more

Here's a simple demo:

Export2lines.zip

[tynedragon]

Just thought I'd drop back in to say a huge thank you for this. Its all working brilliantly and I'm not ashamed to admit that I couldn't have done it without your help!

[rwoods]

Hi Tynedragon

I have this exact same issue exporting to GSX, and in fact the code that 'comment' posted is exactly the solution we have. However, the resulting output file is a UTF-16 format, and GSX won't accept that. Are you have to post-process this file somehow to get it into GSX. I have to open then in TextEdit, paste into a new TextEdit doc and then use that, as the default TextEdit file format is UTF-8. Clunky.

The other option would be to use 'Export Records' as opposed to 'Export Field contents' as you can choose UTF-8 (which GSX supports), but that option strips the Char(9) 'tab' characters out of the resulting output file.

How did you get around the UTF-16 format issue if you don't mind me asking?

[imoree]

It's a little more complex than that, I'm afraid, because you must also remove the repair fields (or their tab placeholders) from the subsequent parts in the same repair.

Here's a simple demo:

Where do you find the time MIchael?