Filtering based on start date and end date

[madman411]

Hi all

I have two portals on a layout displaying two different sets of information. One displays records that have a return date matching the system date, and one displays records that have a start date matching the system date.

The portals are on a layout that references the same table, so I have set up a self join with my booking table. The filter calculation is simply:

Bookings2::Start-date = get(currentdate)

And bookings2::end-date = get(currentdate) for each portal respectively.

Thing is, the portals display no records. Is my calculation too simple?

[comment]

How exactly did you set up the self-join? Do you see any records if you remove the portal filtering?

[madman411]

I comment. 

 

I figured out where I messed up. I had to create an additional SJ relationship and matched the record ID's with the cartesian join symbol. Seems my original two SJ relationships weren't set up correctly to return the proper information.

[comment]

You should try and narrow down the related set as much as possible before applying portal filtering. Relationship predicates are more efficient than portal filtering, since they look at pre-indexed data, while the portal filter has to calculate in real time.
 

[LaRetta]

Listen to Comment here ... using a Cartesian product (X join symbol) relates ALL records to the current record and is very resource-expensive.  To first relate to ALL records and then to use portal filter to narrow the results would be wrong approach.

[comment]

Well, not necessarily "wrong"... I mean, it depends on how many records you have. Sometimes the expense of another TO and/or a calculation field may not be worth it.

[LaRetta]

You are right ... I saw 'bookings' and assumed it would grow, grow quickly and grow large.  :-)

[madman411]

This might be wise. I have about 22,000 records that need to be checked, though on average the most that would be actively "going out" or "returning" would be about 100.

Would two different TOs be necessary? Such as, one TO to reference outgoing items and another for returning?

[comment]

22k records is way too much for portal filtering, IMHO. To save on TOs, you could define a calculation field (result is Date, number of repetitions is 2) =

Extend ( Choose ( Get ( CalculationRepetitionNumber ) - 1 ; StartDate ; EndDate ) )

and use it as the matchfield opposite an unstored calc of Get(CurrentDate). This will reduce the related set to about 100, and you can then use portal filtering to split them up into outgoing and returning.

[madman411]

I figured you'd say that

To understand you a little better:

Create two calculation fields, one with two reps and the calc you supplied (calcdate) and one merely calculating today's date (currentdate). I'm assuming these would fall under my original TO of bookings.

When you say match field, are you referring to the way the existing TO is joined? So, bookings::datecalc = bookings4::currentdate?

[comment]

Yes, except the unstored calc needs to be on "this" side of the relationship - so something like:

 

Bookings::cToday = Bookings #::cBothDates

 

 

You could also use a global field instead of the current date calc and set it by script; that way you can peek at tomorrow's load, if you want.