1 2 Previous Next 21 Replies Latest reply on May 28, 2015 4:45 AM by Menno

    XML newbie - can this be done?

    praxiscreative

      Hi, I have an FM database from which I would like to export a ready-to-use aba file (a banking file listing transactions to be made from an account). The database is a list of purchases for which payments need to be made and lists names, BSBs, account numbers and transaction amounts.

       

      An aba file is just a UTF8 text file with a single Descriptive Record line with basic account info and processing date info, followed by each transaction on a separate line. Finally a single summary line appears that lists among other things the number of transactions.

       

      I've set up a single field for each of these elements, called abaDescriptiveRecordLine in a grand leading summary, abaRecordLine (body)and abaFileTotalRecordLine (trailing grand summary) respectively, and they all work perfectly - onscreen the report is impeccable.

       

      However, I'm at a loss as to how to persuade FileMaker to export the XML the same way ie:

       

      abaDescriptiveRecordLine

      abaRecordLine

      abaRecordLine

      abaRecordLine

      ...

      abaFileTotalRecordLine


      Can someone point where to start with an XML template?


      Many thanks in advance

        • 1. Re: XML newbie - can this be done?
          beverly

          You will not find a "template". You will need to create your own. XSLT is the way to transform FM xml to the format/grammar/schema needed by ABA. Because your database may be different from any other database, the "mapping" of your fields to the ABA elements will not be the same as any other database that may be out there.

           

          However, you can also script & calculate  the XML needed instead of using XSLT. This may be preferrable to many. The use of variables and the FM functions can do this. You need to LOOP through the parts that may repeat (such as the 'abaRecordLine' within the 'abaDescriptiveRecordLine'). I recommend that you push each element to a single field, new record in a temporary table for the sake of export in the correct UTF. Think through how you would manually get the data for each part of the XML and then script through it.


          beverly

          • 2. Re: XML newbie - can this be done?
            Menno

            The attached file is about exporting invoices to xml which i have written a while back for some collegues and they had a good startingpoint with the examples. So it should give you some pointers on how an xml/xslt-export works.

            (I wrote it originally in dutch, so some of the translations may be a bit strange.)

            cheers, Menno

            • 3. Re: XML newbie - can this be done?
              ibrahim_bittar

              I agree with Bev 100%.

               

              We use XML generation everyday in our solution for electronic invoicing in Mexico. The easiest way is to build your own XML and then export as a text file (with XML extension) with UTF-8 encoding.

               

              For the UTF-8 part I use BaseElements plugin. It's just great and free. What else can I ask for?.

               

              Regards

               

              Ibrahim

              • 4. Re: XML newbie - can this be done?
                nicolai
                For the UTF-8 part I use BaseElements plugin

                Nothing wrong with the plugin, but you can also use bash from FileMaker if you are on a mac, the script is fairly straight forward. On Windows I would look into VBS, it should have this functionality as well.

                • 5. Re: XML newbie - can this be done?
                  ibrahim_bittar

                  Yes but you equally need to run a command, so you'd still need the plugin.

                   

                  I'm not sure about what's I've said but almost...

                  • 6. Re: XML newbie - can this be done?
                    nicolai

                    You can run terminal commands with Execute AppleScript. The one in question would be:

                     

                    do shell script \"iconv -f " & $currentEncoding & " -t " & $targetEncoding & "  '" & $filepath &  "' > '" & $filepath & "1" & "'\"

                     

                    I put a file together to demonstrate the technique. If you are using Pro and not Advanced, some of the  AppleScripts have to be adjusted to target FMPro instead of Advanced.

                     

                    (Tested on OSX 10.9 FMPA 14)


                    Nicolai

                    • 7. Re: XML newbie - can this be done?
                      ibrahim_bittar

                      Oh I see.

                       

                      Does it work with FMGo?

                      • 8. Re: XML newbie - can this be done?
                        nicolai

                        Touche. I suppose you use the plugin server-side to cover FMGo?

                         

                        If I do it for FMGo/Server combo and I have a mac server, I would upload the file to a folder in Documents and set an OS trigger on the folder to convert the file from utf-16 to utf-8 using a similar bash script. Then just download it back to iOS device. If I had to to it on Wintel I would go back to google and come up with something.

                        • 9. Re: XML newbie - can this be done?
                          Menno

                          If you'd use PSOS, you'd also be able to export (and import) using XML/XSLT, that would also work with FMGo. I have that (with an import from a webservice) running for a customer who needs the daily currency exchange-rates from the ECB. No plugins at all, the xsl is realy a lot simpler than a script creating an xml.

                          • 10. Re: XML newbie - can this be done?
                            beverly

                            I agree on that Menno, but I've found many people reluctant to learn XSLT. Thus, my suggestion that it *can* all be done with calcs & scripts.

                            • 11. Re: XML newbie - can this be done?
                              praxiscreative

                              Many thanks to all for your input - I'll keep chewing away at it.

                              • 12. Re: XML newbie - can this be done?
                                Menno

                                You are right on people being reluctant, I experienced the same thing. It also seems for a lot of people that the tools needed for producing xml/xslt create a barrier. It is possible to just use something like notepad and a webbrowser, but life is a lot eassier when an editor like oxygen or xml-spy is used, but those editors are not exactly free.

                                 

                                Without the proper tools xml/xslt is hard, however with them, creating flawless xslt-files or finding errors in xml takes hardly any effort. I started once creating xml's with scripts, but after endless problems with charactersets, entities transformed, switched elements, no debugging-means etc.etc. I took up to learn xslt some 10 years ago and now I still use the technique on a daily basis and even create excel-documents with it. Learning xslt is not very easy, but it is doable and it really pays of putting in the effort.

                                • 13. Re: XML newbie - can this be done?
                                  beverly

                                  Well, I use BBedit for XSLT creation. But I do have Xmplify if needed.

                                  • 14. Re: XML newbie - can this be done?
                                    ibrahim_bittar

                                    I use Oxygen every day for XML editing but I have to confess that I'm a little bit afraid of XSLT.

                                     

                                    Is there a good learning source for XSLT?.

                                    1 2 Previous Next