6 Replies Latest reply on Jan 3, 2011 5:34 PM by GregSmith

    Extra Carriage Return for MYOB

    Rick2000

      Title

      Extra Carriage Return for MYOB

      Post

      I'm running MYOB Accountedge with Filemaker Pro 10. I'm trying to import FM10 records into MYOB but MYOB invoicing requires an extra CR between the Filemaker records to have it post post properly. So far I've been doing it by opening the tab-separated file in a word processing program, search for \r and replace with \r\r. I then import the corrected word processing file into MYOB. Is there a way to insert an extra CR between records in FM10 to avoid using the word processing program?

        • 1. Re: Extra Carriage Return for MYOB
          philmodjunk
            

          You might try this and see if it works...

           

          Whatever field is last in the row of exported text for a given record, replace it with a new calculation field in your specified sort order. Make the calculation field:

          OriginalLastField & "¶"

           

          I'm not sure that'll work, but it's worth a try.

          • 2. Re: Extra Carriage Return for MYOB
            comment_1
              

            PhilModJunk wrote:

            I'm not sure that'll work, but it's worth a try.


            I am sure it won't:

            http://www.filemaker.com/help/html/import_export.16.27.html#1029588

             


            • 3. Re: Extra Carriage Return for MYOB
              comment_1
                

              Rick2000 wrote:
              Is there a way to insert an extra CR between records in FM10 to avoid using the word processing program?

              It's possible, if you export as XML and use a custom XSLT stylesheet during export to insert the record delimiters - see:

              http://forum-en.filemaker.com/fm/board/message?board.id=FM-en-4&view=by_date_ascending&message.id=35115#M35115

               

               

              Otherwise you'd need to generate a dummy empty record for each exported one - not something I'd look forward to (and it might not work anyway, since the tabs will be there anyway).

              • 4. Re: Extra Carriage Return for MYOB
                Steve Wright
                  

                I dont use MYOB but developed an export routine for a client.

                 

                I used a temporary table to build the export set, including a blank record as required.

                I then exported to CSV not TAB

                 

                Because of the blank record, the file looks like  the following..

                 

                "Co./LastName","First Name","Addr1","Addr2","Addr3","Addr4","Invoice No","Date","Customer PO","Delivery Via","Delivery Status"

                "data","data","data","data","data","data","data","data","data","data","data" 

                "","","","","","","","","","",""

                "data","data","data","data","data","data","data","data","data","data","data"  

                "data","data","data","data","data","data","data","data","data","data","data" 

                "","","","","","","","","","",""

                 

                 

                Obviously, there's more fields but its an example for readability 

                 

                 

                Edit : "Comment" beat me to that one :-) 

                • 5. Re: Extra Carriage Return for MYOB
                  Rick2000
                     Thanks for all the information. Here's how I'm getting around the problem now which may not be perfect but does work well. I created a Filemaker script that finds the customer and sales records (two separate files) and then exports them. In the same Filemaker script, I've created an Applescript to open TextWrangler which opens the exported tab-separated sales file, does the search for \r and replaces with \r\r, then saves and quits. The Filemaker script goes on to open MYOB and imports the customer records and corrected (\r\r) sales records. The whole process takes less than 5 seconds to export/import about 100 records and also provides a word processing document (the corrected sales record file) you can refer to if any problems occur during the process. It happens so fast that you really have to watch to see TextWrangler do its part of the process. MYOB also provides a log indicating if there were any importing problems such as records skipped, fields misaligned, etc. It might not be pretty, but it works.
                  • 6. Re: Extra Carriage Return for MYOB
                    GregSmith

                    I solved the same problem with the following script to add the necessary blank records and then delete them after the export. This only works for single item sales but can be easily modified for multiple items - no external Applescripts etc needed.

                    Basically it runs through and counts the number of records in the found set to be exported adding a record count starting at 1 and incrementing by 2 in a field GlobCount, then it creates  the correct number of blank records adding a record count starting at 2 and incrementing by 2 for each new record in GlobCount. At the same time in the field "deleteme" it adds "yes". We then sort on the Globcount field which gives us a found set with blank records between each real record. The found set is then exported and then the blank records are deleted by using the deleteme field as a flag.

                    Go to Record/Request/Page[ First ] 

                    Set Field [ Mailing Labels 2::GloBCount; 1 ] **** ( Start numbering our records from "1")

                    Loop

                    Set Field [ Mailing Labels 2::COUNTER; Mailing Labels 2::GloBCount ]

                    Set Field [ Mailing Labels 2::GloBCount; Mailing Labels 2::GloBCount + 2 ] ****( Count by 2 when numbering records so we can insert a Blank Record in the sequence - so our real records are numbered 1, 3, 5, 7, 9, etc)

                    Go to Record/Request/Page[ Next; Exit after last ]

                    End Loop 

                    Set Field [ Mailing Labels 2::Number of Records; (Mailing Labels 2::GloBCount-1)/2 ] ***** (work out the number of Blank Records we need)

                    Set Field [ Mailing Labels 2::GloBCount; 2 ] *****( Number the Blank Records starting at 2)

                    Loop

                    New Record/Request 

                    Set Field [ Mailing Labels 2::COUNTER; Mailing Labels 2::GloBCount ]

                    Set Field [ Mailing Labels 2::Delete Me; "yes" ] ****(Add a flag so we can find the Blank records later to delete them)

                    Set Field [ Mailing Labels 2::GloBCount; Mailing Labels 2::GloBCount + 2 ] *****(Increment counter so Blank Records are numbered 2, 4, 6, 8, etc)

                    Set Field [ Mailing Labels 2::Number of Records; Mailing Labels 2::Number of Records - 1 ] 

                    Exit Loop If [ Mailing Labels 2::Number of Records=0 ]

                    End Loop Sort Records [ Specified Sort Order: Mailing Labels 2::COUNTER; ascending ][ Restore; No dialog ]

                    Perform Script [ “ExportSales” ]  ***** (this script is just a straight export statement)

                    *****(This next bit loops through the found set and deletes the blank records we inserted above)

                    Go to Record/Request/Page[ First ]

                    Loop    *****(Deletes the Blank Records which we no longer need)

                    If [ Mailing Labels 2::Delete Me ="yes" ]

                    Delete Record/Request[ No dialog ]

                    End If

                    Go to Record/Request/Page Next; Exit after last ]

                    End Loop