Jump to content
Sign in to follow this  
dwdata

Time_Key with breaks in sequence

Recommended Posts

dwdata    16

I have found a few functions that help build a list like the following (based on 5 minute intervals (300 minutes)):

9:15:00

​9:20:00

​9:25:00

​9:45:00

​9:50:00

​9:55:00

​10:15:00

​10:20:00

​10:25:00

I need a CF to create a key field that loops though this list, detects breaks in sequence, and handles them like the following:

9:15:00

9:15:00 9:20:00

9:15:00 9:20:00 9:25:00

9:45:00

9:45:00 9:50:00

9:45:00 9:50:00 9:55:00

10:15:00

10:15:00 10:20:00

10:15:00 10:20:00 10:25:00

Anyone have a bit of time to assist on this? I appreciate it ;-)

 

Share this post


Link to post
Share on other sites
Opotoc    1

Hi,

 

I think a recursive CF should you the job easily.

 

Compare two Lists

List1 = List of the actual occurences (/may contain breaks)

List2 = List of all potential occurences -> you can generate this this with the CustomList CF of Agnes Barouh.

 

With your CF just simply loop through

List2 with GetValue , check wether the item exists in List1,

if not add it with " " to your CF_List, other wise "¶".

 

I think this should do the trick.

 

Best,

Alexander

Share this post


Link to post
Share on other sites
dwdata    16

Thanks Opotoc,

I was hoping for a more straight forward way. My first post was in accurate in what I need. I was able to SCRIPT the desired effect (see image attachment), but can't wrap me head around how to get in translated into a CF. i know I can just trigger the script upon record commit, but thought it would be cleaner and more dynamic in a CF.
 

Anyone up to the challenge? I appreciate it.

 

Don

 

------

post-92481-0-24383600-1360609749_thumb.j

Share this post


Link to post
Share on other sites
Opotoc    1

Don,

 

all that needs to be done is to put your script loop into a CF:

 

// ReturnOrSpaceReList ( ListA ; ListB )

If (
	IsEmpty ( ListA ) ;
	$theNewList ;
	Let ( [
			$t = GetValue ( ListA ; 1 ) ;
			$theNewList =
			If (
				PatternCount ( ListB ; $t ) ;
				$theNewList & " " & $t ;
				List ( $theNewList ; $t )
			)
		] ;
		ReturnOrSpaceReList ( RightValues ( ListA ; ValueCount ( ListA ) - 1 ) ; ListB )
	)
)

 

ListA is the list with all potential items, ListB are the actual taken items.

 

So this was my coffee break ;).

 

Cheers,

Alexander

Share this post


Link to post
Share on other sites
dwdata    16

Thank Opotoc,

I fed my list though your CF and the results were not what I desired. I fear my first post threw you off. Let me break down the logic:

1) I need to cycle through each line of a Time list.

2) For each line I need to start incrementing the time by 300 seconds (5 minutes) and compare it what is in the list:
     IF in the list, append a SPACE and the incremented time
    ELSE exit and go to the next time in the list

So for this time list:
 

9:15:00

​9:20:00

​9:25:00

​9:45:00

​9:50:00

​9:55:00

​10:15:00

​10:20:00

​10:25:00

 

The result will look like this:

9:15:00
9:15:00 9:20:00
9:15:00 9:20:00 9:25:00
// Break because 9:30:00 is not in list, now go to next time in the list
9:20:00
9:20:00 9:25:00
// Break because 9:30:00 is not in list, now go to next time in the list
9:25:00
// Break because 9:30:00 is not in list, now go to next time in the list
9:45:00
9:45:00 9:50:00
9:45:00 9:50:00 9:55:00
// Break because 10:00:00 is not in list, now go to next time in the list

 9:50:00
9:50:00 9:55:00
// Break because 10:00:00 is not in list, now go to next time in the list
9:55:00
// Break because 10:00:00 is not in list, now go to next time in the list
10:15:00
10:15:00 10:20:00
10:15:00 10:20:00 10:25:00
// Break because 10:30:00 is not in list, now go to next time in the list
10:20:00
10:20:00 10:25:00
// Break because 10:30:00 is not in list, now go to next time in the list
10:25:00
// Break because 10:30:00 is not in list, now go to next time in the list

So removing the comments, it should look live this:



9:15:00
9:15:00 9:20:00
9:15:00 9:20:00 9:25:00
9:20:00
9:20:00 9:25:00
9:25:00
9:45:00
9:45:00 9:50:00
9:45:00 9:50:00 9:55:00 9:50:00

9:50:00 9:55:00
9:55:00
10:15:00
10:15:00 10:20:00
10:15:00 10:20:00 10:25:00
10:20:00
10:20:00 10:25:00
10:25:00


 

Little background on why I needs this for education: I have a solution where the users establishes Availability slots of various duration lengths. They book Appts within those Availability slots. This key will help to detect when an Availability slot still has space to book appts.

Apprecaite the help so far!!!

Share this post


Link to post
Share on other sites
Opotoc    1

Hi Don,

 

In FileMaker this requires two CFs since we cannot nest two functions into one CF:

  • one to group everything that is in the expected sequence (i.e. every five minutes).
  • the second to extend each group into that up-side-down pyramid.
// ReturnOrSpaceReList ( tlist ; _minutes   )
//
If (
	IsEmpty ( tlist ) ;
	"" ;
		Let ( [
				s = Substitute ( tlist ; Char (13) ; "¶" ) ;
				t = GetAsTime ( GetValue ( s ; 1 ) )  ;
				u = GetAsTime ( GetValue ( s ; 2 ) )  ;
				// next value in list
				v = GetAsTime ( t + _minutes * 60 )
				// following the pattern this should be the next value
			] ;
			If ( u = v ; t & " " ;  t & "¶"  )
		)  & 
		ReturnOrSpaceReList ( RightValues ( tlist ; ValueCount ( tlist ) - 1 ) ; _minutes )
)
// DupReturnOrSpaceReList ( dlist  )
If (
	IsEmpty ( dlist ) ;
	"" ;
	List (
		Let (
			x = GetValue ( dlist ; 1 ) ;
			If (
				WordCount ( x ) > 1 ;
				x & "¶" & DupReturnOrSpaceReList ( RightWords ( x ; WordCount ( x ) - 1 ) ) ;
				x
			)
		) ;
		DupReturnOrSpaceReList ( RightValues ( dlist ; ValueCount ( dlist ) - 1 ) )
	)
)
DupReturnOrSpaceReList ( 
ReturnOrSpaceReList ( YOUR_INTEVAL_LIST ; REGULAR_MINUTES  )
)

 

Cheers,

Alexander

Share this post


Link to post
Share on other sites
dwdata    16

Opotoc - Your CFs seems to return the longest lines of the key, but not the ones in between:



9:15:00 9:20:00 9:25:00
9:45:00 9:50:00 9:55:00 9:50:00

10:15:00 10:20:00 10:25:00



* are the line not be returned in the result:

 

9:15:00*
9:15:00 9:20:00*
9:15:00 9:20:00 9:25:00
9:20:00*
9:20:00 9:25:00*
9:25:00*
9:45:00*
9:45:00 9:50:00*
9:45:00 9:50:00 9:55:00 9:50:00

9:50:00 9:55:00*
9:55:00*
10:15:00*
10:15:00 10:20:00*
10:15:00 10:20:00 10:25:00
10:20:00*
10:20:00 10:25:00*
10:25:00*

 

 

Once again, I am using this as the KEY of a right side of a relationship to see if an Appointment can be place within that Availability slot.

So in the CF's current state, there would be NO MATCH for an Appointment from 9:15am to 9:20am which would produce a KEY on the left side on the relationship:

9:15:00 9:20:00

OR 

 

an Appointment from 10:15am to 10:20am which would produce a KEY on the left side on the relationship:

10:15:00 10:20:00

I hope this is clear. If there is a valid match, the system knows there is no conflict and the the appointment can be booked. Once the appointment is booked the Right side KEY is updated OMITTING the key lines of that appointment.

Thanks again!

Share this post


Link to post
Share on other sites
Opotoc    1
Hi Don,
 
I understand forums are for helping not doing the work ;)
 
To get to your desired result all you need to do is to include another CF based on
DupReturnOrSpaceReList
where everything "Right" is "Left".
 
List (
DupReturnOrSpaceReListRIGHT
ReturnOrSpaceReList ( YOUR_INTEVAL_LIST ; REGULAR_MINUTES  )
) ;
DupReturnOrSpaceReListLEFT
ReturnOrSpaceReList ( YOUR_INTEVAL_LIST ; REGULAR_MINUTES  )
)
)
 
Cheers,
Alexander

Share this post


Link to post
Share on other sites
Agnès    6

Hello,

 

I do not understand this in your example, i hope is a mistake ;) :

9:45:00 9:50:00 9:55:00 9:50:00

 

Perhaps this 2 calc are ok  ( used CustomList () )

 

Calc and result 1 :

9:15:00
9:15:00 9:20:00
9:15:00 9:20:00 9:25:00
9:20:00
9:20:00 9:25:00
9:25:00
9:45:00
9:45:00 9:50:00
9:45:00 9:50:00 9:55:00

9:50:00
9:50:00 9:55:00
9:55:00
10:15:00
10:15:00 10:20:00
10:15:00 10:20:00 10:25:00
10:20:00
10:20:00 10:25:00
10:25:00
 

Let ([

$L = YourList ;
$LV = Substitute ( $L ; ¶ ; " " ) ;
$Inter = Time ( 0 ; 5 ; 0 ) ;
$N = GetAsTime ( GetValue ( $L ; 1 ) ) ;
$T = "" ; $Count = 1 ; $Num = 1 ;
$P = CustomList ( 2 ; ValueCount ( $L ) +1  ;
"Let ([
V = GetAsTime ( GetValue ( $L ; [n] )) ;
I = $N + $Inter ; $N = V ; $T = Case ( I = $N ; 1 ) ;
R = Case ( $t <> 1 ; CustomList ( 1 ; $Count ; ""$Count - [n] + 1"" )) ;
$Num = Case ( $T = 1 ; $Num ; [n] ) ;
$Count = Case ( $T = 1 ; $Count ) + 1
]; R )" )

];

CustomList ( 1 ; ValueCount ( $L ) ;
"Let ([
V = GetValue ( $L ; [n] ) ;
$no = [n]
];
CustomList ( 1 ; GetValue ( $P ; [n] ) ; ""MiddleWords ( $LV ; $no ; [n] )"")
)")

)

 

or

Calc and result 2 :

09:15:00
09:15:00 09:20:00
09:15:00 09:20:00 09:25:00
09:45:00
09:45:00 09:50:00
09:45:00 09:50:00 09:55:00
10:15:00
10:15:00 10:20:00
10:15:00 10:20:00 10:25:00

 

Let ([

$L = YourList ;
$Inter = Time ( 0 ; 5 ; 0 ) ;
$N = GetAsTime ( GetValue ( $L ; 1 ) ) ;
$T = ""

];
CustomList ( 1 ; ValueCount ( $L )  ;
"Let ([
V = GetAsTime ( GetValue ( $L ; [n] )) ;
I = $N + $Inter ;
$N = V ;
$T = Case ( I = $N ; 1 ) ;
$Next = Case ( $T = 1 ; $Next & "" "" ) & V
]; 
$Next  )" )
)

really test before take.

 

I do not know why you do this, but if you have a field with this type of calculation and result, you will have slowness in your database

 

Agnès

Share this post


Link to post
Share on other sites
dwdata    16

Thank you Agnès (and everyone) for the time you took on this. I have went an alternate route, but for education this is the break down of the logic:

I have a list of sequential time intervals for availability of a resource on a specific date:

9:00:00
​9:05:00
​9:10:00
​9:15:00
​9:20:00
​9:25:00
​9:30:00
​9:35:00
​9:40:00


If someone books a appt with this resource from 9:15 to 9:30, the list of intervals would UPDATE:



9:00:00
​9:05:00
​9:10:00
​9:30:00
​9:35:00

​9:40:00


I wanted a mechanism where I could build a KEY field to compare what possible combinations of time slots were available for this resource. Basically, I want to take each line of the list above and explode it until I reach a BREAK in the sequence.

For the first value (9:00), the exploded key would look like this:

9:00:00
​9:00:00 9:05:00
​9:00:00 9:05:00 9:10:00

For the 2nd value (9:05), the exploded key would look like this:

9:05:00
9:05:00 9:10:00

 

For the 3rd value (9:10), the exploded key would look like this:

9:10:00

In each of these cases, the break would happen after 9:10.

After the break is reached, it would go to the NEXT time interval in the list. So the end result would be the following (I march where the intervals started):



9:00:00 <-- 1st
​9:00:00 9:05:00
​9:00:00 9:05:00 9:10:00
9:05:00 <-- 2nd
9:05:00 9:10:00

9:10:00 <-- 3rd
9:30:00 <-- 4th
9:30:00 9:35:00
9:30:00 9:35:00 9:40:00
9:35:00<-- 5th
9:35:00 9:40:00
9:40:00 <-- 6th

With such a key established as a RIGHT SIDE KEY, I could create a LEFT SIDE KEY that produced the following:

For an appt of 9:05 to 9:10 -  KEY = 9:05:00 9:10:00  MATCH meaning it would be allowed

For an appt of 9:05 to 9:20 -  KEY = 9:05:00 9:10:00 9:15:00 9:20:00  NO MATCH meaning not allowed

For an appt of 9:30 to 9:40 -  KEY = 9:30:00 9:35:00 9:40:00 MATCH meaning it would be allowed

For an appt of 9:40 to 9:45 -  KEY = 9:40:00 9:45:00 NO MATCH meaning not allowed

Hopefully this shine more light on what I was shooting for.

Thanks!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.