Newbies tynedragon Posted October 15, 2011 Newbies Posted October 15, 2011 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
Newbies tynedragon Posted October 16, 2011 Author Newbies Posted October 16, 2011 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 Posted October 16, 2011 Posted October 16, 2011 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?
Newbies tynedragon Posted October 17, 2011 Author Newbies Posted October 17, 2011 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 Posted October 17, 2011 Posted October 17, 2011 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 2
Newbies tynedragon Posted October 19, 2011 Author Newbies Posted October 19, 2011 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 Posted February 21, 2012 Posted February 21, 2012 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 Posted February 21, 2012 Posted February 21, 2012 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?
Recommended Posts
This topic is 4667 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 accountSign in
Already have an account? Sign in here.
Sign In Now