7 Replies Latest reply on Nov 14, 2012 7:21 PM by comment

    Script to validate rows and highlight them through conditional formatting

    gktummala

      Scenario - Classroom assignment for course offerings -

       

      Table A

       

      Course IDMeeting DaysStart TimeEnd TIme Classroom
      CS101M,W,F9.00 AM9.50 AMRoom 1
      STAT101M,W,F11.00 AM12.00 PMRoom 1
      CS102T,R9.00 AM10.30 AMRoom 2
      Stat102T,R9.30 AM10.30 AMRoom 2
      Engl101M,W,F9.00 AM9.50 AMRoom 3
      Engl102W9.30 AM10.30 AMRoom 3

       

      Users do a classroom allocation for the course offerings and generate the above room allocation report towards the end. I need to write a script in FM to flag a course if it overlaps with another day and time and highlight the row with a differnet color for correction. All the data exists on a single data table. In the above example, row 3 and 4 has a time overlap and like wise, row 5 and 6 has a time overlap.

       

      Can please anyone advice me on how write a solution for this scenario on filemaker.

       

       

      Thank you!

        • 1. Re: Script to validate rows and highlight them through conditional formatting
          comment

          gktummala wrote:

           

          I need to write a script in FM to flag a course if it overlaps with another day and time

           

          You need a relationship, not a script. Usually, the relationship (using two occurrence of the same table) would look like this:

           

          Bookings::Classroom = Bookings 2::Classroom

          AND

          Bookings::Date = Bookings 2::Date

          AND

          Bookings::StartTime < Bookings 2::EndTime

          AND

          Bookings::EndTime > Bookings 2::StartTime

          AND

          Bookings::BookingID ≠ Bookings 2::BookingID

           

          Then you would test for the existence of a related record in Bookings 2 - that would be an indication of a conflict.

           

           

          In your case it's slightly more complicated because of multiple values in the MeetingDays field. You need a calculation field to convert the comma-separated list into a return- separated one. Then use this calculation field as the matchfield instead of Date above.

          • 2. Re: Script to validate rows and highlight them through conditional formatting
            mbraendle

            The general procedure as Michael describes is ok. However, I think that line 3 and 4 of the relationship should be

             

             

            Bookings::StartTime > Bookings 2::StartTime

            Bookings::StartTime < Bookings 2::EndTime

             

            and line 5 can be left out, because of the BookingIDs are the same, line 3 does not hold.

             

             

            Line 3 and 4 should cover the following situations:

             

            a)

             

            Booking         S-------E

            Booking2   S-----E

             

            b)

             

            Booking         S-------E

            Booking2   S-----------------E

             

             

            It works also in the other direction, because the records of booking and booking2  will be reversed:

             

            S-------E

                  S---------E

             

             

            S-----------------------E

                  S-------E

             

             

            In other words: One just needs to check that one of the beginnings is in the confines of the other time range, then there is overlap.

             

            Message was edited by: MartinBraendle  (I should not hurry!)

            • 3. Re: Script to validate rows and highlight them through conditional formatting
              comment

              MartinBraendle wrote:

               

              However, I think that line 3 and 4 of the relationship should be

               

               

              Bookings::StartTime > Bookings 2::StartTime

              Bookings::StartTime > Bookings 2::EndTime

               

               

              I don't see how that would work. In both your examples,

               

              Bookings::StartTime > Bookings 2::EndTime

               

              is False, so they would not be flagged as conflicting.

               

               

               

              ---

              However, I see that my previous post had the comparison signs reversed - I have edited and it should be correct now.

               

              Message was edited by: Michael Horak

              • 4. Re: Script to validate rows and highlight them through conditional formatting
                mbraendle

                Yes, made a mistake in a hurry (it's late bedtime here, copy-paste error) and immediately (20 seconds after rereading) corrected it after I had posted.

                 

                It's now

                 

                Bookings::StartTime > Bookings 2::StartTime

                Bookings::StartTime < Bookings 2::EndTime

                 

                in my post above. That should work.

                 

                Unfortunately, the e-mail already went out, so it was false there.

                • 5. Re: Script to validate rows and highlight them through conditional formatting
                  comment

                  MartinBraendle wrote:


                  Bookings::StartTime > Bookings 2::StartTime

                  Bookings::StartTime < Bookings 2::EndTime

                   

                  Booking 1    S---------E

                  Booking 2           S---------E

                   

                  This will flag Booking 2, but not Booking 1.

                  • 6. Re: Script to validate rows and highlight them through conditional formatting
                    mbraendle

                    "However, I see that my previous post had the comparison signs reversed - I have edited and it should be correct now.

                     

                    Message was edited by: Michael Horak"

                     

                     

                    Which was exactly the reason why I was confused, thought about a solution and sent a reply ...

                     

                     

                    This will flag Booking 2, but not Booking 1.

                     

                    Are we talking about booking records or booking TOs?

                     

                    The relation works in both directions and comparison runs through all combinations of the record pairs (e.g. for records 1-3: 1,1; 1,2; 1,3; 2,1; 2,2; 2,3; 3,1; 3,2; 3,3 - Think of the problem as of a n x m matrix of comparisons. If one would just calculate the lower or the upper triangle, one would have a huge problem with all inequality operators, because these are not commutative.)

                     

                    Which, in the example above, will give a match for the record combination 2,1.

                    Whether now record 2, record 1 or both are marked then depends on the context in which the existence of the related record will be calculated. There directionality will be introduced.

                    Which would require in my case 2 calculations, and in your solution only one calculation, but the additional requirement of different IDs of the records (line 5). Which makes your corrected solution more elegant.

                    • 7. Re: Script to validate rows and highlight them through conditional formatting
                      comment

                      MartinBraendle wrote:

                       

                       

                      This will flag Booking 2, but not Booking 1.

                       

                      Are we talking about booking records or booking TOs?

                       

                      I was speaking about records here.

                       

                       

                      Note:

                      Rather than looking at the 4 possible forms of overlap, I'd suggest looking at the possible forms of not overlap - there are only 2 of those.