Relationship Help

[Steve Martino]

Hello Forum.  Having a little trouble with a Cartesian join (or self-join), pic attached.

I have a layout based on a table Event_attendees.  This table is a join between Events & People. I am trying to put a portal on the Event_attendees layout that will show the other events that attendee has attended.  It's not entirely necessary, but would come in handy.  At this point, I'd rather learn how to make it work, just for my own knowledge.

When I put this portal on the People table it works fine.  To put it on the Event_attendees layout I need a second TO (Event_attendees_other-event).  I tried (obviously not correctly) a second TO of Event_attendees, and a second TO of People, many different ways, with no luck.  I think I need to accomplish this with a whole second TO group, but am just stuck on how.  There may be even another way, but it's beyond my nubie-ness

Any help, guidance or criticism is always appreciated.

Thanks

Steve

[Wim Decorte]

A couple of things:

- a cartesian join is not the same as a self-join.  In your case it serves two purposes

- for that reason you may want to adopt  the Anchor/Buoy method to avoid having to worry about the bi-directional aspect of the FM relationships

http://www.kevinfrank.com/anchor-buoy.html

- under the A/B rule you never base a layout on a buoy TO, only on an anchor so you always - positively - know what related data you can show.  I think this is exactly what you need at this point

 

 

[Steve Martino]

Thanks for your reply.  I'll give it a look.  Anything obvious popping up?

[Agnes Riley]

I highly recommend you pick up a method for the madness above. Wim's recommendation is solid, as usual.

 

I might just add one thing: there are other methods out there. The Anchor Buoy or Squid method is the simplest and easiest (imho) but might be worth taking a look at the other ones, as well. Ray Cologon wrote a white paper years back to compare the methods.

If you want to show other events attended by the same attendee you just need to show the events in a portal and filter/connect by the attendee's ID.

Edited July 13, 2015 by Agnes Riley

[Wim Decorte]

Agreed with Agnes.  A/B is jus tone of the ways to organize the graph - and your thinking about it.   Pick one method that you like and go with it for now.  But it is important that if you pick one, you document it and stick with it until you find a better one.

[comment]

I am trying to put a portal on the Event_attendees layout that will show the other events that attendee has attended. 

To answer your question without any reference to your relationships graph: define a self-join of the Event_attendees table based on matching:

Event_attendees::PersonID = Event_attendees 2::PersonID

And another occurrence of the Events table to the RG and relate it to Event_attendees 2 using:

Event_attendees 2::EventID = Events 2::EventID

Now you can place a portal to Events 2 on the Event_attendees layout to show all events that the attendee has attended (including the event associated with currently viewed record). To exclude the current record's event, change the first relationship's definition to:

Event_attendees::PersonID = Event_attendees 2::PersonID
AND
Event_attendees::SerialID ≠ Event_attendees 2::SerialID

 

 

 

 

[Steve Martino]

@‌Agnes & Wim.  Thanks for the pointers.

@‌Comment-Thanks...That did it!  I was dancing all around it, and your post was the solution.  One point that may or may not be easily solvable-

In the portal  are 4 fields: Date, Event Name, Number Invited, Number Attended.  The Date & Event populate correctly, the Number Invited and Number Attended only use the numbers from the record I am on-so all the numbers are the same. 

Double checked the field references, they are just number fields.

Thanks again for your help

[comment]

the Number Invited and Number Attended only use the numbers from the record I am on

All the fields in the portal should come either from the Event_attendees 2 or Events 2 table occurrences.

[Steve Martino]

Thanks again Comment, I just cant get those fields to display.  But I did figure it out, the DB was corrupt.  I spent some time going thru the back ups to find one working correctly.

I can't pinpoint the source, but these corruption issues pop up whenever I work on the DB from home on my Windows 8 laptop.  I saw some issues when I use the peer to peer setup to check things in real time on my iPad.  I noticed when I close the DB on the laptop, there seems to be a lag in saving changes.  IOW, I open it right back up and some of the changes I made weren't there.

Same thing happens if I closed a file,  and immediately copied it to dropbox.

I notice if I close a file, then keep rolling over the icon, the tooltip doesn't show the 'Date Modified' time as correct for about 15 seconds.  Guess I need to let it sit before copying it to Dropbox and onto a thumbdrive for backup.

Thanks again for your help

Steve