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.

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.

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

Good morning Michael,

I have tested, and canot get the value from Shell.Item().GetFieldValue("Supplier Code"). There are 2 tables: "SPQ Training" (primary table) and "SPQ Supplier Training"(auxiliary table). In the Modscript, the supplier information, for example: "Supplier Code" field value cann't be pulled from the Modscript.

1. In primary table, I created relational fields, the "Existing Supplier" is Single Relational field point to the "SPQ Supplier Training"(auxiliary table). other Sub-Relational files point to all the detailed supplier information that stored in  "SPQ Supplier Training"(auxiliary table).

2. "SPQ Supplier Training"(auxiliary table):

3. "Existing Supplier" and "Supplier Code" field of "SPQ Training" (primary table).

4. In the Modscript, the supplier information, for example: "Supplier Code" field value cann't be pulled from the Modscript.

5. Form design:

6. Result

Thank you

Good morning All,

The Modscript works correctly, but I still have issues with passing usrId parameter. I connected to the backend database and added the users with SBM account to the Supplier Role group.

Thank you for all your answers. It's really helpful for getting understand SMB Modscript. i appreciate it.

In the #2 screenshot above, I don't see a "Users" field. A user type field points at the Users table, and this field would store the userid that you would pass into the modscript to return a result.

If you don't have a direct link (i.e.) between the user table and the SPQ Supplier Training, you would need to add a piece to the modscript to find the user id as I mentioned above. The code that I mentioned used email, but it looks like you do not have that in there so you would need to modify the where clause to searchby last name. If you have multiple users with same last name, then you can modify the sql to include the last name in the where clause.

var lastName= Shell.Item().GetFieldValue("Supplier LastName");

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

if(user.ReadWithWhere("TS_NAME like '%${lastName}'")){

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

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

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

}

I have already created Supplier User Field in the primary table "SPQ training", The primary table already has: 1. "Supplier"--User Filed, 2. "Existing Supplier" Single Relational Field linked to "SPQ Supplier Training" table, and 3. "Supplier Code" Sub-Relational... "Supplier Code" is the "TS_LOGINID" of "TS_USER" table.

If I understand correctly, I need to create a User field in "SPQ Supplier Training" auxiliary table, which will link between the user table and the SPQ Supplier Training table.

These is the fields I created in "SPQ Supplier Training" auxiliary table as you mentioned.

1. 

2. (All the fields in the form are from the primary table, auxiliary table field cannot be put in the form)

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 u1 = Shell.Item().GetFieldValue("Existing Supplier");
//addToGroup(u1);

var SupplierCode = Shell.Item().GetFieldValue("Employ ID");
Shell.Item().SetFieldValue("Test", SupplierCode);
var user = Ext.CreateAppRecord(Ext.TableId("TS_USERS"));
Shell.Item().SetFieldValue("Test1", user.GetId());

if(user.ReadWithWhere("TS_LOGINID = '${SupplierCode}'")) {
var r = addToGroup(user.GetId());
}

Result:

The Modscript you provided has no issue, but the data in the auxiliary table can't be passed to the script I guess.

Thanks a lot.

You are almost there.

Lets back up as there might be some confusion on SBM users and user fields:

1. Users in SBM are stored in the user table, and each user has a unique user id. If you have a user field in a primary or auxiliary table, and select the user, the underlying value will be the user id.

2 The addToGroup modscript would take that user id and add it as a member of a group. Assuming that you had a user field with the user selected, then the script takes that user id from the field and passes it to the modscript.

3. In the original example that you sent, the actual user record was not related to the Training Supplier auxiliary item directly since there was not a user field relating the two. You could not use the modscript since you didn't have the user id value available in a field.

4. When adding a user field to a table, you select the associated roles (as shown in your screenshot). Users have to be assigned to these roles, either directly or through a group in order to appear in the field selection.  In this case, if your user field is associated to the "Supplier" role and that role is only granted to the "SPQ_Training_Supplier" group, then the user would already have to be a member of that group in order to be selectable.

Back to your issue, one way that you could get this to work is to:

1. Allow all users to be selectable for that field, and select the correct user for each item. This will give the appropriate userid.

2. Add a subrelational  field in the primary application to point directly to that user field.

3. Use that subrelational value in the modscript.

Depending on the number of records that you have in the auxiliary table, this might be a viable option.

God it.

Thank you, Michael, and have a nice weekend!

Thank you, Micheal, and have a nice day!

You too!