A bit confused with self-joins

[Mark Mcdonald]

I'm trying to figure out the best way to return a subset of records by, in this case, a date. I could make a script to find all records with a specific date but there's a ton of records and it's fairly slow to do so. So what I was hoping was that I could use relationships instead. I've tried using a self-join with the Record Date matching to a global date field that I'd use as the filter. This gives odd results.

Are self-joins the wrong approach or is it a matter of incorrect execution?

[*susan*]

Mark,

Searching on a date field shouldn't be slow. Is the date field an unstored calculation? If so, it won't work as the "right" side of a relationship.

Assuming that the date field is stored, you can have a global date field [gDateSelect] with a relationship to dateField. At this point, you could Go To Related using this relationship, or show the resulting records in a portal.

The relationship should look something like this:

TABLEA-gDateSelect == TABLEA-dateField

Is this helpful?

*susan*

[Mark Mcdonald]

Hi Susan,

Yes, this helps - however it returns to using portals which I think are user-interface nightmares. For the work I'm doing I prefer using the table view since it allows more fluid re-arrangement of information. Is there a way to enable portal-like functionality in a table view?

Thanks!

Mark

[*susan*]

If you don't want a portal, go to the related records to display in table view.

*susan*

[Mark Mcdonald]

Thanks, I'll try that. Somewhat relatedly, I'm using some basic calculation fields to determine whether a field has changed given a start date and and end date (using self-joins). The calculation itself works fine but searching against the result is dead slow. I've created an indexed field to cache the result but it has to be refreshed every time the date parameters change (this takes a while). Is there a better way to approach this?

[comment]

Can you explain what exactly the calculation does?

[Mark Mcdonald]

Sure - it's pretty simple - along these lines:

If (mainTable_selfJoin1::Data = mainTable_selfJoin2::Data) ; 0 ; 1)

Thanks,

m

Edited April 1, 2009 by Guest

[comment]

I am afraid that's not clear enough. mainTable_selfJoin2 is a field?

I suspect you don't need this calculation and that you could just search the field where your original stored data is - but I cannot say for sure without a better description.

[Mark Mcdonald]

Sorry typo - I've edited it. I'm checking the content of a field on one date against itself on a second date. I'm using the self-joins for display reasons primarily - is there a way to do this calculation without using references?

Sorry if I'm a bit fuzzy, self-join type issues make my head hurt.

[comment]

It makes my head hurt too, because I don't see your relationships - so I cannot understand what the calculation does. Could you explain what is the actual purpose behind this?