I suggest you use two relationships to link companies to two different table occurrences of people. One link identifies the principal and the 2nd link identifies the advisors.
Companies::PrincipalID = Principal::PeopleID
Companies::Advisors = Advisors::PeopleID
Principal and Advisors are two table occurrences that have People as their data source table.
A calculation field, cAdvisorList defined in companies can convert a list of advisors into a horizontal list you can include into your form letter.
Let ( [ A = List ( Advisors::FullName ) ; count = ValueCount ( A ) ] ;
Case ( count = 1 ; A ;
count = 2 ; Substitute ( A ; ¶ ; " and " ) ;
Substitute ( LeftValues ( A ; count - 1 ) ; ¶ ; ", " ) & " and " & GetValue ( A ; count ) ) )
Now you can place merge fields for Principal::Fullname and cAdvisorList into a block of layout text on a layout based on Companies to create your form letter.
Thanks for the reply. I'm glad to see it should be fairly straight forward, and I can see all sort of applications for the framework once I have it sorted out. I think I have the relations set correctly. However in the calc formula the last line of leftvalues (A;c it thinks the c is a field that's not defined, what have I missed in my set up as I can't see any results to trouble shoot further? I've set the calculated result to text. Thanks.
Muy bad, the lower case c should be "count" as defined in the let function. I also misspelled substitute. I'll edit my earlier post to correct this.
Getting closer I think it's my advisor relationship. I have principal and people id's as numbers. What I'm not sure about is how do I allow for the one to many on the advisor side. What happens in my result is that in my test school I have one principal and 2 advisors and the cAdvisorList returns all 3 people ie it won't exclude the principal. Also as time goes on Advisors could become principals they also move around in organizations. Do I need something like a field checking for principal or not? It's only the odd incidence where a principal is an advisor and I would filter that with finds. What's the most elegant way to address this? Thanks. I posted a picture of the relationships I made in the original post.
I need to read my own posts more carefully.
The relationships should be:
Companies::PrincipalID = Principal::PeopleID
Companies::CompanyID = Advisors::CompanyID AND
Companies::PrincipalID ≠ Advisors::PeopleID
That extra pair of fields will filter out the Principal.
Promoting an advisor to Principal is as simple as updating the Companies::PrincipalID field to store the PeopleID number for the new principal.
Thank you very much Phil that works perfectly. I can already see other ways to apply the framework. For instance in a correspondence database, this is how you filter out what was email, fax, snail mail etc, or different types of tasks etc too. I can also just email all the advisors of a given organization using the same formulate combine the email addresses in a to field. So this is way way more efficient than how I was doing some of that and it will let me do so much more with contact info too. Is there a way to combine all the relevant pieces into one post for others down the line maybe with a better subject than can this be done?
Phil, so I've been playing with this tonight to see what else I might be able to leverage out of it. It's a sales application so like anything the more contacts you have an organization the better off you are. So I can add in Secretaries, VP's, just staffers people connect with etc, and they all show up fine in a list. I discovered in order to still get just advisors in my formula I just had to exclude them like principals, however the exclusions start to mount up etc.I also discovered the formula doesn't like blank id's so if principal id in organization was blank it wouldn't calculate anything and would ignore the advisors totally. I've also discovered that if the principal gets an contact ID that's greater then the advisor id's the formula breaks down and shows everyone in the names as well just like when the second And part of the relation in Advisors is missing. So my question around this is simply why, it should logical just process out principals when ever is sees them, order shouldn't matter.
So I went with simply a static field with the constant "Advisor" as text in company and then in a relation instead of excluding principals I include any people that role equals advisor text. That seems to solve the issues with principal id's greater than advisor id's, and all the different roles as I can start to build relations that include or exclude roles as needed, and can track all the contacts etc. I've been able to concatenate the email address out of the records changing the formula fields too. So the question here is it seems to work, I've struggled with this for a year and a bit so it seems to work really simply and so have I set myself up for some huge land mine down the road with using this constant like I am? Thanks.
There seems to be some confusion here. Whether the Principals's ID number is greater or less than the advisors' ID numbers makes absolutely no difference in how this works.
I've uploaded a Demo file to a share site: http://www.4shared.com/file/JlVw-RL6/PrincipalsAdvisors.html
Download it and compare its design to your own and see what's different. Its design works equally well with filemaker 10 or 11. Note how you can select any existing person from the principal popup menu and their name will disappear from the advisor portal and the person originally selected will disappear. You can select any of the three people listed or even add a new person in the All contacts portal and then select them. I've also added a second "principal" field to demonstrate a second method for displaying the name of a person assigned to a "company" record as principal.
If you have filemaker 11, you could also make the advisor's portal work by setting a filter expression on the advisors portal but that doesn't give you the relationships you need to extract principal and advisor names separately for your form letters.
Thanks for the demo file I found the error. It was supidity on my part as changed the principal id, I think I missed updating it in the main file so the exclude would work. Though that being said it was such a bizarre thing that made no sense to me I tried several versions and even built a second database the results carried through. It was though rather late last night, sorry about that. I love the principal pop up but there will be 700 companies and currently well over 2000 people in the list so it's a bit of a long scoll list but very useful on a second application I want to use the framework for In the database. The method for adding people is great too and I'll definitely incorporate that in both. I'm in 11 Advanced and one of the rabbit holes I did go down was trying to filter portals and what have you however hit that exact wall the form letters didn't work.
Any input on going forward, if I want to try and keep things like secretaries, other staffers etc in the contact database. Should I do the relationship as include and the text category like I tried just getting advisors as a text constant in company and relating that to role in people or is it better to simply stack up the excludes on the relationships?
Thanks for all your help I looked today your solution will kill 25 fields that were in some ways redundant in the organization database and make everything way way more flexible, and the ability to have way more useful info in the contact database so I really appreciate it. I can finally get back to my job of working with the organizations effectively vs the half automated half cobbled things I was doing. Someone asked to have 3 advisors and the system sort of broke:)
You can use multiple exclusions or you can set up calculation fields that use a "contact type field" to be either empty or contain a copy of the PeopleID number to link to subsets of the entire group of contacts linked to one company.
If (ContactType = "Secretary" ; PeopleID ; "" )
With regards to linking principals from a pop-up, here's a scripted approach that is much more user friendly:
I think I'll ultimately need to work more on the calculation there, it only grabs one person and I could potentially have several secretaries or staff etc. I also didn't quite follow how it gets used and set up. For that matter I have 3 principals that are advisors too.
So I've uploaded what I did at http://www.4shared.com/file/Fr_t35Zy/Test.html I took the first formula you gave me and then just made a roles relation so that depending what radio button is selected it populates with the applicable names or subset of names. It's not the most elegant but it's working and I understand how it works too. I'm always only ever going to be emailing one group of roles at a time so it should work down the line for me.
The pop up script works beautifully. I had seen something like that on the web for a different part of my database and tried it and had no luck with it. This post does exactly what I wanted there and so I've implemented that in the area I wanted it in. I marked your last post as the best choice as I think that's the only way to close out the thread and have it drop off your radar as well. Thank you so much for your help. It's made my life much easier on so many fronts outside of what the original question as I didn't get just a fish I learned a bit more about fishing:)