3 Replies Latest reply on Dec 29, 2014 4:33 PM by philmodjunk

    Testing records related to filtered records in portal



      Testing records related to filtered records in portal


      A problem that seems rather simple when I detail it here, but I can't find the way to get FM to do what I want it to do!

      I have three tables:

      Location       Date     Event

      The common key is __pk_DateID

      In a layout based on Location I have a portal listing all relevant dates

      Within that portal I added a button / script to take me to any related Events for that date. Works fine.

      I would like to use conditional formatting on that button to show if certain criteria on each Event record is true. (Specifically, Confirmed, a Y/N field in Event).

      The conditional formatting works when there is one event on a given date, but doesn't work if there are >1 record, as it is not testing the entire table, simply the first related record it finds. As there is no test for it, if the 2nd record in the Event table isn't confirmed, my conditional formatting isn't indicating that fact.

      I have come up with a variety of ideas on how to handle this, but they all seem far more complicated then I think they should be. Therefore I am reaching out here for suggestions.....







        • 1. Re: Testing records related to filtered records in portal

          There is more than one way to link three tables in relationships--even when you use the same value, dateID to link the three. Exactly how the three tables link to each other here is a critical detail. Keep in mind that I only know what you've told me about your database--which is very little so far, but linking a location by date ID instead of location ID seems a bit odd here if the location record is supposed to show the location where the event will take place....

          You are correct that unless you use special aggregating functions, a calculation that references data in a related table will only refer to data from the "first" related record. There are a number of different approaches to deal with that, but it's not yet clear which would be the best approach given the limited info about the basic design and intended functions of your database.

          • 2. Re: Testing records related to filtered records in portal

            Thank you for your reply, Phil. Indeed, there is much missing from my original message!

            I have linked by date because that is the common denominator. The layout actually lists a hotel used on our tours. (Location) and is a primary layout for our interface to everything to do with hotels..

            We use some hotels a few times in a season, some many times - the portal displays the date(s) of stay and the status of that specific reservation - booked/confirmed, high/low season, etc.

            The Event entry is related to the date rather than the hotel - as it happens on the same day that we have an overnight reservation at Hotel XX. It could be a ferry crossing (now that is something to think about while there is one burning off the coast of Greece...) or a walking tour of a city, or the fact that we are taking the entire group to a special event.

            I am not interested in having Event detail show up in this layout - simply the fact that any Events taking place on this given day are 'in order' based on specific criteria. (booked/confirmed).

            In the screen capture below you can see the layout of the current portal.

            The fields Guide Note, Limited Rooms, Off-Site Restaurant all have pop-ups that detail the 1/1 relationship with a relevant note for that day/item. If there is an entry in any of them, and all is OK with that entry the field will be green (similar to those on the left). A problem results in a red box.

            What actually sits in the portal row for the Tour excursion is a macro button, running a script to find the related record(s) in the TourExcursion table. Click on the button and, if no records are found, it returns you to the layout noting that there are no excursions/events for this day. If there are, it will open an Excursion layout with the related records in a found set.

            I am trying to conditionally format the Tour Excursion macro button to do the same  - green, all is ok with all related Excursion/Event records for that day. If there is a problem with any the button should be red.


            • 3. Re: Testing records related to filtered records in portal

              Is TourExcursion a forth table that you omitted from your original post or another name for an "event" record?

              Am I correct that you have a button outside of the layout that needs to be one color if there are no problems in any of the portal rows?

              If so, here is a way to test to see that a given value, "x", is not found in the field "status" of any record in the portal:

              IsEmpty ( FilterValues ( List ( PortalTable::Status ) ; "x" )

              This will return True only if the value "x' is not a member of the list of values returned by the list function. This list function is an example of a special function that can aggregate values from multiple related records.

              But your thread title describes this as a Filtered portal. That could be a problem here. If you have defined a portal filter expression in portal setup to limit what records appear in the portal, this calculation won't work as it will reference all related records, not just those that appear in the filtered portal.