1 2 Previous Next 17 Replies Latest reply on Aug 13, 2013 8:15 AM by willrollo

    Tricky export

      Title

      Tricky export

      Post

           I am trying to copy an export function that an access database currently does to a pc based accounting pacakge. The export file type is usual csv. 

           The user sets a time frame (say june 1st to june 30th). Any new customers who have placed an order in that date are exported as a csv fiel that contains a few details abou the customer - (name, cust number phone etc). (Can do this part!)

           It then exports a file that lists the invoices for those customers. The first record called Header, will contain fields such as date, account number, customer code etc. then the detail records will show 3 records each containing different lines from the such as tax type, total , subtotal. cost code and others...

           This is quite difficult to understand but belive this type eof export is quite common with database to account packages so I am hoping someone may recognise what I am trying to explain! I have pasted an exampe piece from the export from access csv file - it is for one new customer who placed an order.

            

           "header","00019939","Y","Y","RE3099",07,"02/04/2012","19939","N",0000,"","","","","","","","","00","00","01/01/1999"
           "Detail",0,1,45.38,45.38,"EA","03","0","0000","1000310","G3065","6","",""
           "Detail",0,1,9.9,9.9,"EA","03","0","0000","9500000","Total VAT","6","",""
           "Detail",0,1,4.17,4.17,"EA","03","0","0000","1000303","P","6","",""

           So In fm 12 adv, how do i do it?

           I know how to export I just am unsure of how to export the headers and details parts.

            

           Here is the template from the accounts package: obviously I dont use all the fields...

            

           Header (HAS TO BE CALLED Header - DELETE EVERYTHING IN BRACKETS),Document Number,Deleted,Print Status,Customer/Supplier Code,Period Number,Date,Order Number,Inclusive/Exclusive,Discount,Invoice Message1,Invoice message2,Invoice Message3,Delivery Address1,Delivery Address2,Delivery Address3,Delivery Address4,Delivery Address5,Sales Analysis Code,Settlement Terms,Document Date,Telephone,Fax Number,Contact Person,Exchange Rate,Additional Costs,Freight Method,Ship/Deliver
            
           Detail (HAS TO BE CALLED Detail - DELETE EVERYTHING IN BRACKETS),Cost Price,Quantity,Unit Selling Price,Inclusive Price,Unit,Tax Type,Discount Type,Discount Percentage,Code,Description,Line Type,Cost Code,Store,,,,,,,,,,,,,,
            

        • 1. Re: Tricky export
          davidanders

               concantenating fields would work

          https://www.google.com/search?q=concantenate+fields+filemaker

          • 2. Re: Tricky export

                 Hi David

                 I dont see how this would help - It is more that each 'header' record has to have a field called " header' and the subsequent realted records with a 'detail' field. 

                  

                 So for eg, if a client places 3 orders, there will be three invoices. The header record will have such field as Status,Customer/Supplier Code,Period Number,Date,Order Number, as Well as of course the field that should have the data "header; in it. Then three more records which will be the data from the invouces, Detail, Cost Price,Quantity,Unit Selling Price,Inclusive Price, etc.

                  

            Does this make sense?

                  

            • 3. Re: Tricky export
              philmodjunk

                   This file is not a standard csv format as the number of columns in each row is not the same. The header has more columns than the detail rows.

                   You could set up an intermediate table with a set of text fields where a looping script copies the data, first from the header record, then from the detail records, but the detail rows then produced by exporting will have additional empty columns of data than shown in your example.

              • 4. Re: Tricky export

                     Oh Dear! Well how do I overcome this? The accounting software (Sage pastel) has an instruction sheet on how to export into it - I would have thought this owul be a standard export function for a database?  I have linked to a  pdf from the company just in case it may change your solution !

                • 5. Re: Tricky export
                  philmodjunk

                       PDF's cannot be uploaded to a post in this forum. Please note the file formats that are supported in the text next to the upload an image controls. You can post links to file sharing sites such as DropBox if you want to share files

                       CSV IS a standard file format and is supported by FIleMaker PRo, but rows of data where the number of comma separated values are not the same in each row is not supported by FIleMaker as far as I know.

                       What we dont' know is what Sage would do if you imported your data and the detail rows had those extra empty values. It's quite possible that those additional empty values would be ignored and it would import successfully.

                  • 6. Re: Tricky export

                         sorry - thought that hotlink may have worked - see this:

                         https://www.dropbox.com/s/f0lpxryuwpm65ro/Documents%20Import%20Export.pdf

                         let me know your thoughts when you have a minute as I really need this to work - or else swap accounts packages which is also a nightmare! 

                         thanks

                         Will

                    • 7. Re: Tricky export
                      philmodjunk

                           I believe that I"ve already shared my thoughts on this issue in my preceding post.

                      • 8. Re: Tricky export

                             Yup - I wasnt sure if there would have been anymore info on the pdf that may have altered this...Just wanted to check before trying something else...

                              

                        • 9. Re: Tricky export
                          philmodjunk

                               I suggest a small experiment.

                               Set up a table with enough text fields to have one field for every header field.

                               Manually create one example header record and enter some test data in the fields.

                               Manually create one child record and enter test data, leaving the extra text fields empty.

                               Then try exporting that data as a CSV and importing into the other application.

                               If it works, you can pursue setting up a script that loads this export table with the needed data.

                          • 10. Re: Tricky export

                                 Hi Phil

                                 I have created a new table - Export. In this table I have created new fields as per the instructions on the pdf, as well as a field called "header'. Total of 22 fields.

                                 I have also created a 'detail' field and the subsequent fields for that record too.  Total of  14 fields. 

                                 Should I have created two tables? -  an Export Detail table and an Export Header table, and then link them together on a layout and then export them? 

                                  

                                 The Export table is not linked to any other tables as yet. But if I want to export a PDF from this table, should I create a foriegn key linking it to my Invoice details table?

                                 Was attempting to follow your instructions above but wasnt sure about the Manually create one child record part...

                                  

                            Thank you

                            • 11. Re: Tricky export
                              philmodjunk

                                   Manually create one child record is just my suggestion on what to do with your data in order to test this with a very small number of records so that you can quickly and easiry see if it works. You can enter data by hand or just isolate a single record from your existing data.

                                   The export table doe not need to be split into a header and detail table. The structure you have to produce here compresses all your data from two or more tables into a single "flat file" for export where some of the fields in that table hold data from the header record in one row and from child records in subsequent rows.

                              • 12. Re: Tricky export

                                     Thank you. How do I create a structure or export file contains data from the export table with header records and detail records? I am completely flummoxed on how to do this!

                                • 13. Re: Tricky export
                                  philmodjunk

                                       Your example in your first post shows this:

                                       "header","00019939","Y","Y","RE3099",07,"02/04/2012","19939","N",0000,"","","","","","","","","00","00","01/01/1999"
                                       "Detail",0,1,45.38,45.38,"EA","03","0","0000","1000310","G3065","6","",""
                                        
                                       If I count correctly, that's 21 items in the header row and 14 in the detail row. You would create a new table with 21 text fields.
                                       Your script would use 21 set field steps to populate the row for a header record. It would use 14 set field steps to populate the same first 14 fields with data for a detail record.
                                        
                                       But before you try to create such a script. Just create this table, set up a table view of it and enter the above date by hand without the quotation marks. Each quoted item would be the data that goes into a different field.
                                             
                                            If you have this data in a file, you can even create your new table by using Import Records to import this file and then select the "new table" option for the target table.
                                        
                                       Then use export records to export these two records as a csv file and see if you can import it into the other application. If it works, we can take a look at how a script can move the needed data into this table and then export it as a csv file.
                                  • 14. Re: Tricky export

                                         Thank you - that is really kind of you to help me on this! Will follow your instructions and get back to you..

                                          

                                    1 2 Previous Next