I have a layout that contains a portal. The portal shows rows which contain dates. The master table (the "one" table in the "one-to-many" relationship) has two global fields, gStartDate and gEndDate.
I would like to do a find that would select all the records in the master table, that have rows in the related table, that fall within these two dates.
What is the correct way to do this?
Thanks very much in advance to all for any info!
There are a couple of ways to do this, the best one will depend on your particular database:
If the relationship is set up such that
mastertable::index -----< portaltable::mastertableIndex
Then you can simply perform a date range find in the portal on the master table by hand or by script. This would look like:
1/1/2013...12/31/2013
in the portal's date field, assuming you are in the U.S.
When you perform the find, the found set of master records, will all contain a portal record in that date range.
You can also set up a more complex relationship, as you indicated with your own answer. That relationship would look like this:
mastertable::index = portaltable::mastertableIndex
mastertable::gStartDate <= portaltable::date
mastertable::gEndDate >= portaltable::date
If you use that relationship, you can enter Find mode, put an * into the date field of the portal, and perform the Find. This will, again, find all master records that have dates that fall within the range.