Solved

Filemaker - Stuck in a Relationship!

Posted on 2010-08-28
10
870 Views
Last Modified: 2012-06-21
 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
0
Comment
Question by:rvfowler2
  • 5
  • 5
10 Comments
 
LVL 6

Expert Comment

by:ThomDroz
ID: 33552872
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
 
LVL 2

Author Comment

by:rvfowler2
ID: 33555359
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
 
LVL 6

Assisted Solution

by:ThomDroz
ThomDroz earned 500 total points
ID: 33555466
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
 
LVL 2

Author Comment

by:rvfowler2
ID: 33555772
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
 
LVL 6

Accepted Solution

by:
ThomDroz earned 500 total points
ID: 33558499
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 2

Author Comment

by:rvfowler2
ID: 33559077
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
 
LVL 6

Expert Comment

by:ThomDroz
ID: 33559224
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
 
LVL 2

Author Comment

by:rvfowler2
ID: 33560353
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
 
LVL 6

Expert Comment

by:ThomDroz
ID: 33560665
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
 
LVL 2

Author Comment

by:rvfowler2
ID: 33561036
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now