Using date ranges to constrain portal data...

[ViciousTruth]

Hello,

I want to create a portal that has a start date field and an end date field which will constrain the portal data. I'm not sure where to start. How does Filemaker handle dates; for example, is 1/1/2001 lesser or greater in value than 12/1/2009?

When I enter a date range into the date fields, I want the portal to automatically update, so I imagine that I need to do this with my portal relationship. Can anyone give me some clues on how to start?

[comment]

Filemaker handles dates as dates: any date in the year 2001 is lesser than a date in 2009.

Unless you use version 11, filtering a portal means filtering the relationship.

[ViciousTruth]

Hey Comment,

Thanks for the response. I am using Filemaker 11 now: how does 11 deal with this scenario differently?

[comment]

Enter Layout mode, double-click the portal, select 'Filter portal records' and enter your criteria.

P.S. Please update your profile.

Edited May 15, 2010 by Guest

[kenneth2k8]

so let's say I have a portal that shows items and their expiration dates. The items table has a "processed" checkbox field. I want my portal to show items that do not have "processed" checked and the expiration date is past today, or the expiration date has not happened yet.

I made a calculation that shows the FK_CustomerID if the criteria is matched above but found out you can't base relationships on fields that cannot be indexed.

I am unfortunately using fp5 files.

Thank you very much for any suggestions.

[comment]

IIRC, in version before 7 this could be accomplished in two ways:

1. Use the Today function instead of Status(CurrentDate). This enables the calculation to be stored. OTOH, it requires the file to be re-opened in order to recalculate the date in all records.

2. Use Mikhail Edoshin's Smart Ranges method to generate a multi-line key covering the range from today until ... (ideally, until December 31, 3000). I think there was also a plugin for this.

Of course, the easy way would be to upgrade to version 7 or higher, to allow non-equijoins and multiple predicates.

[kenneth2k8]

thank you so much for the reply. I am going to get to work on that today.

If I go from fm6 to, say, 11... is there a utility that might convert my files somewhat easily.

Thanks again

[comment]

Filemaker will convert the files - but depending on your solution there may be additional work to do.

See also:

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

[kenneth2k8]

Thanks again for the reply, and the suggestion on the portal. I worked on it last night and got it how I need it. Much appreciated.