Recursive CF Needed

[dwdata]

I need help with defining a recursive CF to use for building a relational KEY field.

I have sorted time (and Type ID) list like the following (5 minute intervals)

08:00:00 10

08:05:00 10

08:10:00 10

08:15:00 10

09:00:00 12

09:05:00 12

09:10:00 12

09:15:00 12

I need a CF that will determine how many times proceed each time line without a break in the sequence (just the time not the TYPE field) and then builds a key based on those values.

First determine proceeding times:

08:00:00 10 = 3

08:05:00 10 = 2

08:10:00 10 = 1

08:15:00 10 = 0

09:00:00 12 = 3

09:05:00 12 = 2

09:10:00 12 = 1

09:15:00 12 = 0

Then the key is generated:

08:00:00 10 3

08:00:00 10 2

08:00:00 10 1

08:05:00 10 2

08:05:00 10 1

08:10:00 10 1

09:00:00 12 3

09:00:00 12 2

09:00:00 12 1

09:05:00 12 2

09:05:00 12 1

09:10:00 12 1

Notice times with "0"are skipped.

Any help would be appreciated.

[T-Square]

I think your structure needs some examination, but can't tell without some more detail from you.

What are you trying to track here? Why are you trying to build relational keys?

More importantly, why can't you make your relationship based on a non-significant ID field (each record getting a unique sequential number that is never seen or duplicated)?

Unless there's some truly compelling need for it, attempts to build relational keys are usually more trouble than they're worth. In most situations, you can achieve your results without resorting to constructed relational keys, substituting calculations, relations, and display fields to present your users with such information.

David

[dwdata]

Here is the low down (I'll try to keep it simple)

I have a scheduling DB in which my users can establish "Availability Records" in which users can schedule appointments on top of. These records have a Date, StartTime, EndTime, EmployeeID, and Appt_TypeID.

I, also, have a avParent Record which has a Date and EmployeeID. One avParent record can have one or many avRecords (children). In this parent record is a field that compiles a list of times and the Appt_TypeID

08:00:00 10

08:05:00 10

08:10:00 10

08:15:00 10

09:00:00 12

09:05:00 12

09:10:00 12

09:15:00 12

avChild#1 = Times=8am to 8:20am ApptTypeID=10

avChild#2 = Times=9am to 9:20am ApptTypeID=12

I want to build a key which check for a break in the sequence of time and ends up resulting in this:

08:00:00 10 3

08:05:00 10 2

08:10:00 10 1

08:15:00 10 0

09:00:00 12 3

09:05:00 12 2

09:10:00 12 1

09:15:00 12 0

With that established I would like a descending key compiled for values > 0:

08:00:00 10 3

08:00:00 10 2

08:00:00 10 1

08:05:00 10 2

08:05:00 10 1

08:10:00 10 1

09:00:00 12 3

09:00:00 12 2

09:00:00 12 1

09:05:00 12 2

09:05:00 12 1

09:10:00 12 1

This KEY field will part of my RIGHT side of my relationship.

On the LEFT side of my relationship I will let my user search for availability by selecting a DATE RANGE (Start and End), EmployeeID, ApptTypeID, StartTime Range (Start and End), and ApptDuration.

Based on the StartTime Range, ApptTypeID, and ApptDuration I will generate a KEY like this:

StartTimeRange = 9am to 11am

ApptTypeID = 10

ApptDuration = 0:10:00

The KEY generated:

09:00:00 10 2

09:05:00 10 2

09:10:00 10 2

09:15:00 10 2

09:20:00 10 2

09:25:00 10 2

09:30:00 10 2

09:35:00 10 2

09:40:00 10 2

09:45:00 10 2

09:50:00 10 2

09:55:00 10 2

10:00:00 10 2

10:05:00 10 2

10:10:00 10 2

10:15:00 10 2

10:20:00 10 2

10:25:00 10 2

10:30:00 10 2

10:35:00 10 2

10:40:00 10 2

10:45:00 10 2

Note: The range subtracts the ApptDuration from the EndTime because it will fall out of the range.

Now with both the LEFT and RIGHT KEYS established I can create a relationship:

DateRange_Start <= avParent::avDate

DateRange_End >= avParent::avDate

EmployeeID = avParent::EmployeeID

TimeKEY = avParent::avKEY

Now dates with availability can be shown in a portal. They click to navigate to that Date where they can see the availability.

Right now I can do this all via scripting but was hoping I (or a decent soul) could assist me in compiling a CF to bypass the scripting.

Hopefully this is enough to gnaw on ;-)

[dwdata]

Here is a CF I am using current that returns where the first break in sequence is:

__AvDur(theTime, TimeList, MinuteUnit)

Let ( [

List = "¶" & TimeList & "¶" ;

A = GetAsTime(theTime);

U = Time(0; MinuteUnit; 0)

] ;

Case(

PatternCount ( List ; "¶" & __PadTime (A + (U*0)) & "¶") = 0; 0;

PatternCount ( List ; "¶" & __PadTime (A + (U*1)) & "¶") = 0; 1;

PatternCount ( List ; "¶" & __PadTime (A + (U*2)) & "¶") = 0; 2;

PatternCount ( List ; "¶" & __PadTime (A + (U*3)) & "¶") = 0; 3;

PatternCount ( List ; "¶" & __PadTime (A + (U*4)) & "¶") = 0; 4;

PatternCount ( List ; "¶" & __PadTime (A + (U*5)) & "¶") = 0; 5;

PatternCount ( List ; "¶" & __PadTime (A + (U*6)) & "¶") = 0; 6;

PatternCount ( List ; "¶" & __PadTime (A + (U*7)) & "¶") = 0; 7;

PatternCount ( List ; "¶" & __PadTime (A + (U*8)) & "¶") = 0; 8;

PatternCount ( List ; "¶" & __PadTime (A + (U*9)) & "¶") = 0; 9;

PatternCount ( List ; "¶" & __PadTime (A + (U*10)) & "¶") = 0; 10;

PatternCount ( List ; "¶" & __PadTime (A + (U*11)) & "¶") = 0; 11;

PatternCount ( List ; "¶" & __PadTime (A + (U*12)) & "¶") = 0; 12;

PatternCount ( List ; "¶" & __PadTime (A + (U*13)) & "¶") = 0; 13;

PatternCount ( List ; "¶" & __PadTime (A + (U*14)) & "¶") = 0; 14;

PatternCount ( List ; "¶" & __PadTime (A + (U*15)) & "¶") = 0; 15;

PatternCount ( List ; "¶" & __PadTime (A + (U*16)) & "¶") = 0; 16;

PatternCount ( List ; "¶" & __PadTime (A + (U*17)) & "¶") = 0; 17;

PatternCount ( List ; "¶" & __PadTime (A + (U*18)) & "¶") = 0; 18;

PatternCount ( List ; "¶" & __PadTime (A + (U*19)) & "¶") = 0; 19;

PatternCount ( List ; "¶" & __PadTime (A + (U*20)) & "¶") = 0; 20;

PatternCount ( List ; "¶" & __PadTime (A + (U*21)) & "¶") = 0; 21;

PatternCount ( List ; "¶" & __PadTime (A + (U*22)) & "¶") = 0; 22;

PatternCount ( List ; "¶" & __PadTime (A + (U*23)) & "¶") = 0; 23;

PatternCount ( List ; "¶" & __PadTime (A + (U*24)) & "¶") = 0; 24;

PatternCount ( List ; "¶" & __PadTime (A + (U*25)) & "¶") = 0; 25;

PatternCount ( List ; "¶" & __PadTime (A + (U*26)) & "¶") = 0; 26;

PatternCount ( List ; "¶" & __PadTime (A + (U*27)) & "¶") = 0; 27;

PatternCount ( List ; "¶" & __PadTime (A + (U*28)) & "¶") = 0; 28;

PatternCount ( List ; "¶" & __PadTime (A + (U*29)) & "¶") = 0; 29;

PatternCount ( List ; "¶" & __PadTime (A + (U*30)) & "¶") = 0; 30;

PatternCount ( List ; "¶" & __PadTime (A + (U*31)) & "¶") = 0; 31;

PatternCount ( List ; "¶" & __PadTime (A + (U*32)) & "¶") = 0; 32;

PatternCount ( List ; "¶" & __PadTime (A + (U*33)) & "¶") = 0; 33;

PatternCount ( List ; "¶" & __PadTime (A + (U*34)) & "¶") = 0; 34;

PatternCount ( List ; "¶" & __PadTime (A + (U*35)) & "¶") = 0; 35;

PatternCount ( List ; "¶" & __PadTime (A + (U*36)) & "¶") = 0; 36;

PatternCount ( List ; "¶" & __PadTime (A + (U*37)) & "¶") = 0; 37;

PatternCount ( List ; "¶" & __PadTime (A + (U*38)) & "¶") = 0; 38;

PatternCount ( List ; "¶" & __PadTime (A + (U*39)) & "¶") = 0; 39;

PatternCount ( List ; "¶" & __PadTime (A + (U*40)) & "¶") = 0; 40;

PatternCount ( List ; "¶" & __PadTime (A + (U*41)) & "¶") = 0; 41;

PatternCount ( List ; "¶" & __PadTime (A + (U*42)) & "¶") = 0; 42;

PatternCount ( List ; "¶" & __PadTime (A + (U*43)) & "¶") = 0; 43;

PatternCount ( List ; "¶" & __PadTime (A + (U*44)) & "¶") = 0; 44;

PatternCount ( List ; "¶" & __PadTime (A + (U*45)) & "¶") = 0; 45;

PatternCount ( List ; "¶" & __PadTime (A + (U*46)) & "¶") = 0; 46;

PatternCount ( List ; "¶" & __PadTime (A + (U*47)) & "¶") = 0; 47;

PatternCount ( List ; "¶" & __PadTime (A + (U*48)) & "¶") = 0; 48;

PatternCount ( List ; "¶" & __PadTime (A + (U*49)) & "¶") = 0; 49;

PatternCount ( List ; "¶" & __PadTime (A + (U*50)) & "¶") = 0; 50;

PatternCount ( List ; "¶" & __PadTime (A + (U*51)) & "¶") = 0; 51;

PatternCount ( List ; "¶" & __PadTime (A + (U*52)) & "¶") = 0; 52;

PatternCount ( List ; "¶" & __PadTime (A + (U*53)) & "¶") = 0; 53;

PatternCount ( List ; "¶" & __PadTime (A + (U*54)) & "¶") = 0; 54;

PatternCount ( List ; "¶" & __PadTime (A + (U*55)) & "¶") = 0; 55;

PatternCount ( List ; "¶" & __PadTime (A + (U*56)) & "¶") = 0; 56;

PatternCount ( List ; "¶" & __PadTime (A + (U*57)) & "¶") = 0; 57;

PatternCount ( List ; "¶" & __PadTime (A + (U*58)) & "¶") = 0; 58;

PatternCount ( List ; "¶" & __PadTime (A + (U*59)) & "¶") = 0; 59;

PatternCount ( List ; "¶" & __PadTime (A + (U*60)) & "¶") = 0; 60;

PatternCount ( List ; "¶" & __PadTime (A + (U*61)) & "¶") = 0; 61;

PatternCount ( List ; "¶" & __PadTime (A + (U*62)) & "¶") = 0; 62;

PatternCount ( List ; "¶" & __PadTime (A + (U*63)) & "¶") = 0; 63;

PatternCount ( List ; "¶" & __PadTime (A + (U*64)) & "¶") = 0; 64;

PatternCount ( List ; "¶" & __PadTime (A + (U*65)) & "¶") = 0; 65;

PatternCount ( List ; "¶" & __PadTime (A + (U*66)) & "¶") = 0; 66;

PatternCount ( List ; "¶" & __PadTime (A + (U*67)) & "¶") = 0; 67;

PatternCount ( List ; "¶" & __PadTime (A + (U*68)) & "¶") = 0; 68;

PatternCount ( List ; "¶" & __PadTime (A + (U*69)) & "¶") = 0; 69;

PatternCount ( List ; "¶" & __PadTime (A + (U*70)) & "¶") = 0; 70;

PatternCount ( List ; "¶" & __PadTime (A + (U*71)) & "¶") = 0; 71;

PatternCount ( List ; "¶" & __PadTime (A + (U*72)) & "¶") = 0; 72;

PatternCount ( List ; "¶" & __PadTime (A + (U*73)) & "¶") = 0; 73;

PatternCount ( List ; "¶" & __PadTime (A + (U*74)) & "¶") = 0; 74;

PatternCount ( List ; "¶" & __PadTime (A + (U*75)) & "¶") = 0; 75;

PatternCount ( List ; "¶" & __PadTime (A + (U*76)) & "¶") = 0; 76;

PatternCount ( List ; "¶" & __PadTime (A + (U*77)) & "¶") = 0; 77;

PatternCount ( List ; "¶" & __PadTime (A + (U*78)) & "¶") = 0; 78;

PatternCount ( List ; "¶" & __PadTime (A + (U*79)) & "¶") = 0; 79;

PatternCount ( List ; "¶" & __PadTime (A + (U*80)) & "¶") = 0; 80;

PatternCount ( List ; "¶" & __PadTime (A + (U*81)) & "¶") = 0; 81;

PatternCount ( List ; "¶" & __PadTime (A + (U*82)) & "¶") = 0; 82;

PatternCount ( List ; "¶" & __PadTime (A + (U*83)) & "¶") = 0; 83;

PatternCount ( List ; "¶" & __PadTime (A + (U*84)) & "¶") = 0; 84;

PatternCount ( List ; "¶" & __PadTime (A + (U*85)) & "¶") = 0; 85;

PatternCount ( List ; "¶" & __PadTime (A + (U*86)) & "¶") = 0; 86;

PatternCount ( List ; "¶" & __PadTime (A + (U*87)) & "¶") = 0; 87;

PatternCount ( List ; "¶" & __PadTime (A + (U*88)) & "¶") = 0; 88;

PatternCount ( List ; "¶" & __PadTime (A + (U*89)) & "¶") = 0; 89;

PatternCount ( List ; "¶" & __PadTime (A + (U*90)) & "¶") = 0; 90;

PatternCount ( List ; "¶" & __PadTime (A + (U*91)) & "¶") = 0; 91;

PatternCount ( List ; "¶" & __PadTime (A + (U*92)) & "¶") = 0; 92;

PatternCount ( List ; "¶" & __PadTime (A + (U*93)) & "¶") = 0; 93;

PatternCount ( List ; "¶" & __PadTime (A + (U*94)) & "¶") = 0; 94;

PatternCount ( List ; "¶" & __PadTime (A + (U*95)) & "¶") = 0; 95;

PatternCount ( List ; "¶" & __PadTime (A + (U*96)) & "¶") = 0; 96;

PatternCount ( List ; "¶" & __PadTime (A + (U*97)) & "¶") = 0; 97;

PatternCount ( List ; "¶" & __PadTime (A + (U*98)) & "¶") = 0; 98;

PatternCount ( List ; "¶" & __PadTime (A + (U*99)) & "¶") = 0; 99;

PatternCount ( List ; "¶" & __PadTime (A + (U*100)) & "¶") = 0; 100;

PatternCount ( List ; "¶" & __PadTime (A + (U*101)) & "¶") = 0; 101;

PatternCount ( List ; "¶" & __PadTime (A + (U*102)) & "¶") = 0; 102;

PatternCount ( List ; "¶" & __PadTime (A + (U*103)) & "¶") = 0; 103;

PatternCount ( List ; "¶" & __PadTime (A + (U*104)) & "¶") = 0; 104;

PatternCount ( List ; "¶" & __PadTime (A + (U*105)) & "¶") = 0; 105;

PatternCount ( List ; "¶" & __PadTime (A + (U*106)) & "¶") = 0; 106;

PatternCount ( List ; "¶" & __PadTime (A + (U*107)) & "¶") = 0; 107;

PatternCount ( List ; "¶" & __PadTime (A + (U*108)) & "¶") = 0; 108;

PatternCount ( List ; "¶" & __PadTime (A + (U*109)) & "¶") = 0; 109;

PatternCount ( List ; "¶" & __PadTime (A + (U*110)) & "¶") = 0; 110;

PatternCount ( List ; "¶" & __PadTime (A + (U*111)) & "¶") = 0; 111;

PatternCount ( List ; "¶" & __PadTime (A + (U*112)) & "¶") = 0; 112;

PatternCount ( List ; "¶" & __PadTime (A + (U*113)) & "¶") = 0; 113;

PatternCount ( List ; "¶" & __PadTime (A + (U*114)) & "¶") = 0; 114;

PatternCount ( List ; "¶" & __PadTime (A + (U*115)) & "¶") = 0; 115;

PatternCount ( List ; "¶" & __PadTime (A + (U*116)) & "¶") = 0; 116;

PatternCount ( List ; "¶" & __PadTime (A + (U*117)) & "¶") = 0; 117;

PatternCount ( List ; "¶" & __PadTime (A + (U*118)) & "¶") = 0; 118;

PatternCount ( List ; "¶" & __PadTime (A + (U*119)) & "¶") = 0; 119;

PatternCount ( List ; "¶" & __PadTime (A + (U*120)) & "¶") = 0; 120;

PatternCount ( List ; "¶" & __PadTime (A + (U*121)) & "¶") = 0; 121;

PatternCount ( List ; "¶" & __PadTime (A + (U*122)) & "¶") = 0; 122;

PatternCount ( List ; "¶" & __PadTime (A + (U*123)) & "¶") = 0; 123;

PatternCount ( List ; "¶" & __PadTime (A + (U*124)) & "¶") = 0; 124;

PatternCount ( List ; "¶" & __PadTime (A + (U*125)) & "¶") = 0; 125;

PatternCount ( List ; "¶" & __PadTime (A + (U*126)) & "¶") = 0; 126;

PatternCount ( List ; "¶" & __PadTime (A + (U*127)) & "¶") = 0; 127;

PatternCount ( List ; "¶" & __PadTime (A + (U*128)) & "¶") = 0; 128;

PatternCount ( List ; "¶" & __PadTime (A + (U*129)) & "¶") = 0; 129;

PatternCount ( List ; "¶" & __PadTime (A + (U*130)) & "¶") = 0; 130;

PatternCount ( List ; "¶" & __PadTime (A + (U*131)) & "¶") = 0; 131;

PatternCount ( List ; "¶" & __PadTime (A + (U*132)) & "¶") = 0; 132;

PatternCount ( List ; "¶" & __PadTime (A + (U*133)) & "¶") = 0; 133;

PatternCount ( List ; "¶" & __PadTime (A + (U*134)) & "¶") = 0; 134;

PatternCount ( List ; "¶" & __PadTime (A + (U*135)) & "¶") = 0; 135;

PatternCount ( List ; "¶" & __PadTime (A + (U*136)) & "¶") = 0; 136;

PatternCount ( List ; "¶" & __PadTime (A + (U*137)) & "¶") = 0; 137;

PatternCount ( List ; "¶" & __PadTime (A + (U*138)) & "¶") = 0; 138;

PatternCount ( List ; "¶" & __PadTime (A + (U*139)) & "¶") = 0; 139;

PatternCount ( List ; "¶" & __PadTime (A + (U*140)) & "¶") = 0; 140;

PatternCount ( List ; "¶" & __PadTime (A + (U*141)) & "¶") = 0; 141;

PatternCount ( List ; "¶" & __PadTime (A + (U*142)) & "¶") = 0; 142;

PatternCount ( List ; "¶" & __PadTime (A + (U*143)) & "¶") = 0; 143;

PatternCount ( List ; "¶" & __PadTime (A + (U*144)) & "¶") = 0; 144;

PatternCount ( List ; "¶" & __PadTime (A + (U*145)) & "¶") = 0; 145;

PatternCount ( List ; "¶" & __PadTime (A + (U*146)) & "¶") = 0; 146;

PatternCount ( List ; "¶" & __PadTime (A + (U*147)) & "¶") = 0; 147;

PatternCount ( List ; "¶" & __PadTime (A + (U*148)) & "¶") = 0; 148;

PatternCount ( List ; "¶" & __PadTime (A + (U*149)) & "¶") = 0; 149;

PatternCount ( List ; "¶" & __PadTime (A + (U*150)) & "¶") = 0; 150;

PatternCount ( List ; "¶" & __PadTime (A + (U*151)) & "¶") = 0; 151;

PatternCount ( List ; "¶" & __PadTime (A + (U*152)) & "¶") = 0; 152;

PatternCount ( List ; "¶" & __PadTime (A + (U*153)) & "¶") = 0; 153;

PatternCount ( List ; "¶" & __PadTime (A + (U*154)) & "¶") = 0; 154;

PatternCount ( List ; "¶" & __PadTime (A + (U*155)) & "¶") = 0; 155;

PatternCount ( List ; "¶" & __PadTime (A + (U*156)) & "¶") = 0; 156;

PatternCount ( List ; "¶" & __PadTime (A + (U*157)) & "¶") = 0; 157;

PatternCount ( List ; "¶" & __PadTime (A + (U*158)) & "¶") = 0; 158;

PatternCount ( List ; "¶" & __PadTime (A + (U*159)) & "¶") = 0; 159;

PatternCount ( List ; "¶" & __PadTime (A + (U*160)) & "¶") = 0; 160;

PatternCount ( List ; "¶" & __PadTime (A + (U*161)) & "¶") = 0; 161;

PatternCount ( List ; "¶" & __PadTime (A + (U*162)) & "¶") = 0; 162;

PatternCount ( List ; "¶" & __PadTime (A + (U*163)) & "¶") = 0; 163;

PatternCount ( List ; "¶" & __PadTime (A + (U*164)) & "¶") = 0; 164;

PatternCount ( List ; "¶" & __PadTime (A + (U*165)) & "¶") = 0; 165;

PatternCount ( List ; "¶" & __PadTime (A + (U*166)) & "¶") = 0; 166;

PatternCount ( List ; "¶" & __PadTime (A + (U*167)) & "¶") = 0; 167;

PatternCount ( List ; "¶" & __PadTime (A + (U*168)) & "¶") = 0; 168;

PatternCount ( List ; "¶" & __PadTime (A + (U*169)) & "¶") = 0; 169;

PatternCount ( List ; "¶" & __PadTime (A + (U*170)) & "¶") = 0; 170;

PatternCount ( List ; "¶" & __PadTime (A + (U*171)) & "¶") = 0; 171;

PatternCount ( List ; "¶" & __PadTime (A + (U*172)) & "¶") = 0; 172;

PatternCount ( List ; "¶" & __PadTime (A + (U*173)) & "¶") = 0; 173;

PatternCount ( List ; "¶" & __PadTime (A + (U*174)) & "¶") = 0; 174;

PatternCount ( List ; "¶" & __PadTime (A + (U*175)) & "¶") = 0; 175;

PatternCount ( List ; "¶" & __PadTime (A + (U*176)) & "¶") = 0; 176;

PatternCount ( List ; "¶" & __PadTime (A + (U*177)) & "¶") = 0; 177;

PatternCount ( List ; "¶" & __PadTime (A + (U*178)) & "¶") = 0; 178;

PatternCount ( List ; "¶" & __PadTime (A + (U*179)) & "¶") = 0; 179;

PatternCount ( List ; "¶" & __PadTime (A + (U*180)) & "¶") = 0; 180;

PatternCount ( List ; "¶" & __PadTime (A + (U*181)) & "¶") = 0; 181;

PatternCount ( List ; "¶" & __PadTime (A + (U*182)) & "¶") = 0; 182;

PatternCount ( List ; "¶" & __PadTime (A + (U*183)) & "¶") = 0; 183;

PatternCount ( List ; "¶" & __PadTime (A + (U*184)) & "¶") = 0; 184;

PatternCount ( List ; "¶" & __PadTime (A + (U*185)) & "¶") = 0; 185;

PatternCount ( List ; "¶" & __PadTime (A + (U*186)) & "¶") = 0; 186;

PatternCount ( List ; "¶" & __PadTime (A + (U*187)) & "¶") = 0; 187;

PatternCount ( List ; "¶" & __PadTime (A + (U*188)) & "¶") = 0; 188;

PatternCount ( List ; "¶" & __PadTime (A + (U*189)) & "¶") = 0; 189;

PatternCount ( List ; "¶" & __PadTime (A + (U*190)) & "¶") = 0; 190;

PatternCount ( List ; "¶" & __PadTime (A + (U*191)) & "¶") = 0; 191;

PatternCount ( List ; "¶" & __PadTime (A + (U*192)) & "¶") = 0; 192;

PatternCount ( List ; "¶" & __PadTime (A + (U*193)) & "¶") = 0; 193;

PatternCount ( List ; "¶" & __PadTime (A + (U*194)) & "¶") = 0; 194;

PatternCount ( List ; "¶" & __PadTime (A + (U*195)) & "¶") = 0; 195;

PatternCount ( List ; "¶" & __PadTime (A + (U*196)) & "¶") = 0; 196;

PatternCount ( List ; "¶" & __PadTime (A + (U*197)) & "¶") = 0; 197;

PatternCount ( List ; "¶" & __PadTime (A + (U*198)) & "¶") = 0; 198;

PatternCount ( List ; "¶" & __PadTime (A + (U*199)) & "¶") = 0; 199;

PatternCount ( List ; "¶" & __PadTime (A + (U*200)) & "¶") = 0; 200;

PatternCount ( List ; "¶" & __PadTime (A + (U*201)) & "¶") = 0; 201;

PatternCount ( List ; "¶" & __PadTime (A + (U*202)) & "¶") = 0; 202;

PatternCount ( List ; "¶" & __PadTime (A + (U*203)) & "¶") = 0; 203;

PatternCount ( List ; "¶" & __PadTime (A + (U*204)) & "¶") = 0; 204;

PatternCount ( List ; "¶" & __PadTime (A + (U*205)) & "¶") = 0; 205;

PatternCount ( List ; "¶" & __PadTime (A + (U*206)) & "¶") = 0; 206;

PatternCount ( List ; "¶" & __PadTime (A + (U*207)) & "¶") = 0; 207;

PatternCount ( List ; "¶" & __PadTime (A + (U*208)) & "¶") = 0; 208;

PatternCount ( List ; "¶" & __PadTime (A + (U*209)) & "¶") = 0; 209;

PatternCount ( List ; "¶" & __PadTime (A + (U*210)) & "¶") = 0; 210;

PatternCount ( List ; "¶" & __PadTime (A + (U*211)) & "¶") = 0; 211;

PatternCount ( List ; "¶" & __PadTime (A + (U*212)) & "¶") = 0; 212;

PatternCount ( List ; "¶" & __PadTime (A + (U*213)) & "¶") = 0; 213;

PatternCount ( List ; "¶" & __PadTime (A + (U*214)) & "¶") = 0; 214;

PatternCount ( List ; "¶" & __PadTime (A + (U*215)) & "¶") = 0; 215;

PatternCount ( List ; "¶" & __PadTime (A + (U*216)) & "¶") = 0; 216;

PatternCount ( List ; "¶" & __PadTime (A + (U*217)) & "¶") = 0; 217;

PatternCount ( List ; "¶" & __PadTime (A + (U*218)) & "¶") = 0; 218;

PatternCount ( List ; "¶" & __PadTime (A + (U*219)) & "¶") = 0; 219;

PatternCount ( List ; "¶" & __PadTime (A + (U*220)) & "¶") = 0; 220;

PatternCount ( List ; "¶" & __PadTime (A + (U*221)) & "¶") = 0; 221;

PatternCount ( List ; "¶" & __PadTime (A + (U*222)) & "¶") = 0; 222;

PatternCount ( List ; "¶" & __PadTime (A + (U*223)) & "¶") = 0; 223;

PatternCount ( List ; "¶" & __PadTime (A + (U*224)) & "¶") = 0; 224;

PatternCount ( List ; "¶" & __PadTime (A + (U*225)) & "¶") = 0; 225;

PatternCount ( List ; "¶" & __PadTime (A + (U*226)) & "¶") = 0; 226;

PatternCount ( List ; "¶" & __PadTime (A + (U*227)) & "¶") = 0; 227;

PatternCount ( List ; "¶" & __PadTime (A + (U*228)) & "¶") = 0; 228;

PatternCount ( List ; "¶" & __PadTime (A + (U*229)) & "¶") = 0; 229;

PatternCount ( List ; "¶" & __PadTime (A + (U*230)) & "¶") = 0; 230;

PatternCount ( List ; "¶" & __PadTime (A + (U*231)) & "¶") = 0; 231;

PatternCount ( List ; "¶" & __PadTime (A + (U*232)) & "¶") = 0; 232;

PatternCount ( List ; "¶" & __PadTime (A + (U*233)) & "¶") = 0; 233;

PatternCount ( List ; "¶" & __PadTime (A + (U*234)) & "¶") = 0; 234;

PatternCount ( List ; "¶" & __PadTime (A + (U*235)) & "¶") = 0; 235;

PatternCount ( List ; "¶" & __PadTime (A + (U*236)) & "¶") = 0; 236;

PatternCount ( List ; "¶" & __PadTime (A + (U*237)) & "¶") = 0; 237;

PatternCount ( List ; "¶" & __PadTime (A + (U*238)) & "¶") = 0; 238;

PatternCount ( List ; "¶" & __PadTime (A + (U*239)) & "¶") = 0; 239;

PatternCount ( List ; "¶" & __PadTime (A + (U*240)) & "¶") = 0; 240;

PatternCount ( List ; "¶" & __PadTime (A + (U*241)) & "¶") = 0; 241;

PatternCount ( List ; "¶" & __PadTime (A + (U*242)) & "¶") = 0; 242;

PatternCount ( List ; "¶" & __PadTime (A + (U*243)) & "¶") = 0; 243;

PatternCount ( List ; "¶" & __PadTime (A + (U*244)) & "¶") = 0; 244;

PatternCount ( List ; "¶" & __PadTime (A + (U*245)) & "¶") = 0; 245;

PatternCount ( List ; "¶" & __PadTime (A + (U*246)) & "¶") = 0; 246;

PatternCount ( List ; "¶" & __PadTime (A + (U*247)) & "¶") = 0; 247;

PatternCount ( List ; "¶" & __PadTime (A + (U*248)) & "¶") = 0; 248;

PatternCount ( List ; "¶" & __PadTime (A + (U*249)) & "¶") = 0; 249;

PatternCount ( List ; "¶" & __PadTime (A + (U*250)) & "¶") = 0; 250;

PatternCount ( List ; "¶" & __PadTime (A + (U*251)) & "¶") = 0; 251;

PatternCount ( List ; "¶" & __PadTime (A + (U*252)) & "¶") = 0; 252;

PatternCount ( List ; "¶" & __PadTime (A + (U*253)) & "¶") = 0; 253;

PatternCount ( List ; "¶" & __PadTime (A + (U*254)) & "¶") = 0; 254;

PatternCount ( List ; "¶" & __PadTime (A + (U*255)) & "¶") = 0; 255;

PatternCount ( List ; "¶" & __PadTime (A + (U*256)) & "¶") = 0; 256;

PatternCount ( List ; "¶" & __PadTime (A + (U*257)) & "¶") = 0; 257;

PatternCount ( List ; "¶" & __PadTime (A + (U*258)) & "¶") = 0; 258;

PatternCount ( List ; "¶" & __PadTime (A + (U*259)) & "¶") = 0; 259;

PatternCount ( List ; "¶" & __PadTime (A + (U*260)) & "¶") = 0; 260;

"")

)

The CF accommodates for 5 minute intervals. The TimeList value needs to be sorted and formated like so:

08:00:00

08:05:00

08:10:00

08:15:00

etc...

Is there away to recompile this CF as a recursive CF that is much cleaner and faster.

[comment]

The more you explain this, the less I understand it. Most of all, I don't understand the purpose. If you have appointments with date, start time, end time and EmployeeID, you can define a relationship to include all appointments of an employee within a given range simply by:

MAIN::gEmployeeID = Appointments::EmployeeID

AND

MAIN::gRangeStart < Appointments::cAppointmentEnd

AND

MAIN::gRangeEnd > Appointments::cAppointmentStart

where the start and the end on both sides are timestamps.

I also didn't get the part about having a parent record for all employee's appointments for a day. IIUC (big IF), you want to establish a relationship to this parent record, based on a calculation derived from the children. This won't work, since such calculation must be unstored.

Edited March 29, 2008 by Guest
typo

[dwdata]

Thanks for the help. I am sorry but I fear explaining the architecture of my system would just cause greater confusion to the solution I am trying to find.

I have revamp things a bit and I am hoping this will make things easier. Thanks for your patience ;-)

I am trying to build a recursive custom function like the following:

BuildKEY(TimeList)

If the TimeList is:

07:00:00

07:05:00

07:10:00

07:15:00

07:20:00

07:25:00

07:30:00

07:35:00

07:40:00

07:45:00

07:50:00

07:55:00

09:00:00

09:05:00

09:10:00

09:15:00

09:20:00

09:25:00

I need to compile a list that will:

#1) Determine how many time lines proceed each line of the TimeList WITHOUT A BREAK IN THE SEQUENCE. Here is the result of the TimeList above:

07:00:00 = 12

07:05:00 = 11

07:10:00 = 10

07:15:00 = 9

07:20:00 = 8

07:25:00 = 7

07:30:00 = 6

07:35:00 = 5

07:40:00 = 4

07:45:00 = 3

07:50:00 = 2

07:55:00 = 1

09:00:00 = 6

09:05:00 = 5

09:10:00 = 4

09:15:00 = 3

09:20:00 = 2

09:25:00 = 1

#2) Then based on these RESULTS generated, I need a to compile a list that takes each line of the TimeList and repeats it with a space and decrementing index number concatenated to it. The final result will look like this:

07:00:00 12

07:00:00 11

07:00:00 10

07:00:00 9

07:00:00 8

07:00:00 7

07:00:00 6

07:00:00 5

07:00:00 4

07:00:00 3

07:00:00 2

07:00:00 1

07:05:00 11

07:05:00 10

07:05:00 9

07:05:00 8

07:05:00 7

07:05:00 6

07:05:00 5

07:05:00 4

07:05:00 3

07:05:00 2

07:05:00 1

07:10:00 10

07:10:00 9

07:10:00 8

07:10:00 7

07:10:00 6

07:10:00 5

07:10:00 4

07:10:00 3

07:10:00 2

07:10:00 1

07:15:00 9

07:15:00 8

07:15:00 7

07:15:00 6

07:15:00 5

07:15:00 4

07:15:00 3

07:15:00 2

07:15:00 1

07:20:00 8

07:20:00 7

07:20:00 6

07:20:00 5

07:20:00 4

07:20:00 3

07:20:00 2

07:20:00 1

07:25:00 7

07:25:00 6

07:25:00 5

07:25:00 4

07:25:00 3

07:25:00 2

07:25:00 1

07:30:00 6

07:30:00 5

07:30:00 4

07:30:00 3

07:30:00 2

07:30:00 1

07:35:00 5

07:35:00 4

07:35:00 3

07:35:00 2

07:35:00 1

07:40:00 4

07:40:00 3

07:40:00 2

07:40:00 1

07:45:00 3

07:45:00 2

07:45:00 1

07:50:00 2

07:50:00 1

07:55:00 1

09:00:00 6

09:00:00 5

09:00:00 4

09:00:00 3

09:00:00 2

09:00:00 1

09:05:00 5

09:05:00 4

09:05:00 3

09:05:00 2

09:05:00 1

09:10:00 4

09:10:00 3

09:10:00 2

09:10:00 1

09:15:00 3

09:15:00 2

09:15:00 1

09:20:00 2

09:20:00 1

09:25:00 1

If this is not clear, please let me know. My email is [email protected] if you want to contact me privately.

[comment]

Well, I still don't see what purpose this might serve, but what the heck - it seems simple enough:

dwCF.fp7.zip

[mr_vodka]

Okay lets start over with the issue. Forget the custom function for now. I still cant see the purpose of what you are trying to do. If you are trying to build a scheduling app, I believe there would be a better way to approach it than the way you are trying to go.

[mr_vodka]

Tisk tisk... supporting bad habits I see. :)

[comment]

supporting bad habits I see.

To some extent, maybe. But I needed the practice, and Mr. Don Wieland is presumed to be knowledgeable enough to fend for himself...

[dwdata]

Thanks for the CFs. I really appreciate it. My hopes is that the work well for me.

As far as architecture, well as I mentioned earlier, I did not feel it was necessary to give you whole cake when all I was trying to achieve was adding the egg to the batter ingredients ;-)

Comment and Mr. Vodka:

For you efforts and feedback, if you have a copy of Timbuktu Pro and you want to personally contact me off-line. I'd be glad to take you on a 5 minute tour of my architecture so it will all make sense to you. I would not want you to lose sleep on this ;-)

Once again thanks for your time and I hope I can make it up to you in the future.

Don