Non related record portal

[Villmatt]

Hey there,

I'm self taught and hoping someone can help me with this seemingly complicated relationship. I'm essentially trying to build a layout that lets you link a "Tradesman" to a "Job"

I have a tables, "Job", "Tradesman" and "Job_Tradesman"

"Job_Tradesman" being the table that links the two, since 1 single tradesman can be working on multiple jobs and a single job can have multiple tradesman.

I have managed to build a portal that will show the tradesman linked to a job, however I now need to display the tradesman NOT linked to that job in a different portal. My idea being that I will make buttons that the user will click the "Tradesman" within the portal and that will link that said "Tradesman" to the job in table: "Job_Tradesman". So that tradesman will disappear from the portal of non linked tradesman and appear in the other portal of linked tradesman.

My layout is currently using the "Job" table, and my linked tradesman portal is using the "Tradesman" table, linked to the "Job" table using the "Job_Tradesman" table.

Job::pk_JobID = Job_Tradesman::fk_JobID

Tradesman::pk_TradesmanID =  Job_Tradesman::fk_TradesmanID

My original thoughts were that I could just make another instance of "Tradesman" and have the relationship being. Tradesman::pk_TradesmanID ≠  Job_Tradesman::fk_TradesmanID

However the portal shows nothing upon making that change.

 

Not sure what other information I should be including. I've been working on this for a few days now, all scripts to link and unlink work perfectly, I just can't seem to figure this one out.

Edited November 26, 2017 by Villmatt
title change

[Steve Martino]

I was working on something similar.  This may help:

https://community.filemaker.com/thread/177538

There is a sample file from PhilModJunk that may be what you need.

And here's a 3-part blog that may also offer some ideas:

https://filemakerhacks.com/2017/10/20/reciprocal-linkage/

Edited November 26, 2017 by Steve Martino

[Lee Smith]

The one at FileMaker Hacks was one of them I was thinking of.

There are a couple of more. I'm still looking.

This one by BruceR might help.https://fmforums.com/topic/102010-display-active-portal-row/?tab=comments#comment-464458

 

[Villmatt]

Wow guys. 

Extremely helpful. I’m yet to dive into these to construct my solution but have started reading them over. I’m sort of happy that it wasn’t something super easy. I was worried about experts giving me an eye roll. (This newbie has no idea, can’t even create a simple relationship). 

Though I may be happy about it not being an obvious solution. Feeling overwhelmed by the example given by Steve Martino. Wow this looks complicated. 

Im up for the challenge though. I’m very appreciative. I’ll be working on it over the next week or so. I’ll come back to share my progress and solution. 

Thank you again. 

[Lee Smith]

Hi Villmatt,

30 minutes ago, Villmatt said:

This newbie has no idea, can’t even create a simple relationship

We don't do that here. We cater to all skill levels. We do ask that the member has familiarize themselves with FileMaker (open it, played with the templates, create fields, layouts, etc.), and post using the Anatomy of a Good Topic as a guideline.

Lee

[Villmatt]

Hey everyone, 

Would love to share my solution, I'll try and word it in a way that someone else not as familiar with relationships and Portals will be able to understand. 

Just a recap, I have a tables, "Job", "Tradesman" and "Job_Tradesman"

The issue I was having with being able to see the Tradesman not linked to the specific job, was that my layout was built upon the table "Job". This means that unless there is an entry in table "Job_Tradesman" (e.g.: Job_Tradesman ID, Job:Melbourne, Tradesman:Bob ) then the tradesman will not show up regardless of any filters used within the portal. The tradesman is not related to the job until it has an entry in table "Job_Tradesman". 

So perhaps this isn't best practice in the database community. If someone wants to explain to me why this method may not be effective, please do. I'd love to learn.

In table "Job", I made a field I called "Tradesman Relationship", I made this an auto filled field upon creation to the number 1.

In table "Tradesman", I made a field that I called "Job Relationship", I made this an auto filled field upon creation to the number 1.

I made an instance of the table "Tradesman" and made an "=" relationship between the two new fields I created ( "Tradesman Relationship", "Job Relationship" )

Now I have a means of seeing all the tradesman in a portal regardless of them being linked to the specific job. I then applied a Portal Filter to only include those Tradesman that are not linked via the "Job_Tradesman" table.

IsEmpty ( FilterValues ( List ( Job_Tradesman::_fkJobID ) ; Job::__pkJobID ))

I don't know how to explain how that last filter rule works. But it worked for me.

 

My explanation is probably very bad. However I also wanted to thank you guys for your support. The links helped and really pushed me in the right direction.