Creating a User Multi Select that updates multiple records

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

Creating a User Multi Select that updates multiple records

  • Comments 1
  • Likes

I've recently had two requests for information that I think I can handle with a single answer.

  • The first is "How can I add a user select field to a screen that will work in a similar way to the user select field in the New Appointment screen? Such that it shows the users in multi-lines, and allows for insertion of records to a custom entity similar to Comm_Link table".
  • The second question was "Is it possible to allow a user to maintain the members of the company team on the company summary screen?"

My solution to both questions depends first on a trick that I wrote about earlier in the article "Creating User Multi Selects".    You have to create the translation as described there and use the caption family when defining the a new field in the table.

A second trick this solution relies on is that data in multi select fields are comma delimited and can be read using the Values() and FormValues() collections.  This is discussed in the article "Handling the Data in Multi Select fields in Validate Rules and Table Level Scripts".  

I created a new field within the companyboxlong screen as you can see in the image below:

Once the users are selected they appear in a comma defined list.

And the list of company team members will have been updated.

By allowing the update of the company team to be linked to a field in in the company table, new authorisation options are opened up as the field could now be controlled by field level security.

The updating of the Company Team table, actually called 'Team' in the database has been carried out by an UpdateRecord event function in a table level script:

function UpdateRecord()
if (Values("comp_teammembers")!=CRM.GetContextInfo("company","comp_teammembers"))
//if a change to the team has happened delete and rebuild team;
//Delete the team
var intRecordId = CRM.GetContextInfo("company","comp_companyid");
var TeamRecord = CRM.FindRecord("team","team_foreigntableid=5 and team_foreignid="+intRecordId);
while (!TeamRecord.eof)
  TeamRecord.DeleteRecord = true;

//Get the new team members
//Build new team
var strTeamList = FormValues("comp_teammembers");
var arrTeamList = strTeamList.split(",");
for (x in arrTeamList)
//Create a Record
NewTeamRecord = CRM.CreateRecord("team");
NewTeamRecord.team_foreigntableid= 5;
NewTeamRecord.team_foreignid= intRecordId;
NewTeamRecord.team_userid= arrTeamList[x];

Note:  I have only considered the UpdateRecord() event function.  The code would be slightly different for the PostInsertRecord function as CRM.GetContextInfo() is not applicable.

Note: This is a proof of concept and you will need to check the performance and scalability of this suggestion in your test environment.