G'day All I'm not sure if this post goes here or in the define fields section,

I have a booking database that relies on the concatenation of three fields to create a unique key (timeslot) for each booking which is "time"&"room"&"date"

On my booking page I have created global fields for time, room and date to temporarily hold the information while I check if this timeslot is available.

What is the best way check if the timeslot is full or unavailable?

I'm doing it this way because I don't want to create a new record first and then do something like this: If count(SelfJoin:key)>1 then don't book.

I had this working but it was confusing for users and didn't quite work when you couldn't book a room.

Now I am using globals I can't index the field to do the selfjoin.

Any suggestions greatly appreciated

Cheers

Darrel

Hi Darrel ...

Maybe I'm not understanding exactly what you're trying to do, but it seems you want to check whether the concatenated values you enter into the global fields match an existing record. If there is a match, then the booking cannot be made. If there is no match, then the booking can proceed.

Globals work just fine on the left side of the relationship. The value on the left side of the relationship does not have to be indexable.

So, you'd create a calculation ("c_g_booking") to concatenate your g_time, g_room and g_date fields in exactly the same way you are currently concatenating your time, room and date fields ("c_booking"). "c_booking" will be indexable, since it's simply a concatenation of your three existing fields.

Then create a relationship ("available?") between "c_g_booking" on the left and "c_booking" on the right.

A calculation field along the lines of the following formula will display the status of the relationship:

Case(

IsValid(available?:time), "Not Available",

"Available"

)

where "time" is the time field in your bookings file.

Alternatively, when the user clicks a button to check availability, you could have a Show Message script step to display "Sorry, this room is not available for the date and time entered." if there is a matching concatenated key in your bookings file.

Hope this helps.

What is Time ? A Time field or a Time range ?

Ugo ...

I was going to ask the same question -- about time/date ranges -- because I believe most booking applications use ranges rather than single date/time values. But, I didn't want to complicate the issue for the poster. If he needs ranges, he'll quickly figure out that the way he's doing it won't work.