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
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
ASKER
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
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
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
ASKER
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.
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
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
ASKER
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.
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