Self-Join relationship

[ibmacguru]

I am trying to write a calculation that shows overlap in time.

I have a field for start time and one for end time. I need to identify where there is an overlap in these times when comparing records. I've been trying to use a self-join relationship and a Case statement. I

[RalphL]

There are 3 posible conditions for an overlap. Using A & B as the 2 time periods being compared.

Case( StartA >= StartB and StartA <= EndB, 1,

EndA >= StartB and EndA <= EndB, 1,

StartA <=StartB and EndA >= EndB, 1,

0)

[Ender]

ibmacguru,

If you were talking about relating to records who's time ranges overlap, then the solution would be like this:

Using FM7, you would use a multi-criteria relationship with greater than/less than relational operators.

In FM6, it would be more complex. You'd probably have to look at the Smart Ranges technique if there is a wide variation in the possible time periods. If the possible time periods are fixed, you could do an easier multi-key, assembled from whether or not each time slot was used.

[bobearl]

Pardon the low-level question, but . . . head hurts from banging into wall. I am missing something fundamental about self-joins, and wondered if anyone had a good reference or a simple example of one that works. What I'm specifically trying to do is to use a portal on a found set. The advice I got was to use a self-join with a global field to make only the found set appear. I got this to work after a fashion, but I got the unfound records as well, albeit with blank fields. I decided to use a simpler example to practice self-joins, so I borrowed an example from Filemaker Pro 7 Tips (Moyer Group, March 2004) (attached, see pages 11-12). It is a phenomenal example showing how to cascade a name and ID field to create a family tree using different Table Occurrences to represent the father, grandfather, and so forth. However, when I tried to introduce a global field to, say, show me the person whose grandfather was X, it all fell apart.

My question is this: is there a good reference, or a particularly enlightening discussion of self-joins that might help newbies like me figure out how to use self-joins? Even a solid reference on relationships would be helpful - Jonathan Stars new book was tremendously helpful, but the relevant chapter is still a little too abstract for thick heads like mine. Thanks in advance!

[-Queue-]

In order to use a portal for a found set, you have to save all the ids of the 'found' records into a global and use a relationship from this global to the id field. It sounds like this is what you were advised to do, but it's unclear how detailed the advice was.

There really isn't any difference between self-joins and regular relationships. The criteria defined for the self-relationship determines which records in the same table are related instead of those in another table. This is the only difference.

If you peruse the Relationships forum and/or do a search on 'relationship', I daresay you will find more information than you probably would in the average FM book discussing the subject. Plus you will find dozens of sample files, some with great detail into how they work, and some tricks that you probably won't find in an FM book. The basic concept is fairly simple. When fieldA matches fieldB a relationship is valid. This relationship allows you to view or manipulate records from another table or the same table that meet the relationship criteria. In version 7, this concept is stretched, with the addition of >, <, >=, <=, <>, and x operators, allowing for much greater limiting or broadening of criteria. But the way they work is the same. I would suggest getting a firm grasp of the simple '=' relationship before attacking the other, more powerful ones. Once you experiment some, you'll see that they really aren't that difficult to comprehend. It just takes some practice learning how to utilize them most efficiently.

Good luck! Feel free to ask more specific questions when you have the need.

[bobearl]

Thanks very much for the helpful thoughts. I agree, the forums are just tremendous - I have to say, though, I frequently feel like a chimp trying to fix a VCR when I look at the example files, but I probably just need a little more patience. I think I just need to do exercises to get sharper at this, because just about everything seems conceptually simple, but getting things to do what I want them to do . . . another matter.

[-Queue-]

I know how you feel. Four years ago I was asked to pick up where someone left off and design a fairly elaborate system for a company with 8 offices on the west coast. I'd only had a few weeks of FM experience. It took much trial and error to make it work. If I had known about the forums back then, it might not have taken three years to bring it up to par.