So my company is moving our finance side to Sage 50. They have tasked me with linking Filemaker and Sage. Unfortunately the version of Sage my company got does not have a 64-bit version of ODBC drivers to connect up with Filemaker that way for ease of convenience. The only way to deal with this is to create an application with the Sage SDK to basically export and import files. With Filemaker, I have my POs set up with 2 separate tables. One table for the information that is for the whole PO, like company information, shipping, pay terms, etc. The 2nd table is for the Line Items of the PO. Standard 1 to many relationship. Of course a PO could have simply 1 line item as well.
The problem I have is in the exporting from Filemaker. I need a single csv file that I can then import into Sage. I need the PO #, Vendor #, Terms, Shipping method, and Date from the Main PO table and then Quantity, Part ID, Description, G/L code, and Total cost from each line item. For the csv every line is a line that imports, so I need the 5 items from the Main PO on every line, and then the 5 other items are different per line based on the Line Items themselves.
I am not the best scripter, I am getting a lot better as I go, but I am not sure the best way to do this kind of export into the SAME excel file. Also, Sage needs a field that I don't have in Filemaker, so I need to add another field to this CSV as it is exported. Luckily that field is the same number for my purposes. I was wondering if using a Temp table to hold this information is the best way, or if someone has another way to do this? If you just want to point me to a place that explains this process in detail, I am ok with that as well.