1 2 3 Previous Next 33 Replies Latest reply on May 8, 2017 4:29 PM by howardh

    Looking to create an editable job-specific stakeholder list.

    shawnmberkeley

      For each record in JOB:: there is a single client and a number of assigned staff members.  Contact information for both clients and staff is available in CONTACT::, and there is a value list that pulls current client names as well as one that pulls current staff names.

       

      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.  Then I would need the ability to add other clients and staff as stakeholders.  I have created a portal to add these extra stakeholders through a join table, STAKEHOLDERS::, between JOB:: and CONTACT::, but am unsure how to ultimately add them to a stakeholder list.

      I will display the stakeholder list on a layout and use it to specify contacts for job-specific communications. 

        • 1. Re: Looking to create an editable job-specific stakeholder list.
          philmodjunk

          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.

          • 2. Re: Looking to create an editable job-specific stakeholder list.
            shawnmberkeley

            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"?

             

            Thanks, again.

            • 3. Re: Looking to create an editable job-specific stakeholder list.
              keywords

              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.

              • 4. Re: Looking to create an editable job-specific stakeholder list.
                philmodjunk

                "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.

                • 5. Re: Looking to create an editable job-specific stakeholder list.
                  shawnmberkeley

                  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

                   

                  and

                   

                  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

                   

                  and

                   

                  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).

                  • 6. Re: Looking to create an editable job-specific stakeholder list.
                    philmodjunk

                    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----<Stakeholders>-----Contacts

                     

                    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.

                    • 7. Re: Looking to create an editable job-specific stakeholder list.
                      shawnmberkeley

                      Apologies that this has been such a lesson.  I am much closer now.  I set up the relationships as you mentioned, I believe – see attached.  Screen Shot 2017-04-09 at 12.37.17 AM.pngScreen Shot 2017-04-09 at 12.37.00 AM.png

                      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.

                      • 8. Re: Looking to create an editable job-specific stakeholder list.
                        philmodjunk

                        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.

                        • 9. Re: Looking to create an editable job-specific stakeholder list.
                          shawnmberkeley

                          "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?

                          Screen Shot 2017-04-10 at 12.44.25 PM.png

                          As it is now, a Stakeholders-based layout or portal will only display JOB:: or STAKEHOLDER:: field data.

                          • 10. Re: Looking to create an editable job-specific stakeholder list.
                            philmodjunk

                            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.

                            • 11. Re: Looking to create an editable job-specific stakeholder list.
                              shawnmberkeley

                              "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?

                              • 12. Re: Looking to create an editable job-specific stakeholder list.
                                philmodjunk

                                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.

                                 

                                That's it.

                                • 13. Re: Looking to create an editable job-specific stakeholder list.
                                  keywords

                                  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.

                                  • 14. Re: Looking to create an editable job-specific stakeholder list.
                                    shawnmberkeley

                                    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.

                                    1 2 3 Previous Next