Calculation to Global Field

[AndreM]

Hi everyone!

I've been over this issue for a few days now and I run out of ideias. I was hoping someone could give me some tips.

I have a scheduling database. The user sets a start time and an end time. A global field stores all numbers in the range and compares them to the start and end times on the events table. So far everything works just fine.

Here's my issue: I'd like to dynamically change the end time according to current the account name (Meaning, logged user is the one that scheduled the event; event time-end does not change/ logged user did not schedule this event; time-end + 45 min.) The problem is that I cannot make it a calculation (it cannot be stored) as the relationship between tables will stop working. Any thoughts?

Thanks a lot!

[comment]

A global field stores all numbers in the range and compares them to the start and end times on the events table.

What exactly is the purpose of this? I am not even sure what "all numbers in the range" between two times means.

The problem is that I cannot make it a calculation (it cannot be stored)

You could make it an auto-entered value - this would also enable the user to change the default result.

[AndreM]

I'm using Ray Cologon's technique: to make relationships work. Each date is converted to a number; when the user wants to see what's the availability between, let's say, 09:00 and 00:00 a custom function compares the start/end times (the resulting numbers in fact) with a global field that contains a range of values (one for every minute between 09:00 and 0:00).

Making this field an auto-enter could work, the thing is that, at the time a record is created, user's account name will always match creation account name....

What exactly is the purpose of this? I am not even sure what "all numbers in the range" between two times means.

You could make it an auto-entered value - this would also enable the user to change the default result.

[comment]

This is rather confusing:

User X schedules event A between 10:00 and 11:30.

User Y schedules event B between 12:00 and 13:00.

Now, when both users are viewing the same events, you want User X to see:

Event A 10:00 - 11:30

Event B 12:00 - 13:45

while at the same time User Y sees:

Event A 10:00 - 12:15

Event B 12:00 - 13:00

Presumably, User Y also sees a conflict between the two events, while User X does not.

What purpose does this serve? Perhaps if you explained the reason, another way could be found. As it is, the end time cannot be stored (as you have noted) and availability/conflict checking is defeated.

a global field that contains a range of values (one for every minute between 09:00 and 0:00).

This technique has been obsolete since version 7 (though some continued to use it for a while, for performance reasons). You can check availability directly through a relationship like:

gStartTime ≤ Events::EndTime

AND

gEndTime ≥ Events::StartTime

[AndreM]

Thanks for the tip! I'll skip the numbers comparison and see how it performs. I hadn't noticed this technique had become obsolete..

As for the events, here is the big picture: Say user X scheduled a meeting from 10H00 to 11H00. If this is the case, user X he can schedule a meeting from 11H00 to 12H00. On the contrary, if that first meeting was scheduled by someone else the room won't be available until 11H45. This would work as a dynamic gap between meetings.

Does it make sens to you?

This is rather confusing:

User X schedules event A between 10:00 and 11:30.

User Y schedules event B between 12:00 and 13:00.

Now, when both users are viewing the same events, you want User X to see:

Event A 10:00 - 11:30

Event B 12:00 - 13:45

while at the same time User Y sees:

Event A 10:00 - 12:15

Event B 12:00 - 13:00

Presumably, User Y also sees a conflict between the two events, while User X does not.

What purpose does this serve? Perhaps if you explained the reason, another way could be found. As it is, the end time cannot be stored (as you have noted) and availability/conflict checking is defeated.

This technique has been obsolete since version 7 (though some continued to use it for a while, for performance reasons). You can check availability directly through a relationship like:

gStartTime ≤ Events::EndTime

AND

gEndTime ≥ Events::StartTime

[comment]

I think you are talking about two separate things here:

User X scheduled Event A using Room 11 between 10:00 and 11:00.

IIUC, User X becomes available at 11:00 but Room 11 is available only from 11:45.

If the same table is used for scheduling people and rooms, then you need two end-times:

• EventEndTime (Time) - set by the user;

• RoomEndTime (Calculation, stored) = EventEndTime + 2700

and use a different end-time in different contexts.

BTW, if User X wants to schedule another meeting in the same room starting at 11:00 - is the room available?

[AndreM]

You've got a good point. I thought on that solution myself but I'm not sure I can find a way to make it work.

Events table holds information for both people and rooms. From Events table I can figure out which rooms are not available and with a simple relationship (Events::Rooms_fk is not equal to Rooms::Name) I get a list of all rooms that are available between a certain period of time. The issue is getting different end times according to the logged user on the same portal. If I place the account name on global field and compare it with the creation account name I'll loose the relationship, if I place a get(AccountName) it won't work either because it cannot be indexed.. it seems a dead end..

how were you planning to set the different contexts?

BTW, if User X wants to schedule another meeting in the same room starting at 11:00 - is the room available?

That was the idea. If User X has created this meeting, he can schedule that same room any time after the first meeting's end time, no gap needed. Any other user would have to wait until 11:45 (so that rooms can be cleared in time and no one bumps into each other).

I think you are talking about two separate things here:

User X scheduled Event A using Room 11 between 10:00 and 11:00.

IIUC, User X becomes available at 11:00 but Room 11 is available only from 11:45.

If the same table is used for scheduling people and rooms, then you need two end-times:

• EventEndTime (Time) - set by the user;

• RoomEndTime (Calculation, stored) = EventEndTime + 2700

and use a different end-time in different contexts.

BTW, if User X wants to schedule another meeting in the same room starting at 11:00 - is the room available?

[comment]

If User X has created this meeting, he can schedule that same room any time after the first meeting's end time, no gap needed. Any other user would have to wait until 11:45 (so that rooms can be cleared in time and no one bumps into each other).

Well, then I think you have two choices:

1. Use the relationship to "fetch" all rooms that are available by the meeting's end time. Filter the portal to remove rooms that have been scheduled by another user and their end time + 45 minutes is later than the requested start time.

2. Use the so called "Ugo method" (search the forums for examples).

If I am being a little vague, it's because I don't have a clear picture of what exactly you are doing and in what order. Perhaps a custom function could simplify the matter.

[comment]

I forgot to mention yet another option: use a scripted find, triggered by modifying the request, instead of a portal.