Using CRMScript/NetServer to mass-update number in userdefined field?

PARTNER Frode Lillerud

Hi guys,

I need to figure out how I can do a big mass-update of the value of a userdefined number value on person.

Using plain SQL I would have done something like this:

update crm7.udpersonsmall set long05 = long05 + 1

But since this solution need to work in Online, where we can't run SQL queries, I need to figure out how to do it using CRMScript.

As a start, I'm trying to do an update using a reverse join, like this, but it's not working.

SearchEngine se2;
se2.addData("udpersonSmall.long06", "432");
se2.addCriteria("udpersonSmall.udpersonSmall_id.(person->userdef_id).person_id","equals", "3081");
se2.update();

I also briefly looked at the NSImportAgent, but not sure if that is the way to go either.

Looping over every person and using NSPersonEntity to make the change would take too long, since I need to be able to update tens of thousands of persons.

Any ideas?

STAFF /sv/My-Page/4998/

RE: Using CRMScript/NetServer to mass-update number in userdefined field?

Hi Frode,

Why not try Bulk Update?
I suspect you have your reasons. If so I'd like to know them.
I don't have an online test environent, but the WIn team does have a Web running for our daily development since the Admin Win client is virtually no more..

So I tried a selection of 40080 persons using the Web client.
Changed 3 udef fields, 1 text, 1 numeric and 1 list.
Ok, so I got time to make some cofee but still...

ca 13 mins with elapsed row times between 12 and 25 ms.
Using Win, I have seen average row times under 7 ms.
Mileage will vary and be hugely dependent on what I call the "total database access path".
I regularly do a 1000 person bulk update job in Win as way to verify that everything is ok with the db.
I have seen elapsed times go over 350 ms, then Jens or Helene do some magic with the db server and whoops, back down to under 10 ms!

/conrad

Av: Conrad Weyns 28. apr 2018
PARTNER /sv/My-Page/5028/

RE: Using CRMScript/NetServer to mass-update number in userdefined field?

Hi Conrad,

BulkUpdateAgent could perhaps be used, but it's not working in CRMScript, unfortunately. Screenshot from latest SOD version:

Error message says: "EjScript::RunTimeException: An exception occurred in script: "BulkUpdate" at: line 3, char 1 Original exception: EjScript::ParserException: EjScript::Value::Value: cannot create variable of this type: NSBulkUpdateAgent".

The classname passes the syntax check, but doesn't seem to be implemented in ejScript code. We experienced the same problem when the NSQuoteAgent was added a while back as well.

In addition, I'm uncertain if the bulk update agent would allow me to say "increase the value in the userdefined field numberfield by 1 for each person", or if it will only allow me to set the same value on all target persons.

Av: Frode Lillerud 28. apr 2018
STAFF /sv/My-Page/4998/

RE: Using CRMScript/NetServer to mass-update number in userdefined field?

I was actually thinking about the bulk update GUI.
But this sort of rules that out:

In addition, I'm uncertain if the bulk update agent would allow me to say "increase the value in the userdefined field numberfield by 1 for each person", or if it will only allow me to set the same value on all target persons

In this version of bulk update, this is not possible.
With grep for search & replace in all text fields you could do a thing or two but numeric fields can only be set.

Thanks for the answer!
/conrad

Av: Conrad Weyns 28. apr 2018
PARTNER /sv/My-Page/5028/

RE: Using CRMScript/NetServer to mass-update number in userdefined field?

Yea, using the client GUI won't solve my issue, since this is going to be part of a product I'm working on which will be installed at many customers, and the update needs to be done to all persons every night. I need an automated solution.

Av: Frode Lillerud 29. apr 2018
STAFF /sv/My-Page/9874/

RE: Using CRMScript/NetServer to mass-update number in userdefined field?

I guess using a SearchEngine to loop through person.userdef_id.long06 and then another SearchEngine to update the data is going to time out?

Av: Simen Mostuen Iversen 29. apr 2018
STAFF /sv/My-Page/5914/

RE: Using CRMScript/NetServer to mass-update number in userdefined field?

Hi,

We have looked through the code, but unfortunately we are not able to see how you could run such a query.

Is there perhaps another way to solve your problem? What are you trying to do? Could you for instance use a DateTime field and set it to "now" instead?

Sverre

Av: Sverre Hjelm 30. apr 2018
PARTNER /sv/My-Page/5028/

RE: Using CRMScript/NetServer to mass-update number in userdefined field?

The goal here is to have a userdefined field on Person called "Days since last activity". So a number value. For most persons this number should increase by 1 every midnight.

We opted for a numberfield, instead of saving the actual date of the last activity, since it would make it easier when setting up f.instance a Dynamic Selection in CRM to "give me all persons where last activity is more than 100 days ago".

I think we'll have to consider stamping the person with the datetime instead of number of days.

Av: Frode Lillerud 30. apr 2018