Date handling with relationships

May 26, 200619 yr

I am trying to create a summary page that includes all events (in portals)accross multiple tables (vacations, events, to dos) that are equal to todays date. I was able to get the basics to work but there is an issue with events as follows;

An event can have a range (start date to end date) and then frequency (monthly, bi-monthly, weekly, daily) and then can have the day of the week it occurs on. So the trick I am missing is how to let FMP know that the event happens to occur equal to todays date. Any help would be appreciated.

May 26, 200619 yr

There are a couple ways to make a relationship with date ranges for events, but adding recurrences to the mix complicates things quite a bit, as we discussed in this thread:

http://fmforums.com/forum/showtopic.php?tid/124596/

A different approach uses scripts to create separate Event records for each recurrence, as John indicates in this thread:

http://fmforums.com/forum/showtopic.php?tid/176396/

It's hard to say what you should do without knowing what method you're using and what you've got so far. Maybe you can post a sample.

2 weeks later...

June 5, 200619 yr

I have a similar question.

I am trying to match a single record in a transaction table with a date range (a start date and an end date) in a rate table. I want to display the matching rate on a transaction record, depending on it's date field.

This is a simple AND relationship in FM7, but the tables I'm working with are in FM5.

Besides converting them to FM7, what's my best approach to setting up this relationship?

June 6, 200619 yr

Have you tried the Smart Ranges technique?

http://www.onegasoft.com/tools/smartranges/

June 7, 200619 yr

Thanks for that, Vaughn. Somehow I missed the smartranges formulas.

Now that I have created range keys for my two files, what is the best way to link them? The documentation suggests a combined key, but I'm not sure what needs to be combined.