6 Replies Latest reply on Dec 24, 2016 4:01 AM by fmpdude

    Filemaker data conversion


      I am trying to use a clearing house for medical billing and they require a conversion of my filemaker pro data to other formats.  They can accept a few different file formats.  ANSI 5010 is the most popular, but they also accept ANSI 4010 or text/Print Image formats.  Is this possible with Filemaker?

        • 1. Re: Filemaker data conversion

          Well, you can certainly export data from FileMaker to various text formats, including .csv, .xml, .tab and (by simply changing the filename extension) to .txt.


          I just did a DuckDuckGo search for the ANSI standards you cited. Perhaps somebody in the FileMaker world has already done it. If not, I suppose it's theoretically possible to generate this format on your own as it appears to be a text-based data format. But it looks like an absurd amount of work, probably beyond budget of a typical FileMaker consulting project even. I hasten to add that I don't really know and perhaps you have (or someone else has) already done half of the work.


          I'm not sure what you mean by "Print Image" format. Is that like EPS? What about PDF? FileMaker can't generate an EPS file but it can easily generate a PDF.


          Perhaps if you provide a little more info, somebody who's done this type of work before will be able to help. Sometimes useful to include a subject-area keyword in your topic description (say "medical billing") to catch the eye of somebody with the requisite expertise. "FileMaker data conversion" is just a step more precise than "I have a question". :-)



          • 2. Re: Filemaker data conversion

            The data is in TEXT of some pattern. Older versions used an EDI format somewhat like:




            Newer versions use XML:

            <name first="John" last="Doe" />



            or something similar.

            Some versions may be a CSV-type (or other) text format.


            So you must contact the Clearing House and get the specific format (of TEXT) that they need. Then you also need to verify the transmission method (usually secure) that must be used to send and/or receive the text. Then and only then can we point you to further possibilities.


            Short answer: yes (although plug-in or external means may be necessary as part of the process)




            p.s. edited because reply-by-email munged the code.

            • 3. Re: Filemaker data conversion

              A few ideas:


              (1) With the documented format you may be able to create another table/layout in FMP (depending on format complexity) to hold the calculated new format you need. Then export that as text.




              (2) If you need more control, you could also use Java (and use FileMaker's free JDBC driver) to write a program to do everything programmatically also. Depending how long your FileMaker "Script" is in (1) above, I might opt for Java.


              (Looking around, I noticed there were some libraries, already written for Java, that seemed to do this conversion. Unless no other options existed, and there were "test suites" to verify the data format created, I wouldn't try to write a complex data conversion myself for something even remotely approaching HIPPA requirements.)




              (3) Export everything from FMP first and either find a program, or other method, that will take a text file as input and spit out your desired format or just work with the exported text file separately. Obviously, this isn't the best approach if you need multiple exports.




              You might also want to see if you can secure FTP (SFTP) your resulting file programmatically to the destination.




              Since I'm not sure of your background, I'm not sure which of these might work best for you.


              HOPE THIS HELPS.

              • 4. Re: Filemaker data conversion

                The format appears to be just text. Example below:


                ISA*00*          *00*          *ZZ*SENDERIDISA06*ZZ*RECEIVERIDISA08*120219*0910*|*00501*000000001*1*P*:




                NM1*41*2*PRACTICE NAME*****46*SENDERID

                PER*IC*FIRST LAST*TE*8003334747

                NM1*40*2*CCB MEDICAID*****46*RECEIVERID


                I've done something similar in the past to create a file for loan payment information. I was basically a table with one text field and a script created the header/footer and the payment data in the required format. The table was then exported and sent to be processed.


                I would be willing to give you a hand with it.

                • 5. Re: Filemaker data conversion

                  Yes, I also have done this (old style EDI). @MILLS must verify the format and transmission before going down a road that is incorrect.

                  • fixed width or other patterns?

                  • include even "columns/fields" with no data - as blanks?

                  • correct end-of-line (CR? LF? CRLF? "~"?, etc.)

                  • sent as plain text or encrypted?

                  • ...


                  • 6. Re: Filemaker data conversion

                    After coding the transformation, the real issue is that you need some way to "verify" that what you have is correct each time. There may be some type of service that will take a file and verify what it "thinks" you sent it.


                    Or, maybe there are some available test files you could use as a guide?


                    Once you get it right, it will probably always be right but as beverly points out, there are many special cases.




                    If there's no "verification" service or help, that I'd try to get a copy of a some data you have in FMP, but is ALSO in "their" format.  Then, work on your conversion method, however you decide to do it (in FMP, etc.), to get that same FMP data to exactly match what they have for that data. I'd repeat this for a few weeks or months depending what your billing frequency is and make sure it's correct.


                    Even after you switch to the conversion, should you decide to actually do it, I'd still monitor statements every month (or other billing frequency) for a year or more.


                    I would map out what I needed to do in Excel (OpenOffice, LibreOffice, etc.) FIRST before trying to jump in and code it in any environment.


                    HOPE THIS HELPS.