14 Replies Latest reply on May 30, 2017 7:26 PM by user19752

    export to excel is less than optimal

    PeterCortiel

      I have a screen with a portal:

      2017-05-29_07-26-38.png

       

       

      When i  export to excel i get the child records in column to the right of the parent data

       

      2017-05-29_07-26-05.png

       

      Horrible! Why can't I use office automation like in VFP and create and format cells directly

      Without having to shell out $$$$

       

      At least i should be possible from filemaker to pages since both come from Apple!!!

       

      Are the better ways ??

       

      Peter

        • 1. Re: export to excel is less than optimal
          beverly

          Export from a PARENT and including Child Records is always like that. If, OTOH, you export from the CHILD records and include the parent, you get the same information in a different format.

          If you wish to "duplicate" your layout in Excel, you must:

          • use XML & XSLT to create the .xls

          • calculate the XML in Spreadsheet Markup Languange

          • calculate as HTML and have Excel open the file (which is can do very well)

          • web publish and have PHP or other web app create an Excel document as you wish

          • ??

           

          There is no 'push-button' option for what you want. It will take work.

          Beverly

          • 2. Re: export to excel is less than optimal
            PeterCortiel

            Can I have a HTML template and merge into it variables and a table for the children records?

             

            Would I have to have a global variable to contain the template code and search and replace fields

             

            Has someone done this kind of thing (in VFP this was so easy with text…endtext

             

             

            Thanks

            • 3. Re: export to excel is less than optimal
              beverly

              It would be difficult to have a "template" as your fields may not be the same as another's fields. So you must just calculate.

               

              See if this article helps (there are other articles on this site that present different options):

               

              Also this forum has many posts on Excel:

              Beverly

              • 4. Re: export to excel is less than optimal
                fmpdude

                Since you are a VFP'er, you know how to code. 

                 

                The good news is that you can integrate FMP with Excel using Apache Libraries and a little Java code.

                 

                Check out this article as one of many you can find: Excel and Java - Read and Write Excel with Java - Tutorial

                 

                ---

                 

                For our VFP client (the last one), we use MS' XMLHTTP OLE control so we can connect to REST services we write. Then VFP can do even more. I know your question is FMP, but FMP has INSERT FROM URL, which gives you that same flexibility built in.

                 

                What I miss from VFP when using FMP (but still can do using Java + FMP) are the LLFFs (FOPEN, FREAD, FPUTS, etc.) and native arrays as examples. OOP is another thing I miss. VFP is a great environment in so many ways including the EXE (free) distribution model which I demand to this day (no FMS here....).

                 

                Let me know if you have any questions.

                 

                HOPE THIS HELPS.

                • 5. Re: export to excel is less than optimal
                  PeterCortiel

                  I don’t know what you mean by ‘calculation’ but what i am trying to do is having an html document where all changing bits are replaced with  $variable in a field.

                  Like  

                   

                  Then i imagine doing something like set field table::html …

                  …export field ….

                   

                   

                  But so far I am not succeeding yet….

                   

                   






                  State of Connecticut

                  Office of the Treasurer

                  Interest Credit Program - Quarterly Report






                  <<$agency>>



















































                  <<$rows>>










                  <<$mo1>>

                  <<$mo2>>

                  <<$mo3>>















                  <<$perc1>>%


                  <<$perc2>>%


                  <<$perc3>>%





                  SIDName


                  Agency


                  Fund


                  SID


                  Balance1


                  Interest1


                  Balance2


                  Interest2


                  Balance3


                  Interest3


                  TotalInterest










                  • 6. Re: export to excel is less than optimal
                    beverly

                    You can calculate a field to be "HTML-tagged":

                    "<table><tr><td>" & field1 & "</td><td>" & field2 & "</td></tr></table>"

                     

                    This exported can be opened in Excel. Obviously this small example does NOT meet your needs. But what you have can be "calculated". The link to the article is another example of HTML->Excel. Were you able to test it?

                     

                    OR

                     

                    If you have a particular format and can create a "template" yourself in Excel, I've got clients who import a .csv file into one of the worksheets and the data "flows" into other worksheet(s) based on that import. The imported worksheet is just the raw data. The other worksheet(s) is/are what is printed, viewed.

                     

                    Beverly

                    • 7. Re: export to excel is less than optimal
                      PeterCortiel

                      So I can't use merge. With  <<   >>

                         

                       

                         

                      Then what to do with the quotes in HTML??

                         

                       

                         

                      Using a calculator function mmeans hard coding the HTML  

                         

                       

                         

                      Not good

                           

                         

                         

                      Peter +1.860.216.8044 cortiel@gmail.com 

                       

                         

                         

                         

                         

                       

                         

                         

                      >

                      • 8. Re: export to excel is less than optimal
                        fmpdude

                        FileMaker is probably not going to give you the programmatic control you had in VFP and in my view rightly expect from a multi-hundred database product. Using FMP for the first couple of years was a shock to me, too. "You mean I can't do .... 'that'?"  Although there are many high-end folks who use FMP in all kinds of advanced ways, in my view, FMP is aimed primarily at novices. Real software devs who are used to just writing code, having a powerful IDE, importing libraries, etc., are going to be frustrated.

                         

                        You should therefore considering to adjust to the FMP capabilities as they are, OR, take control and do some programming to integrate FMP with Excel (in this case) via JDBC. It's not difficult and then you are in COMPLETE control.

                         

                        Simple JDBC with FileMaker:

                         

                        (Java program):

                         

                        Create a connection to FMP database

                        Create a statement object

                        Create your SQL (SELECT, INSERT, DELETE, UPDATE)

                        For SELECT, Get a ResultSet back (like a CURSOR in VFP)

                         

                        Take that data and do stuff with it.

                             Export to Excel, for example

                             Write it back to FMP after fast processing in Java.

                         

                        Etc.

                        1 of 1 people found this helpful
                        • 9. Re: export to excel is less than optimal
                          beverly

                          Merge fields are layouts display items. You can place them in a calculation, but you have to Substitute() the correct values, so you might as well use the fields as I outlined (concatenated in the literal HTML).

                           

                          If you have the ability to create the HTML and use "placeholder", you can stick that text into a field (or variable) and use the Substitute() to change the place holders with the contents of the fields. I just gets more complex with the parent data and related children data.

                           

                          Did you read the article?

                          Beverly

                          • 10. Re: export to excel is less than optimal
                            beverly

                            What problem is there with the HTML tags being literal (hard coded) and the field contents being dynamic?

                            You can Export as HTML, but that is not going to give you everything you want (in the structure you want).

                            Beverly

                            • 11. Re: export to excel is less than optimal
                              beverly

                              More questions: what would you do to get this into Excel (in the stucture you need) if this were an "export" from a SQL database? or what would you do if you used web publishing with the data (from FM or SQL)?

                              Something has to be 'literal'.

                               

                              Beverly

                              • 12. Re: export to excel is less than optimal
                                user19752

                                There is a way to get merged value without Substitute() using GetLayoutObjectAttribute ( mergeField ; "content" )

                                but I haven't done it with portal data.

                                 

                                Peter, you can use a table to hold your HTML templates (mean not hard coded) with beverly's way.

                                1 of 1 people found this helpful
                                • 13. Re: export to excel is less than optimal
                                  beverly

                                  That requires naming your merge fields, right? or you mean a block of text with merge fields and the block is named. You are correct, getting the portal data still needs to be done!

                                  HTML still needs to be written (with merge fields) and then read back...

                                   

                                  • 14. Re: export to excel is less than optimal
                                    user19752

                                    Yes, it needs object name. I should have written it with quote for clarifying it is not field reference.

                                    GetLayoutObjectAttribute ( "mergeField" ; "content" )

                                     

                                    And I tested it that <<portal::field>> can get only first line of portal, if it is not in portal. GLOA() also should be on portal context.

                                    1 of 1 people found this helpful