Tip: Flag Conflicting Records

Support, Questions and Suggestions for the FM7 Version of CC Calendar.
SeedCode Staff
SeedCode Staff
Posts: 2764
Joined: Thu Nov 20, 2003 11:01 am
PostPosted: Thu Dec 02, 2004 4:58 am
Overview

You can easily modify CC Calendar to flag appointments which conflict with each other, even if their times only overlap partially. The trick is to build a new relationship in CCCalendar that will be true if the two appointments share enough criteria to be considered in conflict. Once that relationship is in place you can choose to display the conflict any number of ways (see "Going Further" below).

The example below is based on CC Calendar Pro and assumes that all an appointments attributes must match for a conflict to be true. That is, the appointments must share at least one of the same Users, be of the same Type and overlap and least some of the same date and time. In many deployments "type" won't make sense as a conflict attribute, so you can leave it out of what follows.

Getting Ready

CC Calendar Pro comes with almost everything you need to setup this conflict relationship. However, you'll need to modify the ApptKeyTimeRangeCalc in CCCalAppts first. This calc ends like this:
Code: Select all
) // End Let
// Thanks to Mikhail Edoshin for this greta trick! - js
)

You'll want to insert the following before that parentheses in the ") // End Let" line so that the calc now ends like this:
Code: Select all
//  New Code


& "¶" &
"X" &
Substitute(
  Left(Right("0000000" & MinuteStart; 8); 8) & "¶" &
      Left(Right("0000000" & MinuteStart; 8); 7) & "¶" &
      Left(Right("0000000" & MinuteStart; 8); 6) & "¶" &
      Left(Right("0000000" & MinuteStart; 8); 5) & "¶" &
      Left(Right("0000000" & MinuteStart; 8); 4) & "¶" &
      Left(Right("0000000" & MinuteStart; 8); 3) & "¶" &
      Left(Right("0000000" & MinuteStart; 8); 2) & "¶" &
      Left(Right("0000000" & MinuteStart; 8); 1);
  "¶";
  "¶X"
  )


// End New Code
)// End Let
// Thanks to Mikhail Edoshin for this greta trick! - js

)

Note that the "¶" above is the paragraph symbol.

Creating the Conflict Relationship

Once you've edited that field you can move to the Graph in the CCCalendar file. Here you want to add another table occurrence of the Appointments table in the CCalAppts file. Call this table occurrence "AppointmentsDailyConflict" and make it the same green as the other occurrences of this table. Next you'll define a relationship between these two table occurrences: this relationship will be true if there is more than one appointment with the same conflict criteria. Define your relationship as follows:

Image

That's it.

Displaying the Conflict

The easiest way to display this conflict is to add a checkbox to the Mini Window Edit Appointment layout (since this is where you'd be making the conflicting appointment). Build a checkbox field formatted like the one for "repeating". That field is a button, so be sure to disable the button action if you copy it. Once you have a checkbox field, specify it to show the CommonOne field from the table occurrence "AppointmentsDailyConflict" which we created above. Set this checkbox to use the value list "one" which is already defined in CCCalendar.

Now you'll see that checkbox light up when you have a conflicting appointment.

Going further

Here are a couple suggestions for other cool things to do in this area.

Hidden Portals. Since we have a relationship that is only true when there are conflicts, we can make a one-row portal using this relationship. Then, anything inside the portal will only show up if there is a conflict with the current appointment. So, on the Mini Window Edit Appointment layout you could add some text in red like "Conflict Detected" and place this inside your one-row portal.

Show Conflicts. You could also create a button that would show you the conflicting records. You'd want to show them by duplicating the layout Mini Window Show Repetitions and changing the portal on the right side (and all the field within it) to use your AppointmentsDailyConflict. You can check out the Show / Hide Repetitions buttons to get examples of the GotoRelatedRecord and window management you'll need here.

Showing Conflicts Elsewhere. Remember that we defined the conflicting relationship inside the graph of CCCalendar, NOT CCCalAppts. Thus, the conflict information is not available to the appointment. If you want to show possible conflicts in other interfaces where you have appointments (in your own solution, perhaps) you'll want to create a relationship like AppointmentsDailyConflict inside the CCCalAppts file. You can then define fields in the Appointments table there that would display warnings if there are conflicts and such fields could be displayed alongside the appointment anywhere you choose.
John Sindelar
SeedCode
Posts: 10
Joined: Wed Jan 12, 2005 9:54 pm
PostPosted: Wed Jan 19, 2005 10:15 pm
I am most thankful not to have to design a feature which my secretary asked me for the moment I introduced CCCalendar.

HOWEVER: I think I have followed your intructions to the letter but my Checkbox in the Mini Window shows conflicts even though the times do not conflict. If I have multiple events on the one day a conflict is detected.

Also, I am baffled by the final ")" after the "greta trick! - js" line.

I have not spent the time to understand the total calculation which you are directing me to modify. I did try inserting the patch one ")" earlier in the calc, but this did not work.

Can you help me?
SeedCode Staff
SeedCode Staff
Posts: 2764
Joined: Thu Nov 20, 2003 11:01 am
PostPosted: Thu Jan 20, 2005 5:42 am
Ian R wrote:I am most thankful not to have to design a feature which my secretary asked me for the moment I introduced CCCalendar.

HOWEVER: I think I have followed your intructions to the letter but my Checkbox in the Mini Window shows conflicts even though the times do not conflict. If I have multiple events on the one day a conflict is detected.


You're right. The calc above works "most" of the time, but I really shouldn't have tried to use the same time calc for this and for what it was already designed to do.

Creating a new field in CCCalAppts works much better. Create a field called ApptKeyTimeRangeConflictCalc with the following definition:
Code: Select all
Let ( [
Start = ApptTimeStart ;
End = ApptTimeEndCalc
];

Substitute(Middle(" 0 1 2 3 4 5 6 7 8 9"; (Int(Mod(GetAsNumber ( Start ); 10^1)/10^0) + 1) * 2  - 1; (Int(GetAsNumber ( End )/10^0) - Int(GetAsNumber ( Start )/10^0) + 1) * 2); " ";"¶" &  Left(Right("000000" & GetAsNumber ( Start ); 7); 6))
&
Substitute(Middle(" 1 2 3 4 5 6 7 8 9"; (Int(Mod(GetAsNumber ( Start ); 10^2)/10^1) + 1) * 2  - 1; (Int(GetAsNumber ( End )/10^1) - Int(GetAsNumber ( Start )/10^1) - 1) * 2); " ";"¶" &  Left(Right("000000" & GetAsNumber ( Start ); 7); 5))
&
Substitute(Middle(" 1 2 3 4 5 6 7 8 9"; (Int(Mod(GetAsNumber ( Start ); 10^3)/10^2) + 1) * 2  - 1; (Int(GetAsNumber ( End )/10^2) - Int(GetAsNumber ( Start )/10^2) - 1) * 2); " ";"¶" &  Left(Right("000000" & GetAsNumber ( Start ); 7); 4))
&
Substitute(Middle(" 1 2 3 4 5 6 7 8 9"; (Int(Mod(GetAsNumber ( Start ); 10^4)/10^3) + 1) * 2  - 1; (Int(GetAsNumber ( End )/10^3) - Int(GetAsNumber ( Start )/10^3) - 1) * 2); " ";"¶" &  Left(Right("000000" & GetAsNumber ( Start ); 7); 3))
&
Substitute(Middle(" 1 2 3 4 5 6 7 8 9"; (Int(Mod(GetAsNumber ( Start ); 10^5)/10^4) + 1) * 2  - 1; (Int(GetAsNumber ( End )/10^4) - Int(GetAsNumber ( Start )/10^4) - 1) * 2); " ";"¶" &  Left(Right("000000" & GetAsNumber ( Start ); 7); 2))
&
Substitute(Middle(" 1 2 3 4 5 6 7 8 9"; (Int(Mod(GetAsNumber ( Start ); 10^6)/10^5) + 1) * 2  - 1; (Int(GetAsNumber ( End )/10^5) - Int(GetAsNumber ( Start )/10^5) - 1) * 2); " ";"¶" &  Left(Right("000000" & GetAsNumber ( Start ); 7); 1))
&
Substitute(Middle(" 1 2 3 4 5 6 7 8 9"; (Int(Mod(GetAsNumber ( Start ); 10^7)/10^6) + 1) * 2  - 1; (Int(GetAsNumber ( End )/10^6) - Int(GetAsNumber ( Start )/10^6) - 1) * 2); " ";"¶" &  Left(Right("000000" & GetAsNumber ( Start ); 7); 0))

&

Substitute(Middle(" 9 8 7 6 5 4 3 2 1 0"; (9 - Int(Mod(GetAsNumber ( End ); 10^1)/10^0)) * 2 + 1; (Int(GetAsNumber ( End )/10^0) - Int(GetAsNumber ( Start )/10^0) + 1) * 2); " ";"¶" &  Left(Right("000000" & GetAsNumber ( End ); 7); 6))
&
Substitute(Middle(" 8 7 6 5 4 3 2 1 0"; (9 - Int(Mod(GetAsNumber ( End ); 10^2)/10^1)) * 2 + 1; (Int(GetAsNumber ( End )/10^1) - Int(GetAsNumber ( Start )/10^1) - 1) * 2); " ";"¶" &  Left(Right("000000" & GetAsNumber ( End ); 7); 5))
&
Substitute(Middle(" 8 7 6 5 4 3 2 1 0"; (9 - Int(Mod(GetAsNumber ( End ); 10^3)/10^2)) * 2 + 1; (Int(GetAsNumber ( End )/10^2) - Int(GetAsNumber ( Start )/10^2) - 1) * 2); " ";"¶" &  Left(Right("000000" & GetAsNumber ( End ); 7); 4))
&
Substitute(Middle(" 8 7 6 5 4 3 2 1 0"; (9 - Int(Mod(GetAsNumber ( End ); 10^4)/10^3)) * 2 + 1; (Int(GetAsNumber ( End )/10^3) - Int(GetAsNumber ( Start )/10^3) - 1) * 2); " ";"¶" &  Left(Right("000000" & GetAsNumber ( End ); 7); 3))
&
Substitute(Middle(" 8 7 6 5 4 3 2 1 0"; (9 - Int(Mod(GetAsNumber ( End ); 10^5)/10^4)) * 2 + 1; (Int(GetAsNumber ( End )/10^4) - Int(GetAsNumber ( Start )/10^4) - 1) * 2); " ";"¶" &  Left(Right("000000" & GetAsNumber ( End ); 7); 2))
&
Substitute(Middle(" 8 7 6 5 4 3 2 1 0"; (9 - Int(Mod(GetAsNumber ( End ); 10^6)/10^5)) * 2 + 1; (Int(GetAsNumber ( End )/10^5) - Int(GetAsNumber ( Start )/10^5) - 1) * 2); " ";"¶" &  Left(Right("000000" & GetAsNumber ( End ); 7); 1))
&
Substitute(Middle(" 8 7 6 5 4 3 2 1 0"; (9 - Int(Mod(GetAsNumber ( End ); 10^7)/10^6)) * 2 + 1; (Int(GetAsNumber ( End )/10^6) - Int(GetAsNumber ( Start )/10^6) - 1) * 2); " ";"¶" &  Left(Right("000000" & GetAsNumber ( End ); 7); 0))
) // End Let
// Thanks to Mikhail Edoshin for this great trick!


Then use that calc for the time component in the relationship described in our original post.

Sorry for the confusion!

Also, I am baffled by the final ")" after the "greta trick! - js" line.


That just closes the IF statement which begins the calculation.

Best,

John
John Sindelar
SeedCode
Posts: 10
Joined: Wed Jan 12, 2005 9:54 pm
PostPosted: Thu Jan 20, 2005 4:49 pm
Thanks John,

I have implemented the new field & relationship.

However two events are shown not conflicting:
7:30am - 2:00pm
1:30pm - 2:30pm

But they do show conflicting if
7:30am - 3:00pm
1:30pm - 2:30pm

Ian
SeedCode Staff
SeedCode Staff
Posts: 2764
Joined: Thu Nov 20, 2003 11:01 am
PostPosted: Fri Jan 21, 2005 6:34 am
Indeed.

Two things need to be changed. The Keys I'm using were not long enough and we can't just link the key to itself, we have to use slightly different keys on each end of the time range.

Note that technically the date portion depicted in the relationship matches above suffers from the same problem, but since the conflicts we're trying to catch are within appointments of the same day, it doesn't matter. I'll leave it to you to expand this principle to the date portion if you like.

At any rate we now have two key fields, their definitions follow below. Use "ApptKeyTIMERangeCONFLICTCalc1" on the left side and "ApptKeyTIMERangeCONFLICTCalc2" on the right side of your conflict relationship and you should be all set.

Let me know how this works for you.

Best,

John

ApptKeyTIMERangeCONFLICTCalc1
Code: Select all
Let ( [
Start = (ApptDateStart-1) * 24 * 60 + Int(ApptTimeStart/60)  ;
End = (ApptDateEndCalc-1) * 24 * 60 + Int(ApptTimeEndCalc/60)     
];

Right("000000000" & Start; 10) &

Substitute(
Middle( ".1.2.3.4.5.6.7.8.9"; Int(Mod(Start; 10^1)/10^0) * 2 + 1; (Int(End/10^0) - Int(Start/10^0) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & Start; 10); 9) ) & Substitute( Middle( ".1.2.3.4.5.6.7.8.9"; Int(Mod(Start; 10^2)/10^1) * 2 + 1; (Int(End/10^1) - Int(Start/10^1) - 1) * 2 ); "."; "¶" &
Left(Right("000000000" & Start; 10); 8) ) & Substitute( Middle( ".1.2.3.4.5.6.7.8.9"; Int(Mod(Start; 10^3)/10^2) * 2 + 1; (Int(End/10^2) - Int(Start/10^2) - 1) * 2 ); "."; "¶" &
Left(Right("000000000" & Start; 10); 7) ) & Substitute( Middle( ".1.2.3.4.5.6.7.8.9"; Int(Mod(Start; 10^4)/10^3) * 2 + 1; (Int(End/10^3) - Int(Start/10^3) - 1) * 2 ); "."; "¶" &
Left(Right("000000000" & Start; 10); 6) ) & Substitute( Middle( ".1.2.3.4.5.6.7.8.9"; Int(Mod(Start; 10^5)/10^4) * 2 + 1; (Int(End/10^4) - Int(Start/10^4) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & Start; 10); 5) ) & Substitute( Middle( ".1.2.3.4.5.6.7.8.9"; Int(Mod(Start; 10^6)/10^5) * 2 + 1; (Int(End/10^5) - Int(Start/10^5) - 1) * 2 ); "."; "¶" &
Left(Right("000000000" & Start; 10); 4) ) & Substitute( Middle( ".1.2.3.4.5.6.7.8.9"; Int(Mod(Start; 10^7)/10^6) * 2 + 1; (Int(End/10^6) - Int(Start/10^6) - 1) * 2 ); "."; "¶" &
Left(Right("000000000" & Start; 10); 3) ) & Substitute( Middle( ".1.2.3.4.5.6.7.8.9"; Int(Mod(Start; 10^8)/10^7) * 2 + 1; (Int(End/10^7) - Int(Start/10^7) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & Start; 10); 2) ) & Substitute( Middle( ".1.2.3.4.5.6.7.8.9"; Int(Mod(Start; 10^9)/10^8) * 2 + 1; (Int(End/10^8) - Int(Start/10^8) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & Start; 10); 1) ) & Substitute( Middle( ".1.2.3.4.5.6.7.8.9"; Int(Mod(Start; 10^10)/10^9) * 2 + 1; (Int(End/10^9) - Int(Start/10^9) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & Start; 10); 0) )

&

"¶" & Right("000000000" & End; 10) &

Substitute(
Middle( ".8.7.6.5.4.3.2.1.0"; (9 - Int(Mod(End; 10^1)/10^0)) * 2 + 1; (Int(End/10^0) - Int(Start/10^0) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & End; 10); 9) ) & Substitute( Middle( ".8.7.6.5.4.3.2.1.0"; (9 - Int(Mod(End; 10^2)/10^1)) * 2 + 1; (Int(End/10^1) - Int(Start/10^1) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & End; 10); 8) ) & Substitute( Middle( ".8.7.6.5.4.3.2.1.0"; (9 - Int(Mod(End; 10^3)/10^2)) * 2 + 1; (Int(End/10^2) - Int(Start/10^2) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & End; 10); 7) ) & Substitute( Middle( ".8.7.6.5.4.3.2.1.0"; (9 - Int(Mod(End; 10^4)/10^3)) * 2 + 1; (Int(End/10^3) - Int(Start/10^3) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & End; 10); 6) ) & Substitute( Middle( ".8.7.6.5.4.3.2.1.0"; (9 - Int(Mod(End; 10^5)/10^4)) * 2 + 1; (Int(End/10^4) - Int(Start/10^4) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & End; 10); 5) ) & Substitute( Middle( ".8.7.6.5.4.3.2.1.0"; (9 - Int(Mod(End; 10^6)/10^5)) * 2 + 1; (Int(End/10^5) - Int(Start/10^5) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & End; 10); 4) ) & Substitute( Middle( ".8.7.6.5.4.3.2.1.0"; (9 - Int(Mod(End; 10^7)/10^6)) * 2 + 1; (Int(End/10^6) - Int(Start/10^6) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & End; 10); 3) ) & Substitute( Middle( ".8.7.6.5.4.3.2.1.0"; (9 - Int(Mod(End; 10^8)/10^7)) * 2 + 1; (Int(End/10^7) - Int(Start/10^7) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & End; 10); 2) ) & Substitute( Middle( ".8.7.6.5.4.3.2.1.0"; (9 - Int(Mod(End; 10^9)/10^8)) * 2 + 1; (Int(End/10^8) - Int(Start/10^8) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & End; 10); 1) ) & Substitute( Middle( ".8.7.6.5.4.3.2.1.0"; (9 - Int(Mod(End; 10^10)/10^9)) * 2 + 1; (Int(End/10^9) - Int(Start/10^9) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & End; 10); 0) )

& "¶" &
"X" &
Substitute(
  Left(Right("000000000" & Start; 10); 10) & "¶" &
      Left(Right("000000000" & Start; 10); 9) & "¶" &
      Left(Right("000000000" & Start; 10); 8) & "¶" &
      Left(Right("000000000" & Start; 10); 7) & "¶" &
      Left(Right("000000000" & Start; 10); 6) & "¶" &
      Left(Right("000000000" & Start; 10); 5) & "¶" &
      Left(Right("000000000" & Start; 10); 4) & "¶" &
      Left(Right("000000000" & Start; 10); 3) & "¶" &
      Left(Right("000000000" & Start; 10); 2) & "¶" &
      Left(Right("000000000" & Start; 10); 1);
  "¶";
  "¶X"
  )

) // End Let
// Thanks to Mikhail Edoshin for this great trick!


ApptKeyTIMERangeCONFLICTCalc2
Code: Select all
Let ( [
Start = (ApptDateStart-1) * 24 * 60 + Int(ApptTimeStart/60)  ;
End = (ApptDateEndCalc-1) * 24 * 60 + Int(ApptTimeEndCalc/60) ;   
Key =

Right("000000000" & Start; 10) &

Substitute(
Middle( ".1.2.3.4.5.6.7.8.9"; Int(Mod(Start; 10^1)/10^0) * 2 + 1; (Int(End/10^0) - Int(Start/10^0) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & Start; 10); 9) ) & Substitute( Middle( ".1.2.3.4.5.6.7.8.9"; Int(Mod(Start; 10^2)/10^1) * 2 + 1; (Int(End/10^1) - Int(Start/10^1) - 1) * 2 ); "."; "¶" &
Left(Right("000000000" & Start; 10); 8) ) & Substitute( Middle( ".1.2.3.4.5.6.7.8.9"; Int(Mod(Start; 10^3)/10^2) * 2 + 1; (Int(End/10^2) - Int(Start/10^2) - 1) * 2 ); "."; "¶" &
Left(Right("000000000" & Start; 10); 7) ) & Substitute( Middle( ".1.2.3.4.5.6.7.8.9"; Int(Mod(Start; 10^4)/10^3) * 2 + 1; (Int(End/10^3) - Int(Start/10^3) - 1) * 2 ); "."; "¶" &
Left(Right("000000000" & Start; 10); 6) ) & Substitute( Middle( ".1.2.3.4.5.6.7.8.9"; Int(Mod(Start; 10^5)/10^4) * 2 + 1; (Int(End/10^4) - Int(Start/10^4) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & Start; 10); 5) ) & Substitute( Middle( ".1.2.3.4.5.6.7.8.9"; Int(Mod(Start; 10^6)/10^5) * 2 + 1; (Int(End/10^5) - Int(Start/10^5) - 1) * 2 ); "."; "¶" &
Left(Right("000000000" & Start; 10); 4) ) & Substitute( Middle( ".1.2.3.4.5.6.7.8.9"; Int(Mod(Start; 10^7)/10^6) * 2 + 1; (Int(End/10^6) - Int(Start/10^6) - 1) * 2 ); "."; "¶" &
Left(Right("000000000" & Start; 10); 3) ) & Substitute( Middle( ".1.2.3.4.5.6.7.8.9"; Int(Mod(Start; 10^8)/10^7) * 2 + 1; (Int(End/10^7) - Int(Start/10^7) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & Start; 10); 2) ) & Substitute( Middle( ".1.2.3.4.5.6.7.8.9"; Int(Mod(Start; 10^9)/10^8) * 2 + 1; (Int(End/10^8) - Int(Start/10^8) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & Start; 10); 1) ) & Substitute( Middle( ".1.2.3.4.5.6.7.8.9"; Int(Mod(Start; 10^10)/10^9) * 2 + 1; (Int(End/10^9) - Int(Start/10^9) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & Start; 10); 0) )

&

"¶" & Right("000000000" & End; 10) &

Substitute(
Middle( ".8.7.6.5.4.3.2.1.0"; (9 - Int(Mod(End; 10^1)/10^0)) * 2 + 1; (Int(End/10^0) - Int(Start/10^0) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & End; 10); 9) ) & Substitute( Middle( ".8.7.6.5.4.3.2.1.0"; (9 - Int(Mod(End; 10^2)/10^1)) * 2 + 1; (Int(End/10^1) - Int(Start/10^1) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & End; 10); 8) ) & Substitute( Middle( ".8.7.6.5.4.3.2.1.0"; (9 - Int(Mod(End; 10^3)/10^2)) * 2 + 1; (Int(End/10^2) - Int(Start/10^2) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & End; 10); 7) ) & Substitute( Middle( ".8.7.6.5.4.3.2.1.0"; (9 - Int(Mod(End; 10^4)/10^3)) * 2 + 1; (Int(End/10^3) - Int(Start/10^3) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & End; 10); 6) ) & Substitute( Middle( ".8.7.6.5.4.3.2.1.0"; (9 - Int(Mod(End; 10^5)/10^4)) * 2 + 1; (Int(End/10^4) - Int(Start/10^4) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & End; 10); 5) ) & Substitute( Middle( ".8.7.6.5.4.3.2.1.0"; (9 - Int(Mod(End; 10^6)/10^5)) * 2 + 1; (Int(End/10^5) - Int(Start/10^5) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & End; 10); 4) ) & Substitute( Middle( ".8.7.6.5.4.3.2.1.0"; (9 - Int(Mod(End; 10^7)/10^6)) * 2 + 1; (Int(End/10^6) - Int(Start/10^6) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & End; 10); 3) ) & Substitute( Middle( ".8.7.6.5.4.3.2.1.0"; (9 - Int(Mod(End; 10^8)/10^7)) * 2 + 1; (Int(End/10^7) - Int(Start/10^7) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & End; 10); 2) ) & Substitute( Middle( ".8.7.6.5.4.3.2.1.0"; (9 - Int(Mod(End; 10^9)/10^8)) * 2 + 1; (Int(End/10^8) - Int(Start/10^8) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & End; 10); 1) ) & Substitute( Middle( ".8.7.6.5.4.3.2.1.0"; (9 - Int(Mod(End; 10^10)/10^9)) * 2 + 1; (Int(End/10^9) - Int(Start/10^9) - 1) * 2 ); "."; "¶" & 
Left(Right("000000000" & End; 10); 0) )

];

"X" &
Substitute(
  Key;
  "¶";
  "¶X"
  )

& "¶" &
Left(Right("000000000" & Start; 10); 10) & "¶" &
Left(Right("000000000" & Start; 10); 9) & "¶" &
Left(Right("000000000" & Start; 10); 8) & "¶" &
Left(Right("000000000" & Start; 10); 7) & "¶" &
Left(Right("000000000" & Start; 10); 6) & "¶" &
Left(Right("000000000" & Start; 10); 5) & "¶" &
Left(Right("000000000" & Start; 10); 4) & "¶" &
Left(Right("000000000" & Start; 10); 3) & "¶" &
Left(Right("000000000" & Start; 10); 2) & "¶" &
Left(Right("000000000" & Start; 10); 1)

) // End Let
// Thanks to Mikhail Edoshin for this great trick!
Last edited by John Sindelar on Mon Apr 25, 2005 9:31 am, edited 1 time in total.
John Sindelar
SeedCode
Posts: 10
Joined: Wed Jan 12, 2005 9:54 pm
PostPosted: Fri Jan 21, 2005 3:21 pm
Thanks John,

Now my only problem is an event which ends at the same time that another begins. They show as conflicting.

Any chance of dropping a Second (or a Minute) of the End Time for the relationship; or some other solution.

Ian

PS iCal Import is going well, I have some work to do on monthly repeats. I am not attempting Yearly repeats.
SeedCode Staff
SeedCode Staff
Posts: 2764
Joined: Thu Nov 20, 2003 11:01 am
PostPosted: Fri Jan 28, 2005 5:13 am
Hi Ian,

I like your suggestion of shaving a minute (the keys we're using don't support second or that would be the way to go). The following changes to the Let statements of both keys prevent conflicts from being recognized if times overlap by only 1 minute. So an end of 2:30pm and a start of 2:30pm won't be seen as a conflict.

Unfortunately, an end of 2:31pm and a start of 2:30pm won't be seen as a conflict either, but I think that is something we can live with.

Code: Select all
Let ( [
Start = (ApptDateStart-1) * 24 * 60 + Int(ApptTimeStart/60) + 1 ;
End = (ApptDateEndCalc-1) * 24 * 60 + Int(ApptTimeEndCalc/60) - 1     
];
John Sindelar
SeedCode
Posts: 10
Joined: Wed Jan 12, 2005 9:54 pm
PostPosted: Fri Jan 28, 2005 1:51 pm
Thanks John,

It all seems to work OK. I am sure we can live with the side effect.

Ian
PostPosted: Sat Feb 12, 2005 1:26 pm
In the last solution given, how would I set the conflict relationship up? Where do I go to put the ApptTIMERRangeCONFLICT1 on the “left side” and the ApptTIMERRangeCONFLICT2 on the “right?” Should the newly created fields be in CCCAppts like the original post was? Please clarify this for me (I’m sorry—I’m new to filemaker).
SeedCode Staff
SeedCode Staff
Posts: 2764
Joined: Thu Nov 20, 2003 11:01 am
PostPosted: Sat Feb 12, 2005 3:34 pm
Yes, these two fields are attributes of the appointment and are defined in CCCalAppts.

If you take another look at the first post in this thread, the section "Creating the Conflict Relationship" describes how to create a new relationship in the CCCalendar graph. You'll see that the screen shot presented in that post has a "left" and a "right" side... that is what we were talking about. Sorry for the slang. =)
John Sindelar
SeedCode
Posts: 4
Joined: Tue Feb 22, 2005 8:15 pm
PostPosted: Tue Feb 22, 2005 8:24 pm
Hey John,
I got the conflict changes made as you described here. I've been attempting to build a count script the way you described in your email, and I haven't been able to figure out how to implement it. What is it that the count script is supposed to count, and from what relationship?

I’ve been banging my head for 3 hours, and I’m feeling like I did when I first started using FM6.
Thanks for whatever help you can offer.
Brian
SeedCode Staff
SeedCode Staff
Posts: 2764
Joined: Thu Nov 20, 2003 11:01 am
PostPosted: Wed Feb 23, 2005 6:58 am
Hi Brian.

Well I think we may be speaking about apples and oranges. The calcs described previously in this thread are intended to let one appointment know if it conflicts with another.

The emails you and I have been passing around discussed how to flag a conflict in the time-bar to the left of the daily view. In some ways this is much simpler. In the Hours table, define a new calculation field called HourConflictountCalc. Set this to evaluate from the context of HoursVisibleDaily and define it to return a number. The calc should read:
Code: Select all
Count ( AppointmentsHoursVisibleDaily::PrimeApptIDX ) - 1


Now, go into layout mode on the daily view and copy and paste the time display field from the time bar. Change this field to be the HourConflictountCalc you just created and move it into the first portal row of the time bar, shortening it so that you can read both it and the time.

This will display the number of conflicts you have for any 15 minute interval. (Simply remove the "-1" from the calc above to get a count of the number of appointments.)

Using this you should be able to build the container field we discussed privately to color code the hour (15 minute increment) according to its capacity.

=)

Note that if you want to show this on the week also, you need a second copy of the calc set to evaluate form the context of HoursVisibleWeek1.
John Sindelar
SeedCode
Posts: 4
Joined: Tue Feb 22, 2005 8:15 pm
PostPosted: Wed Feb 23, 2005 7:44 pm
John, Thanks! I think I got it.

Return to CC Calendar (FM7/8)

Who is online

Users browsing this forum: No registered users and 3 guests

cron
(855) SEEDCODE
[email protected]
Follow us: