4 Replies Latest reply on Jun 10, 2013 6:27 AM by philperrin

    Value list from multiple fields to display related records in a portal

    philperrin

      Title

      Value list from multiple fields to display related records in a portal

      Post

           Hi Friends,

           I've looked around for related discussions on this topic, but haven't quite found what I'm looking for. Thanks in advance for any troubleshooting you're able to provide...

           My database has a table (People) that has two dates associated with each (Date1 and Date2). On a separate table (Memos), I display a portal of my People that uses a field (DateX) to filter which People are displayed in my portal. I'm having problems with my DateX field - it hasn't ever really worked. What I need it to be is a drop-down or pop-up of each of the dates available in Date1 and Date2 on the People table.

           For example, on my People table I would have 3 records with 5/1/2013 in Date1 and a different 3 records with 5/1/2013 in Date2. On my layout for the Memo table, I would like to then click on DateX and find 5/1/2013, select that, and then see the list of 6 People in the portal.

           I realize that over time, I might have too many dates populating here, so I would like to also have DateX only show dates that fall within the past year. But that is just icing on the cake.

           What I've done is try to use the List function to create a field containing all the dates from Date1 and Date2. This hasn't worked for me (only Date1 dates appear). Perhaps my relationships aren't set up correctly - currently I have a field on the People table (ListDates) that is the list function for the two date fields and that is related to DateX. Maybe I don't even need the separate table for Memo. Not quite sure.

           Thank you!

        • 1. Re: Value list from multiple fields to display related records in a portal
          philmodjunk

               Make sure that Memo::DateX, People::Date1 and People::Date2 are all fields of type Date when you check them in Manage | Database | Fields.

               If you define this calculation field, cDateList in People like this:

               List ( Date1 ; Date2 )

               and select TEXT as the result type, this field must be a stored/indexed calculation; you can define this relationship:

               Memo::DateX = People::cDateList

               And a portal to People will display any record where the date in DateX matches either Date1 or Date2.

               You can then define your value list to list dates from cDateList to get a value list of all dates.

               Limiting your value list to just dates for a given year will be tricky. Many of the options you might use for that will result in cDateList becoming an unstored calculation which then can't be used in the relationship needed for the portal, nor in the value list of dates. You may need to use a script with Replace Field Contents that sets and clears a field in each people record to include/exclude the Date1, Date2 values in/from the value list.

          • 2. Re: Value list from multiple fields to display related records in a portal
            philperrin

                 Thanks - This is ok, and I had it set up with the TEXT type, but the problem here is how Filemaker sorts textualized dates... they show up in an order like this:

                 1/15/2011
                 11/1/2012
                 4/4/2010
                 5/1/2013
                 5/10/2013
                 9/5/2009

                 And I get why this happens - they are no longer treated like dates (or numbers for that matter). Any advice on getting the list to display chronologically?

                 9/5/2009
                 4/4/2010
                 1/15/2011
                 11/1/2012
                 5/1/2013
                 5/10/2013

            • 3. Re: Value list from multiple fields to display related records in a portal
              philmodjunk

                   IF the text listed in the value list had this format:

                   2009/09/05

                   they would sort correctly into alphabetical order. It is possible to set up some calculations inside the list function to produce this text from each date, but you then will have issues when you select a date from the drop down. You would need a script trigger driven script (OnObjectValidate) to reformat the entered text into a valid date format before FileMaker's built in date validation can kick in and display an error message.

                   Hint:

                   Right ( "0" & Month ( dateFIeld ) ; 2 )

                   will return the needed leading zero for single digit months.

              • 4. Re: Value list from multiple fields to display related records in a portal
                philperrin

                     Thanks - Looks like a great mod for a different kind of project and I may implement this for a differen endeavor. Out of preference for simple solution, I'm going to use a calendar selector for the DateX field. This keeps things clean (ie, no list that would continually expand, and no trigger scripted validations), although at the expense of the user needing to know the date in question - which, in our case, should be ok.

                     Thanks for your help - have a great day.

                     Philip