Jump to content
Sign in to follow this  

Date field for repeating/nonrepeating dates

Recommended Posts

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,


Share this post

Link to post
Share on other sites

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?

Share this post

Link to post
Share on other sites

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.

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  


Important Information

By using this site, you agree to our Terms of Use.