I need to clear or reset all of the fields for a certain custom fields.
How is this possible? Im guessing its SQL but no idea on SQL?
Matt
I was going to say Batch Entity Editor but unfortunately it does not allow mass-edits of Custom Data Fields.
SQL is going to be very tricky because the Custom Data is stored as JSON. I would only attempt it if you are running SQL 2016, otherwise forget it.
How about if you just delete the custom field, then re-create it again?
EDIT: It doesnât work - when you re-create the custom field, the data is restored.
I created this script and confirm it works, just update the entityType and entityCustomField variables with those for your setup.
function clearCustomField() {
var entityType = 'Customers';
var entityCustomField = 'Test';
// Get entities
qry = "SELECT count([Name]) as [CT] FROM [Entities] WHERE [EntityTypeId]=(SELECT [Id] FROM [EntityTypes] WHERE [Name]='"+entityType+"')";
var entityCount = sql.Query(qry).First;
qry = "SELECT [Name] FROM [Entities] WHERE [EntityTypeId]=(SELECT [Id] FROM [EntityTypes] WHERE [Name]='"+entityType+"') ORDER BY [Name]";
var entities = sql.Query(qry).Delimit(',').All;
for (var n = 0; n < entityCount; n++) {
entityName = entities[n];
// Clear custom field
api.Entity(entityName).Data(entityCustomField).Update('');
}
return true;
}
6 Likes
Absolute legend.
Iâll get this up tomorrow.
Really appreciate it
Matt
1 Like
Mark beat me to it, script all the way 
1 Like
You are not calling the function correctly. In your âClear Fieldsâ rule, the âUSR ExecScriptâ action - handler.func should be âfieldClear.clearCustomFieldâ (that is, the handler name and the function name - you had used the handler name and the script display name).
Doh
Iâm nearly home will try again 
Completely forgot about the API for this. Good call @markjw!
To be complete, you should include the brackets for the function as well:
fieldClear.clearCustomField()
1 Like
Awesome, yes its now working as expected.
I need to do something else too but I rekon I can work it out from this script
Matt
1 Like
Just one last one, Ive played with it a little now to create another script.
I want it to move that field into another field before it clears it.
so, itll run the script on the 1st of the month and itll move âVisits Countâ to âVisits Last Monthâ
I managed to do it pretty easily, however I cant get it right at the endâŠ
function moveCustomField() {
var entityType = 'Customers';
var entityCustomField = 'Visits Last Month';
var monthlyVisits = 'Visit Count';
// Get entities
qry = "SELECT count([Name]) as [CT] FROM [Entities] WHERE [EntityTypeId]=(SELECT [Id] FROM [EntityTypes] WHERE [Name]='"+entityType+"')";
var entityCount = sql.Query(qry).First;
qry = "SELECT [Name] FROM [Entities] WHERE [EntityTypeId]=(SELECT [Id] FROM [EntityTypes] WHERE [Name]='"+entityType+"') ORDER BY [Name]";
var entities = sql.Query(qry).Delimit(',').All;
for (var n = 0; n < entityCount; n++) {
entityName = entities[n];
// Change custom field
api.Entity(entityName).Data(entityCustomField).Update(.Data(montlyVisits));
}
return true;
}
What do I put in to Update() for it to grab the var monthlyVisits?
Cleanest would be to get value into variable, update, then clear;
function moveCustomField() {
var entityType = âCustomersâ;
var lastMonthVisits= âVisits Last Monthâ;
var monthlyVisits = âVisit Countâ;
// Get entities
qry = "SELECT count([Name]) as [CT] FROM [Entities] WHERE [EntityTypeId]=(SELECT [Id] FROM [EntityTypes] WHERE [Name]='"+entityType+"')";
var entityCount = sql.Query(qry).First;
qry = "SELECT [Name] FROM [Entities] WHERE [EntityTypeId]=(SELECT [Id] FROM [EntityTypes] WHERE [Name]='"+entityType+"') ORDER BY [Name]";
var entities = sql.Query(qry).Delimit(',').All;
for (var n = 0; n < entityCount; n++) {
entityName = entities[n];
//Get current count
var lastMonthsCount = api.Entity(entityName).Data(monthlyVisits).Get();
// Update to last months count
api.Entity(entityName).Data(lastMonthVisits).Update(lastMonthsCount);
// Clear current
api.Entity(entityName).Data(monthlyVisits).Update('');
}
return true;
}
Not tested but allong those lines
2 Likes
Diamond. Letâs have a go!
Didnât work, but no bad.
Im probably missing something so ill just do it again in the morning.
Matt
function moveCustomField() {
var entityType = 'Customers';
var entityCustomField = 'Visits Last Month';
var monthlyVisits = 'Visit Count';
// Get entities
qry = "SELECT count([Name]) as [CT] FROM [Entities] WHERE [EntityTypeId]=(SELECT [Id] FROM [EntityTypes] WHERE [Name]='"+entityType+"')";
var entityCount = sql.Query(qry).First;
qry = "SELECT [Name] FROM [Entities] WHERE [EntityTypeId]=(SELECT [Id] FROM [EntityTypes] WHERE [Name]='"+entityType+"') ORDER BY [Name]";
var entities = sql.Query(qry).Delimit(',').All;
for (var n = 0; n < entityCount; n++) {
entityName = entities[n];
//Get current count
var lastMonthsCount = api.Entity(entityName).Data(monthlyVisits).Get();
// Update to last months count
api.Entity(entityName).Data(lastMonthVisits).Update(lastMonthsCount);
// Clear current
api.Entity(entityName).Data(monthlyVisits).Update('');
}
return true;
}
This is what I have⊠but it doesnât clear, or move?
This would be only this month and last and not relative to specific date periodsâŠ
I would invest a bit of time and look at reporting ticket count by unique day dates which would make this irelevent and give more powerful dynamic reporting abilities ie forghtnight, quarter, year etc
If refining to unique day date visits becomes to complex you could posibly add a ticket state to the first ticket of a date similar to your last visit. A âfirst ticket of a visit stateâ and maybe count customer tickets with that state?
1 Like
understand, but as a temporary measure. Im installing the till tomorrow in Devon.
According to them im upgrading their router too to a DrayTek Vigor too⊠news to me, not even on their invoice they paid eitherâŠ
ooh can you count ticket states? so rather than tally into a custom data field itâll change the ticket state to say State:Daily Visit?