1 2 3 Previous Next 32 Replies Latest reply on Jul 2, 2014 9:05 AM by BruceRobertson

    Why is a Powerful System like FileMaler Hamstrung so badly?

    garonson

      Title

      Why is a Powerful System like FileMaler Hamstrung so badly?

      Post

           See my graphic.  Access can do it.  Crystal Reports Can do it. InfoMaker can do it.  Oracle BI can do it.  Why can't FileMaker?  The Dreaded Multi-table Report!!!

      2014-06-26_6-21-59.png

        • 1. Re: Why is a Powerful System like FileMaler Hamstrung so badly?
          philmodjunk

               And why can't you do it?

               While the non-linear relationships make it more of a challenge, such a report is still quite possible in FileMaker.

               Care to upload a rough outline of how you want that report to look in FileMaker?

          • 2. Re: Why is a Powerful System like FileMaler Hamstrung so badly?
            garonson

                 Send me an email at gordona@visionconsultants.com and I will send FileMaker 13 file also.

            • 3. Re: Why is a Powerful System like FileMaler Hamstrung so badly?
              philmodjunk

                   No need for the file. Your example matches what I expected you to post.

                   I am assuming that this is a report that you will want to print or save as PDF.

                   With these relationships and this report specification, you can base the report layout on one of the two child tables (Ingredients or Preparation_Steps) and place a portal to the other table on your layout in a grand summary layout part. You'd make the portal many rows tall and set it to "slide up", "resize enclosing part". Use a portal for which ever related table is best suited to a simple tabular format for displaying the data and (hopefully) one where there is no need for slide/resize between the individual rows of the portal. Slide and Resize will eliminate the space taken up by unused portal rows.

                   This approach is not perfect but it is about a hundred times simpler to set up than the alternative, but sometimes you have to use that alternative approach. The alternative is to add a 4th "report table" to your database that is linked to Recipe_MK of your recipes table. This table has to have enough fields of the correct data types to receive all needed data from both Ingredients and Preparation_Steps. In some cases, you can use the same field to hold different data from different fields of each of these two fields. To produce your report, a script deletes all records from this report table and then imports the set of related records from each of the two "child" tables.

                   To produce the needed report requires a complex layout design of layered fields and conditionally formatted field labels that appear or disappear based on whether the record is for an ingredient or a preparation step--which is the key reason NOT to use this option if you can avoid doing so.

                   There is a third option where you use Save As PDF to stitch together reports based on different layouts, but since that option starts each new portion of the PDF on a new page, it's probably not the best option for this particular report.

              • 4. Re: Why is a Powerful System like FileMaler Hamstrung so badly?
                garonson

                     Thanks for your efforts and advise.

                     I will, though, repeat my comments.  I have worked with many databases and many report writers since mainframe days.  For a NOT CHEAP, POWERFULL System like FileMaker not to have inherent capabilities to go from "A" to "B" directly and not play games is simply ridiculous.  Embedded or child reports is a long-existing concept and is there for a reason.  Good systems SHOULD NOT waste user's time to develop cluggy workarounds that only wizards like you can dream up.

                      

                     I will try your method.

                     Gordon

                • 5. Re: Why is a Powerful System like FileMaler Hamstrung so badly?
                  philmodjunk

                       That would be methodS

                       In most cases, the first method is sufficient.

                  • 6. Re: Why is a Powerful System like FileMaler Hamstrung so badly?
                    garonson

                         PHIL.

                          

                         Still can't get report to work even when I try your alternate suggestion.  You are welcome to the file as it is now to show me how your secret works.  NEED AN EMAIL ADDRESS OR OTHER WAY TO SEND YOU THE FILE.  Again, mine is gordona@visionconsultants.com

                         BODY ELEMENTS WILL NOT RENDER.

                         Gordon

                    • 7. Re: Why is a Powerful System like FileMaler Hamstrung so badly?
                      philmodjunk

                           There is no secret and the method is clearly described in my original post. One way to make a file available to others here in the forum is to upload it to a sharing site such as Drop Box and then post the down load link here. No email address needed that way, just don't leave any sensitive data in the file when you upload it.

                           And if you want only me to look at the file, send the link to me in a private message.

                           

                                BODY ELEMENTS WILL NOT RENDER.

                           That would suggest that the elements you are trying to put in the body are not from the layouts specified table occurrence. Please note that the portal is not to be placed in the body layout part. It should be placed in either a leading or trailing grand summary layout part.

                           PS. All caps is the e-version of shouting at someone. No need to make my ears ring here! wink

                      • 8. Re: Why is a Powerful System like FileMaler Hamstrung so badly?
                        garonson

                             My filemaker 13 pro advanced file is at: http://www.dropbox.com/s/xbjndsp8c51kysd/Recipes%20Copy.fmp12

                             If someone wants to show me how to make the recipe report work I would be most grateful.

                             I created a parent/child table for gathering the report data and created a script that populates the parent/child.

                             The child records won't print.  The parent data that populates the header and footer works.

                             Gordon

                        • 9. Re: Why is a Powerful System like FileMaler Hamstrung so badly?
                          SteveMartino

                               I'm not quite sure what you are shooting for, but for fun I took a shot.  I assume you just want to print a recipe? If this is true, there's a whole lot of extra stuff on your database you just don't need, like the Print_Recipe_LineItems table, and the very long script

                               Changes I made:

                               1.New Layout for printing, with new Script attached to button.  Open script and take care of the print settings.

                               2. Your primary keys should be locked down, not allow editing.

                               3. You didn't allow creation of records thru your relationships.

                               4. Used sliding objects for everything in the print layout.  When you look at it in layout, or browse mode, it looks wrong.  When you switch to Preview Mode (or run script, after you set up your print options, you'll see the effects of the sliding.

                               5.  Removed the 4 buttons on the Recipe layout for "Add Ingredient", Remove Ingredient, Add Step, Remove Step.  They are not needed.  Added button to delete portal row, which will delete the related record. Tabbing into the next row then committing the record will add a new step (or ingredient).

                               6.  Fixed Next and Prev buttons to navigate.

                          https://dl.dropboxusercontent.com/u/71328624/Recipes%20Copy.fmp12

                               edit:  One odd thing I noticed, no matter what record I select to print, it shows the first record in preview, but prints the correct record.

                          • 10. Re: Why is a Powerful System like FileMaler Hamstrung so badly?
                            garonson

                                 Steve,

                                 I downloaded your revised file, but every time I tried to open, fileMaker crashed and closed.  Tried both 12 and 13, same both ways.

                            • 11. Re: Why is a Powerful System like FileMaler Hamstrung so badly?
                              garonson

                                   Hi Steve,

                                   1)  I find users would rather have the buttons rather than figure out the ins and outs of how to make a portal work.  I see this as a choice in methodology.

                                   2) Finally got your file to open in FMP12.  I knew I could print with Portals, but every class and the FileMaker manual highly suggests that portals not be used for printing.  I understand the slide up feature as it is the same in most report writer software,  If Portals are the BEST way to go, why does all training material, suggest otherwise?

                                   3) the report table and script to load it was the idea of several folks who provided answers on how they would go about it.  This proves to me that there is a great deal of varied methodology in FileMaker application, good and not so good.

                                   After many years with other systems, I find that FileMaker attacks database design from a whole new perspective and methodology.

                                   Thanks for your time and assistance.

                                   Gordon

                              • 12. Re: Why is a Powerful System like FileMaler Hamstrung so badly?
                                SteveMartino

                                     I'm newer to FM (little over 2 years now)  I found FM much easier to build/work then Access. You're original way may be the better way, I've seen something similar on invoice databases, I just don't understand it enough to make it work, or to know if it's the 'right' way to do it.  I also spend a lot of time trying to figure out things myself (online research, books, etc.) only to end up asking the question here and having the great and wonderful Phil sum it up and answer it easily & quickly.  Posting screenshots and sample files makes the process of figuring out what you have, how it's set up, and what you want it to do go much smoother.  Most people here enjoy helping to solve problems, especially someone like me who is a FM enthusiast, and has received so much help from Phil and the community.  Plus any problem solved helps all who read the forum.

                                • 13. Re: Why is a Powerful System like FileMaler Hamstrung so badly?
                                  garonson

                                       Steve,

                                        

                                       Happy to compare notes anytime.  You are welcome to email me at above address for non-public items.

                                  • 14. Re: Why is a Powerful System like FileMaler Hamstrung so badly?
                                    garonson

                                         Steve,

                                         The version that you sent me will not work with long text strings.  A Portal will not auto-adjust for different length strings.  Therefore only parts of the menu items display, not full text.  Reports from a schema with more than one child table do not work in Filemaker without bringing everything back to a flat file (report table).  To me this is a major shortcoming of Filemaker.  It has great relational table structures but is sadly deficient in producing equivalent output.  You should not have to flatten relational data to make it reportable.  I have said this to the forum and directly to filemaker on a number of occasions over a number of years.  My statements have met only deaf ears.  All modern data mining software (other than Filemaker) recognizes the need for relational reporting.  As an Oracle BI and Sybase Infomaker instructor, I know that child or embedded reporting is a well recognized methodology.  I can setup ODBC and access tis Recipe database with several report writers and with little work make a great looking Recipe report.  There is simply no good reason why I cannot do this in a powerful relational system like filemaker.  It is wrong, wrong, wrong!

                                         Gordon

                                    1 2 3 Previous Next