How to load user's information from a SBM table to a created the SBM Role automatically

Question

We have a SMB project, there is a table called Suppliers, which has hundreds of suppliers&rsquo; individual information. We also created a Supplier Role in this project. Instead of setting one person to Supplier Role group from the SBM workCenter manually, how can we load all individual information from the Suppliers table to Supplier Role automatically? 
 Thank you

Michael Fiala · Accepted Answer

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()); 
 }

Michael Fiala · Answer

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.

Cuiwei Zhang · Answer

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

Cuiwei Zhang · Answer

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}.
"; } else { return "Added to group ${groupName}.
"; } } else { return ""; } } var u = Shell.Item().GetFieldValue("Supplier Code"); var r = addToGroup(u); Thank you again.

Cuiwei Zhang · Answer

Thank you Thompson.

Cuiwei Zhang · Answer

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

Michael Fiala · Answer

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

Cuiwei Zhang · Answer

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

Cuiwei Zhang · Answer

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.

Michael Fiala · Answer

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()); 
 }

Cuiwei Zhang · Answer

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}.
"; } else { return "Added to group ${groupName}.
"; } } 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.

Michael Fiala · Answer

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.

Menu

Solutions Business Manager Discussions

Verified Answers

How to load user's information from a SBM table to a created the SBM Role automatically

Top Replies

Resources