1 2 Previous Next 18 Replies Latest reply on Oct 28, 2013 1:48 PM by BenjaminDestrempes

    Filter portal records based on date range

    BenjaminDestrempes

      Title

      Filter portal records based on date range

      Post

           Hi,

           I am trying to get my head around a portal that would filter records based on date ranges using 2 different tables in its calculations. Here is my current situation:

           I have 3 tables: Consultants, Consultants_Tasks and Projects.

           When I create a new project, I assign it consultants and tasks with start dates and end dates using the Consultants_Tasks table to manage the many to many relationships.

           The Consultants_Tasks table is linked to Projects with the Project_ID foreign key and to Consultants with the Consultant_ID foreign key.

           I would like to add a portal to the Projects layout that would allow me to specify a date range (Project_Start_Date and Project_End_Date) and would display all the records from the Consultants table but filter out the consultants who would have a task assigned anywhere between these two dates in the Consultants_Tasks table. I just have no idea how to proceed.

           I am using FileMaker Pro Advanced 12 on Windows 7.

           Any help will be appreciated!

           Regards,

           - Ben

        • 1. Re: Filter portal records based on date range
          philmodjunk

               You appear to have these relationships:

               Consultants---<Consultants_Tasks>-------Projects

               If you use additional table occurrences of Consultants and Consutants_Tasks, you can get the needed portal of available consultants:

               Projects----<Consultants|All---<Consultants_Tasks|All

               Projects::AnyField X Consultants|All::AnyField

               Consultants|All::cDateList = Consultants_Tasks|All::cDateList

               Make your portal a portal to Consultants|All with this portal filter:

               IsEmpty ( Consultants_Tasks|All::StartDate ) //any field in the table that is never empty will work.

               the two cDateList fields are calclation fields that return a list of dates from Start Date to end date. A custom function or a looping script can create that list of return separated dates. If you have FileMaker Advanced, I can dig up a custom function for that.

          • 2. Re: Filter portal records based on date range
            BenjaminDestrempes

                 I would very much appreciate you digging up a custom function or a script, my knowledge in the area is very limited as you can see.

                 Thanks for the help.

            • 3. Re: Filter portal records based on date range
              philmodjunk

                   But which can you use? Do you have FileMaker Advanced? If you don't, you won't be able to add a custom function to your solution and you'll need to go with a script.

              • 4. Re: Filter portal records based on date range
                BenjaminDestrempes

                     I use FMPro Advanced 12. Would a custom function also work with instant web publishing?

                • 5. Re: Filter portal records based on date range
                  philmodjunk

                       There won't be any issues with a custom function and web publishing. The custom function will compute a value in a field or in a script step and those will function the same whether from a FileMaker Pro or a Web Browser client.

                       Here's a custom function I've used for getting a range of dates as a list of the same:

                       //DateRangeList ( DateStart ; DateEnd )
                       //
                       //Returns a list of return separated dates from DateStart to DateEnd
                       //
                       //DateStart  :   Date--first date of list
                       //DateEnd    :   Date--last date of list
                       //
                       Case ( IsEmpty (DateStart ) ; "" ;
                                  IsEmpty ( DateEnd ) ; DateStart ;
                                  DateStart > DateEnd ; DateStart ;
                                  DateStart = DateEnd ; DateEnd ;
                                  List ( DateStart ; DateRangeList ( DateStart + 1 ; DateEnd ) )
                                )

                       Lines starting with // are comments. You can copy and paste this directly into the function editor, but then you have to use the text in the first row to name the function and the parameters used.

                       Your cDateList field might use this function like this:

                       DateRangeList ( TaskStart ; TaskEnd )

                       with Text selected in the Result Type drop down.

                  • 6. Re: Filter portal records based on date range
                    BenjaminDestrempes

                         I added the cDateList text field to my Projects layout but it seems I am making a mistake with the relationships. The field only works if I set it to display Consultant_Tasks::cDateList and then, obviously, it only displays the date range for that particular project. Setting it to Consultant_Tasks|All:cDateList returns nothing.

                         I interpreted 

                    Projects::AnyField X Consultants|All::AnyField

                    as being really any field so I added a k_Project field to Consultants|All that links to Projects::ID but that field is empty because obviously, a Consultant may be assigned to multiple Projects. Is that what's causing the issue?

                    I also linked Consultants|All::cDateList to Consultants_Tasks|All::cDateList, both of which are calculations with 

                    DateRangeList ( TaskStart ; TaskEnd )

                     as a text result.

                    • 7. Re: Filter portal records based on date range
                      philmodjunk

                           Consultant_Tasks::cDateList

                           and

                           Consultant_Tasks|All:cDateList

                           refer to the same field from the same data source table. If one shows data and the other is empty, the relationship is why. Better check your relationships for both projects to Consultants|All and from Consultants|All to Consultant_Tasks|All. If either has an issue, it could explain why the field is empty.

                           This field will not list all the dates of a project. It will list all the dates from the start date to end date of a specific task. But you wouldn't normally put this field directly on your Projects layout. Since the value is different for each task record, you'd normally use a portal so that you can see this data for all the tasks assigned to a given project.

                           And please note that you have a second cDateList defined in the Consultants table. That one has to be set up just right to handle the fact that you want this list to be the Project's list of Dates to match against the second list of dates. Typically, this requires defining two global date fields with a script that updates them with the current project's start and end dates. Then Consultants::cDateList computes a list of dates from the dates in those two global fields.

                      • 8. Re: Filter portal records based on date range
                        BenjaminDestrempes

                             Ok, cDateList returns the dates related to a specific task. I added a portal to Consultants|All with the IsEmpty ( Consultants_Tasks|All::StartDate ) filter and have added the DateStart and DateEnd fields to the Projects table, however the portal remains empty. Is this caused by a relationship issue?

                        I am also a bit puzzled by the fact that the dates returned are related to one specific task. I am looking to create a portal that will return all consultants that do not have a task assigned on any project between the project's start date and end date, not only on this particular project.

                        • 9. Re: Filter portal records based on date range
                          philmodjunk

                               There's a second date list that returns all the dates for a given project. How did you set that one up? It's named Consultants::cDateList in my previous posts.

                               It's by comparing the list of dates for the project to the list of dates for each project task that the portal filters the consultants down to only those that do not have any existing tasks that conflict with the date range of the current project.

                          • 10. Re: Filter portal records based on date range
                            BenjaminDestrempes

                                 Hi,

                                 Sorry for the late reply, didn't get any time to work on this for a while.

                                 Consultants::cDateList is set up as a calculation: DateRangeList ( Consultants_Tasks|All::Task Start Date ; Consultants_Tasks|All::Task End Date )

                                 So far I have managed to get a portal that shows all tasks related to a single project but I am missing is a way to make a portal that will list tasks related to all projects, like you said in your last post. I am a bit clueless as to how I can achieve this though.

                            • 11. Re: Filter portal records based on date range
                              philmodjunk

                                   A portal that lists all records in a given table can be based on a relationship that uses the cartesian join operator: X

                                   LayoutTable::anyfield X PortalTable::anyfield

                                   You can drag from any field in one table occurrence to any field in the other, then double click the relationship line and change the default = operator to X.

                              • 12. Re: Filter portal records based on date range
                                BenjaminDestrempes

                                     Oh, that's good to know, I never noticed there could be different types of relationships. I got the portal to display the tasks of all consultants along with their respective start and end dates, but trying to filter the portal with the Projects::DateStart and Projects::DateEnd fields empties out the portal, no matter which dates I enter.

                                     (Consultants_Tasks|All::cDateList  > Projects::DateStart) and (Consultants_Tasks|All::cDateList < Projects::DateEnd) is my filter.

                                • 13. Re: Filter portal records based on date range
                                  philmodjunk

                                       You can't use an inequality to compare a text field full of dates to a single date field. The data types are too different.

                                       If you go back and review my very first post, you'll find that I recommended this portal filter:

                                       IsEmpty ( Consultants_Tasks|All::StartDate ) //any field in the table that is never empty will work.

                                  • 14. Re: Filter portal records based on date range
                                    BenjaminDestrempes

                                         Ok, I tried again with the IsEmpty filter but unfortunately, the portal still displays all consultants, regardless of whether they have a task assigned between Projects::DateStart and Projects::DateEnd.

                                         My relationships look like this:

                                         Projects::AnyField X Consultants|All::AnyField

                                         Consultants|All::cDateList = Consultants_Tasks|All::cDateList

                                         Consultants_Tasks|All::cDateList is a calculation: DateRangeList ( DateStart ; DateEnd )

                                         The DateRangeList function is:

                                         
                                              //DateRangeList ( DateStart ; DateEnd )
                                         
                                              //
                                         
                                              //Returns a list of return separated dates from DateStart to DateEnd
                                         
                                              //
                                         
                                              //DateStart  :   Date--first date of list
                                         
                                              //DateEnd    :   Date--last date of list
                                         
                                              //
                                         
                                              Case ( IsEmpty (DateStart ) ; "" ;
                                         
                                                         IsEmpty ( DateEnd ) ; DateStart ;
                                         
                                                         DateStart > DateEnd ; DateStart ;
                                         
                                                         DateStart = DateEnd ; DateEnd ;
                                         
                                                         List ( DateStart ; DateRangeList ( DateStart + 1 ; DateEnd ) )
                                         
                                                       )
                                         It seems I am only missing the second cDateList, Consultants|All::cDateList, which you said should be set up with 2 global fields and a script to update them with the current project's dates. How would such a script look?
                                    1 2 Previous Next