I have a FM 5.5 db that we have used for some program listings on our website using php. We are looking at creating a lobby message board that we would use the same database and would show the events listed for that date. What is not in the db is a list of weekly programs that do not change, they are always on Wed @ 3 pm, Thu @ 10:30 am, etc. I'm trying to incorporate them into my db but am having problems with the date field (we'll call that field webdate).

My first thought to get this working is instead of creating a new record for each week's static program I would simply update the date field for each program record. 5 programs, only 5 records needed. I am using the mod function referenced against a previous date. I have a date field with a date of the same weekday, say 1/2/2007, as Wed's program. If the mod of today's date matches the mod in the above list date field then that event should be listed on the message board, if it doesn't match then the mod simply takes the old date. I can get that piece to work.

My problem that I can't seem to figure out how to then get today's date in the webdate field without screwing up the existing dates. I originally had webdate set as a Date type but that doesn't work when these new programs are added. I tried changing the type to Calculation and tinkering with the formula but I always seem to erase the webdate fields for the existing records.

At this point I'm stumped. Am I on the right track with using the mod function or should I go a different route? I realize I've typed quite a bit here hopefully you can make sense of what I'm trying to accomplish.

Any help is appreciated,

Nick

I'm trying to make sense of this... maybe what you need in your calculation is a Case statement to deal with the "today's date" problem?

I don't think the calculation itself is difficult - but if it relies on current date, it cannot be used for a relationship. You might get around that in v.5 by using the Today()function, but it's not future-proof.

I would have a flag field (0 or 1) called Weekly, and a calculation (result is Text) =

Case ( Weekly , DayName ( ProgramDate ) , ProgramDate )

Then on the calendar side of the relationship another calculation (result is Text) =

CalendarDate & "¶" & DayName ( CalendarDate )

A relationship matching the two calculation fields will show both programs scheduled for that calendar date, and weekly programs scehduled for the day of week of calendar date.