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;
}
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).
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;
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?
Was just a thought on an alternative method of counting multiple tickets in a day as only one visit.
If you find a simpler way to count ticket states you can set a custom state to the first ticket of a visit maybe.
It’s not tried or tested method just an idea.