Export Sales to MYOB

[chrisrgr]

I need to export sales transactions from Filemaker 9 into MYOB. MYOB requires that each new sales record have a blank row between itself and the last sales record. How would I tell Filemaker to export this blank row between records into Excel?

[mass6]

I'm not sure how or even if this can be done is FM, but if Excel is you target, I can provide with the following technique.

1. Export table to Excel as is.

2. Open in Excel, and then create a new column at the far left of this spreadsheet.

3. In this column (A), and in the first row (1), which should be cell A1, enter the number 1.

4. Select this cell (A1) and all the cells below it to the bottom of your data rows. So if your spreadsheet has 2000 rows, you should now have 2000 rows selected (A1 through A2000).

5. Now you want to fill this row with a series of odd numbers, 1,3,5, etc. You can do this by choosing from the toolbar EDIT>FILL>SERIES, and then entering 2 for the step value. This should fill this column with odd numbers starting with the number 1.

6. Now, go to the first blank row after your data and enter the number 2 in the first cell (Should be A2001 by this example).

7. Now, select this row and the 2000 rows below it (A2001 though A4000).

8. Repeat step 5 above. This should fill these cells with even numbers starting with the number 2.

9. Now, in column A, you should have rows 1-2000 numbered with Odd numbers, and the following 2000 cells, rows 2001-4000, numbered with even numbers.

10. Now simply sort column A in ascending order and you will have your original data in order with blank rows in between them. At this point you can delete column A if you like.

Hope this helps. Good luck.

[Fenton]

You could do very much the same thing as mass6 suggests in FileMaker, but importing your IDs (and only your IDs) from your main table into a temporary table. Then using the Replace step to do what he said with Fill Down.

But, personally, I think I'd export as delimeted text, then run it through another tool to add the extra blank lines (AppleScript, command line, or a text editor).

[chrisrgr]

I am currently trying to experiment with inserting all the field information into a global field and then exporting that global field, I just haven’t figured out how to get all the formatting to work.

MYOB processes tabbed delimited files by importing all the sales info by order line item. Orders with multiple line items are grouped one row after another. MYOB know to start a new invoice when line items are separated by a blank row.

Since there is no way that I know of to export with this requirement directly with the export records command, I was thinking I might be able to do this by writing all the records to a global field then exporting that global field.

This works however, I still have not been able to figure out how to format the data as it is written into the global field that would meet the requirement that MYOB needs.

[Fenton]

I guess there are several ways to do this, and that how you want to do it depends on what experience you have, with both FileMaker and other tools.

My personal preference is to use other tools to produce an export that is "FileMaker unfriendly," rather then mucking up my FileMaker file, especially if Macs are the only machines involved, as there are built-in AppleScript and command line tools which are good at post-processing text.

A cross-platform solution might be xml export, with an xsl stylesheet. If you can identify the lines where you want the extra returns, then xsl could just add a blank line there (before the next order likely).

The FileMaker global field should also work, though likely slower. What kind of problems are you having with that? It is kind of hard for us to tell. Perhaps you could post a small example of what the text file is supposed to look like.

[chrisrgr]

Here is what the import file should look like.

MYOB_Sample.txt

[Fenton]

Well, that looks fairly simple. I imagine there are more fields involved. But basically it is much like a FileMaker Subsummary. When the CustomerName (or ID) changes, then a new row is needed, before it.

I could write the xsl for this, fairly simply. Unfortunately I can't do it right this minute; maybe in 2-3 hours from now.

But I also wonder what problem you would be having with the global field. I can't see anything that would stop a Loop from building this same thing.

Or maybe you've got it built, but can't get an export file that works? If you're using Export Field Contents, it produces a UTF-16 file, which not all applications can handle. It is fairly easy to get another format however, by exporting a single record-single field, or by writing the file directly with AppleScript and command line (sounds geeky, but is quite easy on Mac).

[chrisrgr]

There are about 50 fields involved but once I get the template made it will be easy to add the rest.

The problem I am having with the global field is formatting. I can get all the info from all the records into the global field. I just don’t know how to add the blank row or headers.

Right now my website back end downloads into this type of file and I import it directly into MYOB however, I would like to build a FMP solution and from Web to MYOB all intergrated.

[Fenton]

An xsl template may not need to "hard-code" the field names, as it could read them from the export order; worth the trouble when there's 50.

I'm wondering what the problem is. You're doing this with a script, right? In which case why couldn't it add an extra blank line, when either you reach the end of an order, or before the beginning of the next order (much the same thing, but a different test). Both of these tests should be available to you, looping through the order line items. The global field could be in any table, 'cause it's global -]

You would basically be setting the global to itself, then whatever, extra line or record of stuff (fields and tabs), over and over. I think I'd create Unstored calculations for the "stuff," including the tab (which you can either put the tab into the calculation itself, or better, put one in a Custom Function, then use that wherever).

[chrisrgr]

Ideally I would like to just have FMP handle the whole thing. The only issue I am having is not knowing how to make FM format the global field. What tool would I use in the calculations menu that would insert the data with a tab separation and also a row separation?

[Fenton]

I think the relevant phrase is "build the global field", not "format the global field." It is going to be a Loop, Go to next record (exit after last) script. It is going through the Invoice line items, sorted by customer Id, and building the global field line by line.

In each of the tables, Invoice and Invoice Line Items, you would have an unstored (important) calculation field which puts together the fields you want, including a tab* between the fields. You set the global field to itself, and whatever the next line is.

When you go to the next line, if the customer is different, you add an extra line to the global field (set it to itself & ¶), before adding the next data line.

You would then go grab one line from the Invoice table, the customer info, via the Invoice relationship. Then start adding the line items lines again.

When the Loop ends (hopefully), you need to export the global field out. Export Field Contents may not work, because of the UTF-16. You can export tab-delimited instead, 1 record, 1 field. Use a New Window to isolate.

If you need more help, could you upload a simple example file?

*Either type the tab into the calculation field (inside quotes), or type it once into a Custom Function and use that.

[chrisrgr]

Fenton I got it to work. I could not figure out how to get a tab into the calculation menu then I just tried pasting " “ into the calc menu from text pad and it worked, I was able to make the field format correctly.

Thanks for your help …Chris

Edited October 14, 2007 by Guest

[Garry Claridge]

I wrote an FM to MYOB application about seven years ago, it still works fine

I used a temporary table (as Fenton) suggested. This table had most of the fields needed by MYOB. The Script that wrote the data to the temp table and also inserted the extra lines.

It then exported a tab delimited file.

The Script then used an AppleScript step to import the exported file directly to MYOB.

So, all done with one button in FM :

Good Luck.

Garry

[Willrollo]

On 17 October 2007 at 2:17 AM, Garry Claridge said:

Hi Garry

I am experiencing similar issue with Accounts edge and FM 14! Any chance you still have a copy of your app mentioned here? Would be a huge help to me!!!

Thank you