date ranges and portals

[nlo]

Hi all

I have a problem with a solution I try to build and I need some help.

I have a CalendarDB and a ProjectsDB.

CalendarDB has 2 layouts, "Monthly" and "Daily". Clicking on a date - field "CalendarDate" - in the "Monthly" layout, it brings to front the "Daily" layout for that date, that contains among other info and a portal which displays info from ProjectsDB or creates records for the ProjectsDB.

ProjectsDB contains the fields "StartDate" and "DueDate"

There is a relation "C_to_P", which relates "CalendarDate" from "CalendarDB" with "StartDate" from ProjectsDB.

The result is that I can see only projects that start on that date, when I am in the "Daily" layout, while I need to see all the projects for which StartDate <= CalendarDate <= DueDate, in order to know if a new project can start on that date.

Is there a way to do that?

Any ideas will be very helpfull...

[kennedy]

If the projects aren't too overly long, you can have a calculation field that computes all dates of the project from the start and due dates... and basically form that up as a multi-key... do a search in the forums on "multi" and you should find numerous threads discussing how to use multi-keys.

HTH.

[nlo]

Hi Kennedy,

Thanks for your answer

Unfortunatelly,as you noticed,using a multi key, based on a calculation field, is not the right solution, because about 30% of the projects in my ProjectsDB, will last for more than 3 months - some of them may last for 1 year or more.

Do you think that if I create a script, that will perform a find in the ProjectsDB for the correct records, copy them in a "TempProjectsDB" and relate that file with "CalendarDB", would be a solution? I am afraid the performance in that case would be very poor.

[kennedy]

Such a script will perform reasonably quick... but you don't want to be calling it dozens of times... that would be a performance problem. So, the key is to figure out what you do most rarely.

For example, if the project start and due dates change rarely, then I would associate a script with those fields such that whenever either is changed, you go through and adjust all the Calendar records in between. On Calendar record, have a field "Project" which has the ID of the Project that is running on that day. That is the field you update in that script.

Now, when displaying various reports or when running scripts analyzing where to schedule the next project, you have *instant* knowledge of what days are running what project and what days are free. Great performance.

Now if you are doing a schedule optimization program where you are adjusting start and due date hundreds of times, then such structure may be very bad. In fact, I'd wager that your whole "calendar" structure needs to be replaced with a very different structure in that case.

HTH.

[Somnimedia]

SmartRanges:

http://onegasoft.com/tools/smartranges/index.shtml

I have it built in to my FMP template.