Filemaker - Stuck in a Relationship!

 I am stuck in a Filemaker Relationship!  Pun Intended.  :)   On previous advice, I created a new Notes table with relationships to our 3 businesses:  Sales, Rentals, and Business Sales, as well as to the Contacts table and Agents table.  This works fine and I can navigate anywhere through "related records."  

However, I tried to get fancy and do the same for the Contacts and Agents (SEE ATTACHED picture of the Relationships diagram).  Since they were already related to Notes, FM forced me to create a 2nd table occurrence of each.  This caused no problem in showing data via a portal (so long as version 2 of each table was the link).  However, when I try to go to the related record, I have nowhere to go since each of my layouts are based on the main table and not version 2 of it.  I could create a duplicate layout related to version 2 of the table; however, then I would have to maintain 2 layouts for each table.  Is there an easier way of doing this?  Thanks.
wrc-Relationships.JPG
LVL 2
rvfowler2Asked:
Who is Participating?
 
ThomDrozConnect With a Mentor Commented:
rvfowler2:
The only option I can think of is to capture the record number in the portal and place it in a global field and then navigate to Layout#1 (of whatever table I am going to), Enter Find Mode, and then enter the number in Layout#'s RecNo field.  Anyone have a simpler option?

rvfowler2
I modified and 'simplify' your posted idea that is also faster,  that is using a variable rather than a global

There is nothing wrong with using this as a procedure to go to a specific non-related layout and display a record.

If you insist on trying to force a layout by creating additional complicated relationships that is another option.

The more common version of Table occurrence is using the anchor buoy approach.


Thom Droz
0
 
ThomDrozCommented:
rvfowler2

One approach is to change the layout properties so it is related to the table you want it to be based on
You may need to then make sure each field on the layout is from the correct table as well

hope that helps
thom droz
droz-consulting.com
0
 
rvfowler2Author Commented:
Yes, but as I mentioned above, I already have several portals that work fine referring to Layout#1/Table#1.  It's just that if you look at the far right of the diagram, you'll see that I have WRC_Contacts and WRC_Agents.  When I try to connect tables to them, FM forces me to label them as table #2 because they already have an existing relationship.  Thus, I can get the portal to work fine as I attached the portal to Table#2, but I cannot go to a Related Record because the layout is based on Table#1, which I want because I already have portals that send me to this layout and work just fine.

The only option I can think of is to capture the record number in the portal and place it in a global field and then navigate to Layout#1 (of whatever table I am going to), Enter Find Mode, and then enter the number in Layout#'s RecNo field.  Anyone have a simpler option?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
ThomDrozConnect With a Mentor Commented:
rvfowler2:

It can not go to the related record because the record is not related...

You want it to go to a record from an unrelated layout.

This can be done by setting a variable (not a global) then go to that layout and do a find for that field using the variable

OR
Create a relationship and a layout based on that relationship then go to related record.

hope that helps

Thom Droz
0
 
rvfowler2Author Commented:
Actually, you didn't tell me anything that I didn't already post.  I already knew I was going to a record from an unrelated layout, so my question was how do I get around this problem.  I did get one other solution:  I could tie in directly to the main tables because of the relationship via Notes.  I just tried it and it works.  I believe this is a Grandparent to parent to child relationship.  The only limiter is that for Contacts, every contact has to have at least one Note for the contact to show up in the portal, and similarly each Business Profile, Property, or Vacancy has to have at least one note posted on it for it to show in portals on the Agent's layout.
0
 
rvfowler2Author Commented:
Thanks, Thom, yes you did simplify my posted idea into the two options, and thanks for the recommendation of the anchor buoy approach.  That is what I was referring to by the Grandparent-Parent-Child relationship above.  Those are worth some points.  (I obviously need the Advanced Class in Relationships and am trying to talk my boss into it.)

So, any recommendations out there?  
Should I a) go with the anchor buoy approach, but the limitation is that I must have a Note for each property and each contact for them to show up in a portal, or b) just create a second set of virtual tables and then navigate back to the related record via a global field or variable?
0
 
ThomDrozCommented:
rvfowler2:

As I mention before use a variable not a global....

As to the 'correct layout' for you Your Table Occurrences should be based on what reporting you may want.  In my opinion there is no right answer except what is best for you, based on how you want your database to work....   It is not a flawed or incorrect design that says
Set $Variable to KeyField
Go to Layout1
Enter Find Mode
Set KeyField to $variable
Perform Find

Hope that helps!

Thom Droz
0
 
rvfowler2Author Commented:
Thanks.  I finally chose not to use the anchor approach as that means contacts will not show up in certain portals unless they have a Notes entry, etc.  Sorry, I forgot to mention one important point:  I have to use a global field instead of a variable in one case because the table I'm going to is in a completely different file (old design from FM 6).  Thanks for all your help.
0
 
ThomDrozCommented:
rvfowler2:

Good luck with your project!
It may also be possible to use a $$variable that might holds its value even when you go to the old database, honesty not 100% sure that will work but it is worth a try!

thom droz
0
 
rvfowler2Author Commented:
Yes, I did try it, but $$ doesn't hold across files, thought it does across scripts and tables within the same db.  The only way to cross dbs is with a global field.  And, one tip:  Just create one global GoToField in one db, and use that for all scripts where you want to Goto a related record.  The db doesn't even have to be the one you are coming from or going to!  This will save duplication of fields.
0
All Courses

From novice to tech pro — start learning today.