Jump to content
Salesforce and other SMB Solutions are coming soon. ×

FM Relationships


Recommended Posts

I'm working on a legal database. I have a master table and many others. I have a table called participants, which as indicated contains the ID numbers of each participant involved with the case, as well as their relationship in the case. Two of those particpants will always be the case manager (coded CMA) and the case responder (coded RSP).


I would like the case manager's and case responder's ID's to show in their respective two fields in the master table. I'm trying to do this using the following calculation, with very little success.


Any ideas? THANK YOU!


If ( case_number = tbl_leg_participants::dist_case_no and tbl_leg_participants::relationship = "CMA" ; tbl_leg_participants::participantID ; "" )

Link to comment
Share on other sites



assuming two things:

1- "CMA" and "RSP" are stored in the same field (relationship)

2- the field will always be empty for any other participant


you can show them in a two-row portal, sort descending; or a one-row portal, sort descending by this field "relationship". If you want to show the RSP, have it show row number 1, and for CMA, row number 2. You can set all this in the portal setup dialog.


If the field holds data for other participants also, you can create a second field,

"cPrimusInterPares=Case(relationship = "CMA" or relationship = "RSP"; relationship).

and then use this field for the sorting of the portal(s).






PS. why your calcs won't work:

the first part is always true (because it reflects the actual relationship you're using). And for the second part, filemaker looks at the first related record (that was created) only. So if this holds RSP or CMA, one of the two calcs will work, but if it doesn't, neither will.


PS2. the fieldname "relationship" is a little bit confusing since this term is so often used in talking about the database structure itself!

Link to comment
Share on other sites

That makes a lot of sense. I now have my related portal records sorted and portal row 1 is CMA and 2 is RSP.


Now how do I get my manager and responder fields on the root table to grab those values (the ID), now that I know which portal row the value is in? I'm not seeing it in the portal dialog box.


And thank you for getting back to me so quickly!


- todd

Link to comment
Share on other sites

Now how do I get my manager and responder fields on the root table to grab those values (the ID),


I'm not really sure what you want to do with that, but if it's a matter of display, then showing only one portal row as described above, and formatting that portal as transparent/no border, should give you what you want. If you need to see the name of the participant, you can do that by creating a relationship from the participants table to the employees table and putting the related field in the participants portal.



Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...

Important Information

Terms of Use