Clear a certain field for all entities

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 :wink:

1 Like

I cant get it to fire, sending screen shots 2 secs





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 :joy:

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?

@JTRTech this one - if you could?

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?