Question:
How and where is the project team stored?
Can I update the project team from external sources?
Can I update the project teams based on revised Build Team rules?
Answer:
If you are trying to update many teams, write a custom report or create custom SQL code. The information here may help.
The project team is stored as a mapping between a project and various contacts and roles (responsibilities):
- xsfUserProject—the backbone of the project team. Each row in this table is a row on the project team grid. This table relates a contact with a project and his/her responsibility on the project. There can be multiple rows per person if the contact has multiple responsibilities. The primary key on this table is UserProjectKey. The Alternate Key is UserKey, Project and Responsibility (Foreign Key reference to xsfUCRole).
- xsfUser—the contacts. This is the source of default responsibility and current contact data (phone number, email, etc). Key is UserKey. Good candidate keys are UserLogin, EMail, and EmployeeID. Vendor ID is stored here, but all contacts associated with a vendor will share this value.
- xsfUserRole—the user’s permission, particularly when project sensitive. This is a mapping between xsfUser and xsfUCRole with conditions (such as project)
- xsfUCRole—the role definition. We call a role a responsibility when the role has a “work” capability. The role name is displayed as the responsibility on the project team.
All of the data for a project team can be retrieved from the data store using the Project ID passed to dbo.p_GetProjectTeamList and it returns the team.
Updating the Team
There are several resources in the system that may prove useful if you intend to update/import project team members.
- EXEC dbo.pu_ProjectTeam—this small procedure is used by the system to add multiple contacts to the team based on the ‘build team‘ rules. It also provides a sample of how to add multiple contacts to a team using a side table with just 4 fields (Project, Userkey, ResponsibilityKey, TeamKey), handling both team membership and implied project role membership (security). After you have reviewed your build team rules, you can add missing team members (based on those rules) to a single project:
EXEC dbo.pu_ProjectTeam @pProjectLike=’GC003′, @pReportOnly=0
or for all projects:
EXEC dbo.pu_ProjectTeam @pProjectLike=’%’, @pReportOnly=0
- EXEC dbo.psf_ProjectTeamListState—this procedure is used by the Project Team grid UI when changes are made. It handles create and update with audit trail and implied project role membership (security). Hint: use @pTS=NULL and some user contact as @pByUser (for the audit trail). You can also update ancillary attributes such as
- Show—the team member shown in the default view
- Public—the team member public
- Active—the team member active, or soft deleted
- Contact Project
- EXEC dbo.psf_ProjectTeamRemove—this procedure is used by the Project Team grid UI when rows are deleted and removes a contact from the project team, handling audit and security.
Sometimes gathering certain data is so common that there is a view or function to help
- dbo.vsf_ProjectTeamResponsibility—this view returns one row per contact on the project team, either explicitly or via security only
Team Membership -vs- Security
From a colloquial perspective, being on a project team obviously implies corresponding permission. But in terms of system internals, the visible team membership is stored separately from security. It is possible to appear on a project team and have no security/permissions. Likewise, it is possible to have permissions to act on a project without appearing on the team.
Additional Comments:
This is an advanced topic and is not covered by standard implementation and support. Thoroughly test your work!
If your site is integrated with Microsoft Dynamics SL, then changes to the MANAGER1 (and optionally MANAGER2) in DSL will update the project team.
If you site is integrated to an ERP system via XTS, changes to the project team will be sent to the partner system the next time the ‘SendProject’ action is scheduled. For information on how to schedule XTS actions, contact your implementer.
KBA-01602; Last updated: September 19, 2017 at 8:08 am;
Keywords: schema