Once you have created the join table records, you need only pull up the join table records for a given project to list all contacts for that project. Go To Related Records from a layout based on Projects could be used to pull up such a found set. A layout based on the join table can include fields from both Projects and contacts to fill in the needed details for such a list.
Note that the same layout can be used to list all contacts for a given project or all projects for a given contact.
Thanks phil, but I am unable to wrap my head around this so far.
I have a layout (STAKEHOLDER_ADD) showing records from JOB:: on which I have two portals that allow addition of staff (STAKEHOLDERS::StaffFullName) and client (STAKEHOLDERS::ClientFullName) stakeholders. These work.
I have another layout (STAKEHOLDER_LIST) which I am trying to populate with all of the entries to the two portals on STAKEHOLDER_ADD. I also need to populate the list with the assigned staff for each record from up to six areas (JOB::FullName_AreaX).
I cannot figure out how to populate STAKEHOLDER_LIST even with just the portal entries. I placed a button in both of the portals with the following action:
Go to Related Record [ Show only related records ; From table: "STAKEHOLDERS" ; Using layout: "STAKEHOLDER_LIST" (STAKEHOLDERS) ]. "Match Current Record" is checked.
How can I define a field to place on the STAKEHOLDER_LIST layout that will capture both portal entries, not to mention the assigned staff? Would you also please expand on "Go To Related Records from a layout based on Projects could be used to pull up such a found set"?
I assume your join table at its most fundamental will have two fields—jobID and contactID. However, since you will use this table for two different types of join you will also need a joinTYPE field, into which you will enter, say, Client or Staff to define the type of join; this field could match to global fields in the Job table, containing either Client or Staff. The relationship for each join will also include the joinTYPE field in the match setup, so that you can distinguish between the two types.
With that properly set up, you can define a third relationship that disregards the TYPE match, and this will show all stakeholders regardless of type.
"I would like to create a list of all stakeholders for each JOB::JobNumber. It would be populated with the client and all assigned staff members."
I am not sure that you have your relationships and join table set up correctly. If it were, GTRR pull up a found set of all staff and all clients assigned to a given project. You do not need names in the join table as you should be able to refer to name fields from contacts.
So so please describe your relationships.
I thought the relationships might be the problem. I do not understand them very well, no pun intended.
I have the following:
1) STAKEHOLDERS::JobNumber (indexed text field/auto-enter calculation "$$StakeholderJobNumber")
JOB::JobNumber (indexed calculation field/JobLetter [Auto-enter Data] + GetAsText (JobID) where JobID is an indexed auto-enter serial number
2) STAKEHOLDERS::ContactID_Client (indexed number field/auto-enter calculation "$$StakeholderContactID_Client")
CONTACT_STAKEHOLDERS_CLIENT::ContactID_Client (indexed calculation field/Case ( PatternCount ( ContactType; "Client" ) ; ContactID ) where ContactID is an indexed auto-enter serial number
3) STAKEHOLDERS::ContactID_Staff (indexed number field/auto-enter calculation "$$StakeholderContactID_Staff")
CONTACT_STAKEHOLDERS_STAFF::ContactID_Staff (indexed calculation field/Case ( PatternCount ( ContactType; "Staff" ) ; ContactID ) where ContactID is an indexed auto-enter serial number
The CONTACT_STAKEHOLDERS_X tables are sub-tables (is that the correct term?) of the primary table CONTACT::.
In all three relationships I checked to allow creation and deletion of records in STAKEHOLDERS::.
Seems like I made it way too messy (and likely wrong).
Yes, you have made it far more complex than it needs to be. It's also dependent on always having the correct value in the global variables. It's also not clear how you can have two different portals to stakeholders on your Job layout given those relationships.
What you should have set up is:
Job::__pkJobID = StakeHolders::_fkJobID
Contacts::__pkContactID = Stakeholders::_fkContactID
the __pk fields (Pk means "primary key") would either be number fields that auto-enter a serial number or text fields that auto-enter get ( UUID ). the _fk (fk means "foreign key") fields would be simple number or text fields (match to the type of the _pk field to which they are linked) You would only set up auto-enter calculations on the _fk fields if you are not using portals and value lists from which to select a contact or job ID for a stakeholder record and in those cases, the global variable would just be set to the value of the relevant _pk field.
The only fields absolutely necessary in StakeHolders are the _fk field and a "type" field that identifies the stakeholder as either "staff" or "client". You don't need name fields in this table as you can access the Job's name from the Job table and a contact's name (both client and staff) from the contacts table.
So if you are on a record on the Job layout, the GTRR step that we have been discussing can specify a layout based on StakeHolders and will pull up a found set of StakeHolder records all linked to the current Job record on that layout. You can place fields from both Job and Contacts on this layout to provide additional information such as names to make a complete report type list of the stakeholders.
But I think I have my Add Stakeholders portal wrong. The Add Stakeholders layout is based on records from JOB::. The portal to add the records shows related records from STAKEHOLDERS::. To get this to work, I had to add a STAKEHOLDERS::FullName field, which I specified as a drop-down list, assigning the value list Contacts_AllActive. This value list uses a CONTACTS:: field that calculates all active contacts, both client and staff. When I add the GTRR button to either a JOB:: based layout or the Add Stakeholders portal, it works, navigating me to the STAKEHOLDER:: based Stakeholder List layout displaying the names from the portal. It also correctly displays the status of the STAKEHOLDER::OptOut check box. But I cannot figure out how to get it to display information from related tables, like CONTACTS::. I also do not understand how to set up the STAKEHOLDERS::ContactType field, which I cannot get to display data in the Stakeholder List. Then I have to be able to add the assigned staff – JOB::FullName_AreaX.
You can't just define a type field and expect it to add data. Once you have added that field, you still have to enter or select a type in each record of the stakeholder table. A script or Replace Field Contents can automate that update.
To to add a field from contacts to this new layout, open the field picker and select contacts from the drop down at the top of this dialog. The name you select here must exactly match the name of the contacts occurrence "box" linked to your Stakeholder table. You can then drag any field now shown in the field picker to your layout. If data and relationships are correct, that's all you need to do.
"You can't just define a type field and expect it to add data."
I do understand that, but for each unique ContactID there exists a ContactType. I am thinking that if I have my relationships correct, it should be able to be auto-entered.
Regarding relationships, I must have something wrong. Earlier, I posted the dialog boxes for the two relationships I created. Here is a screen shot of the relationship graph. Are these relationships correct for what I am trying to accomplish?
As it is now, a Stakeholders-based layout or portal will only display JOB:: or STAKEHOLDER:: field data.
You should also be able to add fields from CONTACT STAKEHOLDERS.
Whether you can auto-enter a type would depend on the fields in Contacts and if the data is in contacts, you don't need another field for it in Stakeholders.
"You should also be able to add fields from CONTACT STAKEHOLDERS"
Can you tell from what I have posted why I am unable to? if not, what additional information would you need?
I see no reason why you cannot. And is it that you can't add the field, or that you don't see the data that you expect when you try?
This is really quite simple so it's hard to see where this breaks down for you
Enter layout mode.
Use the field tool to add a field object to the layout or the portal row.
Select CONTACT STAKEHOLDERS from the drop down at the top of the specify fields drop down.
Click to select the field that you want.
Shawn, the attached demo files shows the technique I described in my post a few days ago. Given the discussion since it might help you get your head around the whole setup.
twopartkeyDEMO.fmp12.zip 71.6 K
I have been able to place the CONTACT STAKEHOLDERS fields, but have not been able to get the data to appear. I tested fields from all three tables in layouts and portals based upon each of the three tables and could only get CONTACT STAKEHOLDERS fields to show in CONTACT STAKEHOLDERS layouts and portals.