Posted on 09/01/2024 13:38:26
Hi Stephen,
There are three interesting topics in this thread. Let's start with the simples one first :-)
1) You can actually make this work by using the customer number approach as suggested by Nicolai. The Extranet app has a nifty feature in that it listens to a lot of the properties on the user, including custom fiels, when users are created in the frontend. The properties must be prefixed with UserManagement_Form, So you can add a hidden field in the Create Account form with the customer number of the current user, i.e., the primary user:
<input type="hidden" name="UserManagement_Form_CustomerNumber" value="@GetString("UserManagement:User.Address")" />
And then the new user will have the same customer number as the primary user who created the user.
2) For your tablescript, I would consider, do I have a lot of customer groups or just a few. If you have just a few, I would use the API to look up the group:
var group = new Dynamicweb.Security.UserManagement.Group(accessUserName);
if (group != null)
{
var col = mapping.SourceTable.Columns.FirstOrDefault(c => c != null && c.Name.ToLower() == "DISCOUNTACCESSUSERGROUPID".ToLower());
if (col != null && row.ContainsKey(col.Name))
{
row[col.Name] = group.ID;
}
}
Then, the API handles the cache, and this could reduce the number of database calls and speed up performance.
if you have a lot of groups, i would cache all the groups in a dictionary in the httpcontext.items collection, when the first row is processed, and the use that cache for the subsequent records. I've attached and example of, how this could be done with products, but it can easily be transformed to user groups.
3) Then you make an interesting statement about cloud based solution, and I aknowledge your point. You don't have on-prem tool-kit at hand, so it can feel like losing control. But in the case you are discribing, we do have very useful tool, the SQL Firehose, which I would use in the folloing way to solve your case. In fact, I would favor this over table script any day, because it keeps the platform standard, and it is so much easier to debug data in a database rather than debuggin a table script from your local machine.
1: Create a staging table for the raw data from ERP. We will load the data into this e.g. from an OData endpoint before loading in into the EcomDiscounts table. It could be something like this, and, yes, you can create this through the SQL Firehose too:
create table Staging_Discounts(
ItemNumber nvarchar(25) not null,
CustomerNumber nvarchar(25) not null,
Discount float not null
)
Very simplified, of cause. Just add your own fields and keys.
2: The I'd add an SQL view that joins the staing tabel with AccessUser to get group id for the customer gorup. This, you can also do through the firehose:
create view Staging_DiscountGroup as SELECT ItemNumber, Discount, AccessUserId from Staging_Discounts inner join accessuser on AccessUserUserName = CustomerNumber WHERE AccessUserType = '2'
3: Finally, I'd set up a Data Integration activity with the Dynamciweb source provider pointing to the Staging_DiscountGroup view, and with Dynamicweb destination provider pointing the EcomDiscounts table.
Then, you have a code-free solution to the challenge, which you can easily apply to both cloud and on-prem solutions. I added an article about this approach some time ago, which you might find interesting to have a look at:
https://doc.dynamicweb.com/community/devblog/blog/alternatives-to-applying-code-in-you-integration-flow?PID=8967
Br.
Lars
