To clarify, in SBM, roles and groups are ways to assign privileges to users:

• a role is created in Composer and attached to the application.
• a group is defined in SBM Application Administrator.

You can add users to either level, but I would recommend adding users to a group, and then assigning a group to a role. Groups are easier to manage privileges and you can run system reports to see who is in the group.

To answer your question, if I understand correctly, the user accounts do not exist at all in SBM, but the user information is in the suppliers table. You want to add these individuals as actual users within SBM (note that this does require the appropriate additional user licenses for SBM).

For adding new users, I usually resort to using an orchestration as I trust that functionality better. Here are the steps that i would take:

1. I would create a group for your suppliers, associating the correct role with that group
2. Create one supplier to act as a template user associate with group created in 1 and add any other additional information.
3. Create an orchestration, which runs a report of all records in the suppliers table, and then create users for each record, using the template user created in step 2.
   

Now if you already have the users in SBM and you just want to add them to the group, you can do the same as above, just updating the user account.

If the users are already I added, I choose modscript to automatically add users. I use the following snippet often on transitions where a user is selected in a field, but may not yet be part of the correct permission group to own the item yet. The script searches for the group by name, and if that particular userId is not in the group, then it adds it.

def addToGroup(userId){
    var groupName = "Group Name";
    //check if member
    var group = Ext.CreateAppRecord (Ext.TableId("TS_GROUPS"));
    group.ReadWithWhere("TS_NAME='${groupName}'");
    var grpId = group.GetId();
        
    var member = Ext.CreateAppRecord (Ext.TableId("TS_MEMBERS"));
    var qs2 = "TS_USERID = ${userId} AND TS_GROUPID = ${grpId}";
    if(!member.ReadWithWhere(qs2)) {
        // add to member
        member.SetFieldValue("USERID",userId);
        member.SetFieldValue("GROUPID", grpId);
        member.SetFieldValue("ESTABLISHEDBY", 0);
        var ret = member.Add();
        if (ret == 0){
            return "Error adding to group ${groupName}.<br/>";
        } else {            
            return "Added to group ${groupName}.<br/>";
        }
    } else {
        return "";
    }
    
}

Hi Michael,

Thank you so much for your detailed response. I have created the Modscript you showed here on transitions where a user is selected in a field. I have tried the Modscript, but had validation issue. Error: "Illegal character" in 'Add Supplier'  at (2, 1). I used the Group name called "SPQ_Training_Supplier" that defined in Application Administrator. 

This is the Group name defined in Application Administrator:

This is the Modscript where I called in the transition:

Data that is copied from web pages and pasting into a script can have problems.  This is frequently because "DOUBLE QUOTE" characters are not ASCII character 34.

Edit the script in Composer and replace the characters that look like double-quote with actual double-quote characters.

I agree. Cutting and pasting script from other sources can introduce problematic characters. In addition to the double quotes, I have found problematic characters for blank spaces.

First, try the validate script button to see if Composer can find the error, which sounds like it is in line 2. As PM Thompson  recommended, edit the script in composer and see if the errors are removed.

Second, I've also had to paste code into Notepad++ to find the illegal characters. After pasting code into a new page, select to show all characters in Notepad++, and then you may see some invalid ASCII characters.

Hi Micheal, 

I have reloaded the Modscript you provided. and there are no invalidation issues, but the users in Supplier table aren't added to the Supplier group.

1.       2. 

For example, the user Lynda - Lynn showed in "graph 1" is the user in the Auxiliary Supplier table. I want to add this user into the Supplier group showed in "graph 2" after selecting the user from "graph 1".

I'm unsure about your process and what you are trying to do. I'll give you a few scenarios.

In general, you create users usually for people who need to access items within SBM. If the user name is informational only, you could use the "Contacts" and "Business" tables to store that information to be selected in a similar what to your explaining.

CASE 1 - Supplier User Already Exists in SBM

If I understand what you are showing above, your supplier table has a user field where you have selected "Lynda - Lynn."  When someone selects the Lynda - Lynn record above, then you would like to populate the "Supplier" field with the user selected in the record. The selection list under "Supplier" is limited to the "Supplier" role, which the group "SPQ_Training_Supplier" has selected.  Then, I would add a sub-relational field to the user field in the "Suppliers" table, and then using the modscript above, you would first get the field value from the item, run the modscript to add user to group if necessary, and then set the supplier value. For example:

var u = Shell.Item().GetFieldValue("User Relational FieldName");

var r = addToGroup(u);

//if you have an admin log field or something to store messages, you could write it out to see for errors;

Shell.Item().SetFieldValue("MODSCRIPT_LOG", r);

Shell.Item().SetFieldValue("SUPPLIER", u);

CASE 2 - Supplier User doesn't Exist in SBM

The other option that I can see is that there is no user in SBM, then the question is do you need to automatically add the user in order to allow access to the item?

If yes, it gets trickier. I was thinking of this scenario when I referenced the orchestration previously.  I personally don't like using modscript to add new users. An orchestration handles it better and you can pass in a template user as well as default groups. I would use this approach if you are constantly adding or changing your supply records constantly.

If the supplier list doesn't change, you could import export the user data from the suppliers table and import the users under App Admin (App Admin > Users > Import Users > Import from File). I would then add a user field in the suppliers table and reference that user or update the modscript to search for the user id with email address from the suppliers record:

var email = Shell.Item().GetFieldValue("User Relational Email");

var user = Ext.CreateAppRecord(Ext.TableId("TS_USERS"));

if(user.ReadWithWhere("TS_EMAIL = ?", [Pair(DBTypeConstants.VARCHAR, email)])){

var r = addToGroup(user.GetId());

Shell.Item().SetFieldValue("MODSCRIPT_LOG", r);

Shell.Item().SetFieldValue("SUPPLIER", user.GetId());

}

Good afternoon Michael,

 Thank you so much for your Answer. I appreciate it. I am still in learning curve of SMB application development and have lots of questions when developing the application.

I have run the Modsript you designed. And it is deployed without any issues, but I got the error in workcenter. (The paramater "userId" isn't transferred)

Error message:

The record with the 'select TS_MEMBERS.TS_ID, TS_MEMBERS.TS_USERID, TS_MEMBERS.TS_GROUPID, TS_MEMBERS.TS_ESTABLISHEDBY, TS_MEMBERS.TS_NAMESPACEID from TS_MEMBERS where TS_USERID = (None) AND TS_GROUPID = 994' select statement could not be read in the 'Members' database table.

ORA-00904: "NONE": invalid identifier

I have created Sub-Relational field in SPQ Training primary table:

I have run the Modsript:

def addToGroup(userId) {
var groupName = "SPQ_Training_Supplier";
//check if member
var group = Ext.CreateAppRecord (Ext.TableId("TS_GROUPS"));
group.ReadWithWhere("TS_NAME='${groupName}'");
var grpId = group.GetId();

var member = Ext.CreateAppRecord (Ext.TableId("TS_MEMBERS"));
var qs2 = "TS_USERID = ${userId} AND TS_GROUPID = ${grpId}";
if(!member.ReadWithWhere(qs2)) {
//add to member
member.SetFieldValue("USERID", userId);
member.SetFieldValue("GROUPID", grpId);
member.SetFieldValue("ESTABLISHEDBY", 0);
var ret = member.Add();
if(ret == 0){
return "Error adding to group ${groupName}.<br/>";
} else {
return "Added to group ${groupName}.<br/>";
}
} else {
return "";
}

}

var u = Shell.Item().GetFieldValue("Supplier Code");

var r = addToGroup(u);

Thank you again.

Thank you Thompson.

BTW, It is the CASE 1 - Supplier User Already Exists in SBM. All suppliers already have the SMB accounts.

In your Suppliers auxiliary table, do you have a "User" field type that points to the actual user record?

The user field would be the one that you would want the subrelational to reference. The call Shell.Item().GetFieldValue("Supplier Code") should be getting back a numeric value instead of "(None)".