1 2 Previous Next 16 Replies Latest reply on Apr 3, 2013 9:33 AM by philmodjunk

    Filtering Portal with date range, unexpected results



      Filtering Portal with date range, unexpected results



           I have a database used to analize test results of products.  In one layout we have a portal that lists the products by name, dispalying the number of test carried out on each product.   


      Product     Tests

           Widget A     24
           Widget B    12
           Widget C    19  

           I am using two global flieds to store date ranges for filtering perposes.  Within the portal I have the following formula under Filter:

           TESTS::Test Date ≥ PRODUCTS::SearchDateBegin and TESTS::Test Date ≤ PRODUCTS::SearchDateEnd

           This works fine, but the probelm is that once it finds any test on a product that has a date outside of the above paramenters, it removes the product from the portal, filtering it out.  This is not the result I am looking for.  What I would rather is all products stay listed, just the number of tests change.  Thus, if you have test a product 3 times in the last 3 months, say once per month, then if the user puts a date range from Feb - Mar, it would show a test count of 2 because one of the test dates is in January and fall outside the date search.  instead the entire product is being removed rather than just adjusting the count.  The count is performed in the TESTS table using a summary field set to count.

           Any ideas how to perform the date range search without eliminating the data, rather just updating totals in the portal.  If there are 300 products I want them to always display, just update totals.

           Hope this makes sense...




        • 1. Re: Filtering Portal with date range, unexpected results

               FileMaker is working correctly but not the way you want it to work.

               Enter layout mode.

               Select your portal and make a copy of it. (Select Duplicate or copy/paste it back to the layout)

               For one of the two copies of your portal, remove the portal filter expression so that all records show in the portal.

               In the other copy, keep the portal expression, but reduce the number of rows displayed to just one row. Put a "count of" summary field defined in your portal's table inside this single portal row.

               It will dispaly number of tests falling in the specified date range. You can set borders and fill colors for your one row portal to be invisible and then this one row portal will look like an ordinary field that displays the number of tests falling within the date range.

          • 2. Re: Filtering Portal with date range, unexpected results

                 Hi Phil-

                 Thanks for the quick response.  I just tried what you sugested and the problem I am having is that the sencond portal (on liner) floats above the orginal protal, thus when I scroll down it is actuall not imbedded on each line, it's one number floating above.  I have made sure the second protal is small enough to fit within a single line of the the original protal, but it still floats...

                 Any ideas

            • 3. Re: Filtering Portal with date range, unexpected results

                   You cannot place a portal inside a portal.

                   SInce this portal would display the same count (as far as I can tell from here) for all the records in the portal, why do you need it inside the portal row?

                   (By using a different approach, we can get that to work, but I need to understand more about what you are trying to do before I describe that approach.)

              • 4. Re: Filtering Portal with date range, unexpected results


                     Ok, that's what my understanding was - no nesting of portals.

                     So here is what I am trying to achieve.  I have a layout called "Analysis”.  This is where we go to review results of all testing.  Within this layout I have a tab called Testing Count.  In here I want a dynamic list of all products, tested or not.  Products are added every day so this list constantly changes and is from a table called “products”.  Next I have a field that counts how many products have been produced. From there I have a field to show the number of test against each product, whether it’s zero or 500. Next, I have a field called Frequency to calculate how often we are testing based on the number products produced; if we want to test every 100th produced of any given product we want to see if we are inside that parameter.  I currently have this setup in a portal and it works great.  But now we have decided that we want to search these same parameters based on a date range.  Thus, we could isolate these figures to a year, month, or even week.  This is where everything stops.  A portal in a portal would be great, but as we know it is not possible.  So what would you recommend for our situation?


                     Thanks again!!!


                     Product    Volume     Tests     Frequency

                     ABC           4544         43       106

                     CDE           233           1          233

                     FGH           0                0          0




                     Currently shows all products from date of inception, rather than date range.

                • 5. Re: Filtering Portal with date range, unexpected results

                       Am I correct that you want to see all products listed in the portal, but with zero for the statistics shown if nothing was tested in the specified date range?

                       I can deduce the following possible set of relationships for your current portal:


                       I don't know what you are basing your current layout where you have this portal so have called it "layoutTableOccurrence" here.

                       This implies the following matchfield details:

                       Products::__pkProductID = Tests::_fkProductID

                       I really can't tell at this point what details are in place for the relationship between the layout's table occurrence and Products. IT might use the X operator to list all products or it might specify, just a guess, a match based on a clientID so that all products tested for a given client are shown.

                       Does this look correct?

                       For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                       If so, do you really need the portal that you have now to list the products? A list view layout could be used on a layout based on products and then we don't have the portal inside a portal limitation.

                       Or we can modify the "filtering" used to make it part of the relationship between Products and Tests.

                  • 6. Re: Filtering Portal with date range, unexpected results


                         Hi Phil-


                         My relationships are exactly as you stated:


                         Products2:PK_productID -------X------Products::PK_productID --------------< Tests::FK_productsID


                         The “Analyses” layout is based on Products and the portal to list all of the products within this layout is based on Products2.


                         As for client ID we do not use this as all products are produced by us .

                    • 7. Re: Filtering Portal with date range, unexpected results

                           If that's what you have, you need to change your relationships. I would have assumed a layout based on Product2 and a portal based on products. And you'll need that structure to get a count of all related Tests records that fall within the specified date range.

                           I'll start with the simple way to set this up in relationships and then follow up with a more sophisticated way to handle date ranges that may evaluate more quickly than the simple approach:


                           Products::anyField X AllProducts::AnyField

                           AllProducts::__ProductID = Tests|DateRange::_fkProductID AND
                           AllProducts::SearchDateBegin < Tests|DateRange::Test Date AND
                           AllProducts::SearchDateEnd > Tests|DateRange::Test Date

                           Your layout would be based on Products, the portal would refer to AllProducts. These relationships give you the option of using aggregate functions such as count or sum to compute values from the set of related records in Tests|DateRange. You can also reference summary fields defeind in Tests|Date range to show the very same totals.

                           Option 2:

                           With large numbers of records, the use of inequalities amy slow things down to unacceptable levels. If so, you might try this relationship:

                           AllProducts::__ProductID = Tests|DateRange::_fkProductID AND
                           AllProducts::Datelist = Tests|DateRange::Test Date

                           The trick here is that DateList must be a return separated list of all dates from SearchDateBegin to SearchDateEnd. A recursive custom funciton (requires Filemaker 12 to install in the file) or a looping script must be employed to load DateList with the correct list of dates for this option to work.

                      • 8. Re: Filtering Portal with date range, unexpected results

                             Hi Phil-  

                             You assumption was correct, I listed the relationship wrong.  It is in fact Products:PK_productID ----X---Products2::PK_productID -----< Tests::FK_productsID.  Thus, I do not need to change the relationships.  But I need help with your first example.  I have entered that formula into the filter for the portal and I still have the same problem.  If I have tested product ABC for the past eight months say every two weeks.  If product ABC has a test date in any of is recortds that falls outside the date range, that product is removed from the list all together, so thus my portal may onyl show 5 products simply because they have no test dates outside the range.  This is not what I want to happen.  I need the list to always remain in full, showing all products.  The only thing that should change when I change the date range is the count.  So if the date range is for eight months it would show a count of 16 tests.  Then if I change the date range to 4 months, the product should still appear but read a count of only 8 this time.  Any ideas



                        • 9. Re: Filtering Portal with date range, unexpected results

                               There is no formula for the portal filter. What I am recommending does not use a portal filter expression. My last post describes the match fields to use for each relationship. So you do indeed need to modify the relationships as the match fields used are different.

                          • 10. Re: Filtering Portal with date range, unexpected results

                                 Sorry Phil- I'm lost then...What is this refering too, where do I set this up?

                            AllProducts::__ProductID = Tests|DateRange::_fkProductID AND
                            AllProducts::SearchDateBegin < Tests|DateRange::Test Date AND
                            AllProducts::SearchDateEnd > Tests|DateRange::Test Date

                                 I assume AllProducts refers to my Products2  or are these all new occurances I need to setup in relationships?

                            ------Tests|DateRange    What does this refer to?

                            Thanks again, sorry for my confusion...


                            • 11. Re: Filtering Portal with date range, unexpected results

                                   That expression describes the Relationship between an occurrence of Products and an occurrence of tests. It uses three pairs of match fields in the same relationship. You can double click the relationship line between the two to open up a dialog box where you can select all of the needed fields and operators.

                                   Using your names it would read:

                              Products 2::__ProductID = Tests::_fkProductID AND
                              Products 2::SearchDateBegin < Tests::Test Date AND
                              Products 2::SearchDateEnd > Tests::Test Date

                              Tests|DateRange is a naming convention where Tests is the name of the underlying data source table and |DateRange helps identify the nature of the relationship to make it easier to select the correct table occurrence when you are working with drop down lists of table occurrences in other parts of the FileMaker application.

                              • 12. Re: Filtering Portal with date range, unexpected results

                                     Thanks Phil -  I shook the cobwebs out and I understand where you are headed now.  I will try this now and will try not to bug you again!!!


                                     Thanks very much!!!


                                • 13. Re: Filtering Portal with date range, unexpected results

                                       Hi Phil-  Worked like a charm.  I see what you mean about option two being more efficient.  Perhaps when I play with custom functions a little more I will give it a go, plus a little guidence from you of course.

                                       As Always - Thank you for your help on this forum - Outstanding!!!

                                  • 14. Re: Filtering Portal with date range, unexpected results

                                         A looping scritpt also generate the needed list of dates in a text field.

                                    1 2 Previous Next