11 Replies Latest reply on May 10, 2013 9:24 AM by GuyStevens

    Structure question (Access to FMP12)



      Structure question (Access to FMP12)


           So... I'm an Access guy, but I have a job repairing a Filemaker database. No problem, I've been going well so far, I actually like Filemaker much better than Access...  It really is much nicer.  Most of the problems were simple (objects with the wrong association, cosmetics, needing return buttons, minor changes to scripts, etc)

           But...  Now I'm into the queries part of the repair and I'm having a structure problem...  And I think it's because I'm thinking like an Access guy and not a Filemaker guy, so I just need a little help here.

           There are a number of reports (Layouts)  that are getting their data from a table (Which I know which table they are gathering the data from), but I have no idea how it's querying the data... In Access, there would be a Query associated with the form, I could change the query and get different data on the form... but here the Layout just fires off all by itself.  I can't figure out how to find where the layout is getting its data FROM...

           There is nothing in the "Find" mode that I can see... and I have no idea how to tell if a script is running on a layout (In fact, it seems that all scripts are run by all layouts?!?  Not sure how this works).



        • 1. Re: Structure question (Access to FMP12)

               You've hit on a key difference between Access and FileMaker. Access forms are "hardwired" to a specific chunk of SQL code that queries the database and returns a RecordSet. To get the form or report to display a different set of records, you have to use VBA code to "rewire" the data source by creating and executing a new SQL query for the form/report's record source object.

               FileMaker layouts are hardwired directly to a specific table via the Table Occurrence named in Layout Setup | Show Records From. Instead of a RecordSet controlled by a SQL query, it references the table occurrence's "found set" a much more dynamically  flexible object--but roughly the same idea.

               FileMaker then provides a number of tools controlled by a script or by the user selecting options from the records menu for determining what records are in that found set.

               Entering find mode, entering criteria into one or more fields and then performing the find is the most commonly used method of pulling up a found set of records for a given layout. You can research this more in help, but the basic method is just that simple. You click the Find button in the tool bar or select one of the other methods for entering find mode. Say you want all contact records with "CA" in the state field.  Enter CA in the state mode and click the Perform Find button in the toolbar or press the enter key to perform the find. You now have a found set of records where CA is found in the City field.

               Sort Records, Omit records, show all records, find matching records, quick find, show omitted only and Go To Related Records are all other methods for manipulating the found set for a given layout.

               In many ways, it's as though you used Manage | Database | Relationships to set up the portion of your SQL query where you use the SELECT and JOIN keywords, but leave out any WHERE or ORDERBY clauses as the user instead specifies that criteria via one of the above named methods.

               And all of the above methods can be done via scripts so if you are clicking buttons to go to a given report, there's a good chance that the script associated with that button is performing a find or using one of the other methods to produce the found set found when the layout for that report pops up.

               This means that the casual user of your database can manipulate the found set any number of different ways unless you the developer prevernt them from doing so--unlike Access where you have to specifically design for user initiated searches.

          • 2. Re: Structure question (Access to FMP12)

                 I get it! That makes sense.  In this case, it's an application, so everything is going to be going through the scripts (Which is really easy to reason out) :D  Thank you!  So, where would I go to find out which script is handling the data going to this report?  I'm noticing a "checkmark" next to certain scripts in the Manage Scripts form.  Would this be the scripts fired off on load of the report/layout?

            • 3. Re: Structure question (Access to FMP12)

                   If there's a check box next to the script name, it will be visible in the scripts menu when you pull it down while in Browse mode.

                   To figure out which script is performed by a given button, enter layout mode and double click the button. In some layouts, this can be complicated by grouping the button with other layout objects or overlaying other objects on top of the button object, but once you have button setup open, you should see that the perform script option is selected for the button if it performs a script. You can then click specify to see the name of the script and where it is located inside manage scripts.

                   If you are using FileMaker 11 or or newer, you can then select "Edit" from the gear drop down to open the script in the script editor in order to review and modify the script.

              • 4. Re: Structure question (Access to FMP12)

                     Scripts can also be performed by script triggers. Either on a field event (like OnObjectModify) or on a layout event (like OnLayoutEnter).

                     To find the field related script triggers go to layout mode and right click a field and choose 'Script Triggers'.

                     Layout related script triggers can be found by going in layout mode and then choosing the menu 'Layout" - "Layout Setup" - "Script triggers"

                     A field that has a script trigger attached to it has a red asterisk in it in layout mode.

                     If you go to "File" - "Manage" - "Layouts" you can also see reed asterisks for layouts that have script triggers attached to them.

                • 5. Re: Structure question (Access to FMP12)

                       AHA! So, let's say I have a layout.  It has all of my fields on it at the moment and is pulling all 56,000 records.  I add a script to this layout that then performs the find and refines the find and that gives me a "place" to put the criteria for culling this database down.

                       This is a complicated situation.  My client wants a report EXACTLY like this other report, except she wants it to have the LAST two weeks instead of the next week and she wants it to be only records where a certain field is blank (And the banner in red).  I duplicated the report, but naturally, it does the exact same thing as the cloned report... so this has been a nightmare for me... I spent the entire day fighting to find how to change the criteria on the report, I ended up looking pretty sad.

                       So... I'm "answering" my own question here in this thread... should I be posting a new thread for all of these revelations or is it okay to continue to reply here?

                  • 6. Re: Structure question (Access to FMP12)

                         The first question is: Where is the script that performs the find?

                         Is it triggered when you click a button to go to that layout?
                         Is it triggerd by a layout script trigger?
                         Are there buttons on the report to give you different kinds of reports? (Last week, last two weeks, last month, ...?)

                         You could do this for instance:

                         Create an enpty layout that's like a reports screen; On there you put a bunch of buttons or text fields that you turn in to buttons. Like:
                         - Sales Report for Last Month
                         - Sales Report for The Last two Weeks
                         - Sales Report for the Last Week.

                         Every line becomes a button and runs a script.
                         A script could look like this:

                         Go to Layout [The layout that will become your report; Probably a list layout, with sales records.]
                         Enter Find Mode [Uncheck the Pauze Field ]
                         Set Field [ Sales::SalesDate ; Get (CurrentDate ) - 14 & "..." & Get ( CurrentDate ) ]
                         Set Field [ Sales::EmptyField ; "=" ]
                         Set Error Campture [On]
                         Perform Find
                         Sort Records [You can sort if you have created SubSummary fields]

                         Voila. This find will go to the report layout. Then perform a find for the records with a date between today and 14 days ago. Then check for the "Empty Field" fields that are empty. A field that's empty can be found using "=" any value can be found using "*".

                         Then your find is performed, your records are sorted et voila.

                         That's how that works.

                         You can also add buttons and fields on your layout that let you perform different kinds of finds.

                         Then another script can be made to bring yo to the same layout but performs another find. Like last months records etc.

                         I hope this helps.



                    • 7. Re: Structure question (Access to FMP12)

                           About the "Banner in Red" Is that a value? Where you have a "Benner" field and it has values like "Red", "Blue", "Green", ...
                           Because that you can find.

                           Also, don't clone the report layout. Rather have another script triggered but go to the same layout. Perform a different find and you have a different report.

                           You can even dynamically change the title of the report.
                           - Last weeks sales
                           - Sales from 1-1-2013 untill 1-2-2013
                           - etc.

                      • 8. Re: Structure question (Access to FMP12)

                             You may also find this tutorial on summary reports useful: Creating Filemaker Pro summary reports--Tutorial

                        • 9. Re: Structure question (Access to FMP12)

                               Phil:  Thank you!  I'll look it over.

                               DaSaint: I was using it as an illustration:  she wants the report to be exactly the same as the other report, the only difference was the title of the report says "Exception report" instead of "Shipping Report" and it is in red (So you can visually tell instantly you are in the correct report).  And the criteria are not about what shipped, but what DIDN'T Ship.   Looking this up on line I get a lot of "Use the Find function" or "Use the wizard" which I can't do if I'm modifying an existing report.  I think I'm fuzzy, but I think some hands on will clear me up.  Thanks guys! You guys are awesome! :D

                          • 10. Re: Structure question (Access to FMP12)

                                 Here's a thread with scripted find examples that you may find helpful: Scripted Find Examples

                            • 11. Re: Structure question (Access to FMP12)

                                   You can do this many different ways. Either you create two different layouts that look different. Or you use the same layout and change the title and the background color using conditional formatting and the setting of a field or variable.

                                   The problem is I don't know how it's done now, how do you get to that report?

                                   You create a layout that displays the records the way you want but then it's up to a find to specify the records that are shown.

                                   That find can be triggered by button or by entering a different layout.

                                   I hope this example file illustrates a little what I mean: