Jump to content
View in the app

A better way to browse. Learn more.
FMForums.com

A full-screen app on your home screen with push notifications, badges and more.

To install this app on iOS and iPadOS
  1. Tap the Share icon in Safari
  2. Scroll the menu and tap Add to Home Screen.
  3. Tap Add in the top-right corner.
To install this app on Android
  1. Tap the 3-dot menu (⋮) in the top-right corner of the browser.
  2. Tap Add to Home screen or Install app.
  3. Confirm by tapping Install.

Time_Key with breaks in sequence

dwdata
in Custom Functions Discussions

Featured Replies

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 ;-)

 

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

  • Author

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

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

  • Author

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!!!

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

  • Author

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!

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
  • 4 weeks later...

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

  • Author

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!

Create an account or sign in to comment

https://fmforums.com/topic/87568-time_key-with-breaks-in-sequence/
Go to topic listing

Important Information

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

I accept

Configure browser push notifications
Chrome (Android)
  1. Tap the lock icon next to the address bar.
  2. Tap Permissions → Notifications.
  3. Adjust your preference.
Chrome (Desktop)
  1. Click the padlock icon in the address bar.
  2. Select Site settings.
  3. Find Notifications and adjust your preference.