Date range calculations

[merkaba22]

I know how to create a relationship by date fields in the parent and child tables, but am unclear on how to create a relationship based on a calc for a date range, ie the way the calc would be written -- I think globals would be involved.

I wonder if there are any readably apparent calcs I could use?

Thanks in advance:)

[merkaba22]

In followup, I suppose I could create a global wherein the user types in the field the values comprising two dates seperated by three periods, Date1 ... Date2, to cover a range of dates and build the relationship on that pre-existing FM find feature, but wonder if there is a better way these days?

[Andy H]

Hi,

Try looking at the below post. I think that should point you in the right direction:

http://fmforums.com/forum/showtopic.php?tid/209693/post/333941

[merkaba22]

In other examples I can create a key field based on a calc for the portalID&date which will return all the records for this combination, for example:

I specify portalID as "1" related to the parent file via parentID defined also as "1", the relationship becomes dynamic based on the date entered into "date" and shows all records for that specific date in the portal.

I am looking for a daterange_calc definition that provides a range of dates that the user can select, say date1 and date2, such that all the records for the dates within the range between date1 and date2 will be shown in the portal.

Thank you in advance.

Edited March 11, 2010 by Guest

[IdealData]

In the parent table you will need 2 globals and set up the relationship like this

gStartDate <= PortalDate

gEndDate >= PortalDate

[Ocean West]

Another option...

I have 1 global / 1 relationship

and 2 custom functions in my solution along with a script

I am using MagicDates ( ) and CustomList ( )

Note: in this solution the Custom List is renamed to list.custom

[merkaba22]

Ideal Data!

This worked perfectly for the application and I wonder if there is a way to expand:

gStartDate <= PortalDate

gEndDate >= PortalDate

I added in another global variable field to the parent, say, "Location" and a calc field, c = Location:

(Case(IsEmpty(Location= 1); ""; Location))

Added to the definition of the relationship:

gStartDate <= PortalDate

gEndDate >= PortalDate

gLocation_calc = Location

And this works but when I do not give a value to "Location", there are no records shown in the portal at all -- is there a way to modify this so that all records are returned and that when a value is assigned it filters accordingly?

The idea being I could then offer more variables, like "service" to further filter the display in the portal.

Thanks again!