3 Replies Latest reply on Jul 8, 2012 8:32 AM by philmodjunk

    Help with Lookup, Relationship setup and conditional formatting



      Help with Lookup, Relationship setup and conditional formatting


      Hello, I'm new to FM and I'm stuck on a lookup problem.


      I have two tables, Calendar and Daily Visits.

      The Calendar layout has in 4 fields shown under each day. For example, let's say the first day of the month is a Sunday the 1st. It has the following fields: A1, B1, C1, D1. Monday the 2nd has A2, B2, C2, D2,Tuesday the 3rd has A3, B3, C3, D3....and so on for the remaining days of the month.

      Daily Visits has records from four different types of nurses that visit a patient. Each nurse visit is a separate record.

      The two tables are linked via an ID field.

      What I'm trying to do is when a particular nurse visits a patient on a given day, the respective A,B,C,D box for that day is highlighed using conditional formating. So if Nurse A visited patient A on Mon, Wed, Fri, box A2, A4, A6 under the visited date would be highlighted.

      What I have now works but only the first visit works. 


      Thanks for your help!

        • 1. Re: Help with Lookup, Relationship setup and conditional formatting

          I strongly suggest that you redesign your calendar table. (And please note that a Table and a Layout may have exactly the same name, but they are not the same thing.)

          I suggest that you have one record for each day rather than one record for each month. This will lead to a much more flexible design that will make it much easier to set this up as well as many other tasks you may need to do with this database.

          Here's a very simple calendar demo file you may want to take a closer look at:  https://www.dropbox.com/s/e8d03xvwe8vtz85/Calendar.fp7

          • 2. Re: Help with Lookup, Relationship setup and conditional formatting

            Many thanks Phil for your reply and suggestion.


            Unfortunately for the purpose of this Calendar style setup, I couldn't use the standard typical setup such as from your attached sample. My Calendar layout is mainly to be used as a visual color coded layout than the typical calendar we are all familiar with.

            I started to go through the functions Help screen and was able to find a solution for what I wanted to do and it worked out perfectly.

            I created a calculations field in my Calendar table to collect all records pertaining to the patient with a LIST function. I now have a listing of all unique visit date records from the Daily Visits table. Next I used a FilterValues formula along with IsEmpty for the Conditional Formatting for each of the box. The sample formula below:

            IsEmpty(FilterValues ( Calendar Worksheet::DailyVisitListing; Self & Calendar Worksheet::FD14 & Calendar Worksheet::_fkPatientID & Calendar Worksheet::_fkCarePeriodID)) = 0

            What I've basically done is have each box using a preset unique visit code to check and see if it is listed in Calculated Visits field. If it is found in the calculated field, highlight itself to the color I've preset it.

            I've attached a picture of the calendar layout and a sample of the color scheme I was trying to create. Hopefully others will find this useful.

            Note that color highlights you see there for the four boxes under the dates (Week 2 Friday and Week 2 Saturday) is just two sample color scheme I'm testing out. Single color vs multi-color scheme.

            • 3. Re: Help with Lookup, Relationship setup and conditional formatting

              I see nothing here that cannot be adapted to the structure of the demo file. But keep in mind that a screen shot of a layout, taken in browse mode conveys very little information about the structure of your file and you haven't really laid out in this thread the other tables in your system and how they are related.

              The "Day boxes" in my demo file are filtered portals, there's no reason why the portal's filter can't be used filter for a specific subset of records such as all the "visit" records for a specified client.