Hi Mark,
Just wanted to update you on how I went with this.
So instead of writing a script to handle the lookup I used an automation and relationship field lookups
Here is a summary;
Starting Fields:
[Booking Code] = Venue ID:Court ID:Date
[Start Code] = [Booking Code] & [Start Time ID] (00:00=1, 00:30=2, etc.)
[Finish Code] = [Booking Code] & [Finish Time ID] (00:00=1, 00:30=2, etc.)
[Duration Code] = [Booking Code] & [Duration Time ID] *each duration time id in the list has the booking code prefixing it ([Booking Code] [Duration Time Code 1] / [Booking Code] [Duration Time Code 2] / etc.)
My automation then copies the codes and writes them into fields stored on the related court;
[Starting Codes]
[Duration Codes]
[Finish Codes]
These fields are also relationship lookup fields into from the Courts table into the Bookings table, so that once you select a venue and court in the booking record, the record can see all the existing booking codes made.
I then created 2 formula checkbox fields to check for Start Code conflicts and Finish Code conflicts;
e.g. If(Contains([Court - Start Codes], [(Formula) Start Code]) or Contains([Court - Duration Codes], [(Formula) Start Code]), true, false); then just set up rules to stop the record from being saved if either checkbox is ticked.
The automation also copies the list of codes in the lookup so it basically it ends up just adding the new booking codes to the list.
------------------------------
Josh Hamilton
------------------------------
