In certain situations, it would be very helpful to be able to write a null value to certain fields using the Rules Engine.
For example, some of our customers use rules to map CSMs from the Account object to Gainsight. This is done in the Load to Customers rule. Any time a change is made in their CSMs, the rule updates correctly.
However, if the CSM value is deleted, the "null" value is not pushed to Gainsight. So on accounts where the CSM has been removed, this change is not reflected in Gainsight.
This means our customers have to manually delete the CSM from Gainsight at the same time as they remove them from the account as this cannot be done automatically in the rules engine.
Thanks for your time!
-Logan T.
New Idea
Writing Null Values with the Rules Engine
Logan -- are they unable to detect the change because the underlying value was deleted? Or can they detect the change but not push the NULL?
I wanted to piggy back on this conversation, as we have a similar issue. We are looking for a way to write NA to the Usage Data objects for historical data. Is there a way to accomplish that?
Logan, I am going to assume that your issue was resolved. However, I will say that I just tested this our and it worked fine for me. I had a CSM field on the customer info object. Removed the CSM from account CSM. Ran the rule and it updated like it should.
Kclark, are you looking to write the actual string 'NA' to a string field, send a blank, or a 0? The answer depends on which one you are trying to do.
However, based on what you have listed you can accomplish this.
This assumes you are trying to over write data.
-Build a rule that sources your usage data.
-Bring in the fields you need for identifiers, such as the account id and date. If you have anymore, depending on your level of granularity, bring those in too.
-Apply whatever filter you want for the grabbing of your historical data, and bring in a date filter too. Something along the lines of date >= first day of current month and date <= last day of current month. This is to limit the amount of records you bring in and process.
-Move to the action page. Map your fields and apply the correct identifiers, such as date and account id. Add a custom field mapping, select it to be whatever field you are trying to write NA, I assume a string field.
-Put 'NA' in the text box. If you are trying to do this for a number you have to put a number in the field, such as 0. If you are trying to send a blank to a string you can type the word 'null' in there. The word 'null' used to work but, I am not sure if it still does or not.
-Run rule
This other scenario assumes you don't have historical data but trying to fill in a week.
-Build a rule that sources your customer info object.
-Bring in the fields you need for identifiers, such as the account id. If you have more, depending on your level of granularity, bring those in too.
-Apply whatever filter you want for the grabbing of your account information.
-Move to the action page. Map your fields and apply the correct identifiers, account id and/or others. Add a custom field mapping, select it to be whatever field you are trying to write NA, I assume a string field.
-Put 'NA' in the text box. If you are trying to do this for a number you have to put a number in the field, such as 0. If you are trying to send a blank to a string you can type the word 'null' in there. The word 'null' used to work but, I am not sure if it still does or not.
-Apply another custom field mapping, select the date field
-In the drop down apply for whatever is your scenario, if you are trying to fill in one missing week do a custom mapping for that date. If you are trying to fill in multiple weeks and your date is always on monday and your week starts is on monday put first day of current week. Or, you can put Rule date and then do it all in your scheduling too.
-Run rule
Hope this helps. Let me know if you have any questions.
Kclark, are you looking to write the actual string 'NA' to a string field, send a blank, or a 0? The answer depends on which one you are trying to do.
However, based on what you have listed you can accomplish this.
This assumes you are trying to over write data.
-Build a rule that sources your usage data.
-Bring in the fields you need for identifiers, such as the account id and date. If you have anymore, depending on your level of granularity, bring those in too.
-Apply whatever filter you want for the grabbing of your historical data, and bring in a date filter too. Something along the lines of date >= first day of current month and date <= last day of current month. This is to limit the amount of records you bring in and process.
-Move to the action page. Map your fields and apply the correct identifiers, such as date and account id. Add a custom field mapping, select it to be whatever field you are trying to write NA, I assume a string field.
-Put 'NA' in the text box. If you are trying to do this for a number you have to put a number in the field, such as 0. If you are trying to send a blank to a string you can type the word 'null' in there. The word 'null' used to work but, I am not sure if it still does or not.
-Run rule
This other scenario assumes you don't have historical data but trying to fill in a week.
-Build a rule that sources your customer info object.
-Bring in the fields you need for identifiers, such as the account id. If you have more, depending on your level of granularity, bring those in too.
-Apply whatever filter you want for the grabbing of your account information.
-Move to the action page. Map your fields and apply the correct identifiers, account id and/or others. Add a custom field mapping, select it to be whatever field you are trying to write NA, I assume a string field.
-Put 'NA' in the text box. If you are trying to do this for a number you have to put a number in the field, such as 0. If you are trying to send a blank to a string you can type the word 'null' in there. The word 'null' used to work but, I am not sure if it still does or not.
-Apply another custom field mapping, select the date field
-In the drop down apply for whatever is your scenario, if you are trying to fill in one missing week do a custom mapping for that date. If you are trying to fill in multiple weeks and your date is always on monday and your week starts is on monday put first day of current week. Or, you can put Rule date and then do it all in your scheduling too.
-Run rule
Hope this helps. Let me know if you have any questions.
1 person likes this
Thanks for the helpful response Wayne! To answer your question, I am looking to write 'null' values. I plan to give it a try today and will let you know if it works.
I tried to write 'null' to the scorecard field I get the error "should be a number." Is there some other way to write a blank (null) to historical scorecard values, or to get around this error? Thanks!
Unfortunately you can't write a null to a number field. You have to write a 0.
Are you looking for a one time push or something weekly?
If you are looking for a one time push you can do it on the backend. Meaning if it is SFDC then data load to the usage object. If it is MDA it is also a data load through S3.
If it is a weekly then the only option is to insert records first then update with the data.
Which scenario are you looking for?
Are you looking for a one time push or something weekly?
If you are looking for a one time push you can do it on the backend. Meaning if it is SFDC then data load to the usage object. If it is MDA it is also a data load through S3.
If it is a weekly then the only option is to insert records first then update with the data.
Which scenario are you looking for?
I am looking to write this weekly. Here are a few more details.
I have an MDA table full of numerical values (0 to 100) for every customer, every week. I used those numbers to write historical health scores over time (see this post to see what I did).
In some cases, the value '0' isn't bad, but it appears as a bad thing by appearing red on the health scorecard. In those cases, I want to overwrite the 0 (red, bad) with a 'null' (grey) value so as to not confuse my scoring roll up. Does that help?
I have an MDA table full of numerical values (0 to 100) for every customer, every week. I used those numbers to write historical health scores over time (see this post to see what I did).
In some cases, the value '0' isn't bad, but it appears as a bad thing by appearing red on the health scorecard. In those cases, I want to overwrite the 0 (red, bad) with a 'null' (grey) value so as to not confuse my scoring roll up. Does that help?
I've looked at the other post, to get the result you want is going to take some work and backend data loading.
FYI, this is an advance job and should only be handled if you know your way around SFDC and the backend.
-You have to do an export of your SFDC usage data object, where your historical scorecard data lives, bring in the id column and the scorecard columns, these columns are not easily identifiable as they are 'SC_'SFDC ID'__C. To know which columns are which you have to look at the JBCXM__ScorcardMetric__c object. Looking at the ID column and name you can associate the usage object SC sfdci id columns to its metric.
-Do a find and replace on the scorecard columns in excel, Find 0 replace with nothing for the columns you are trying to null out.
-Perform an update on those columns on the usage object
-Rerun your load historical values rule that you talked about in the other community post, ensuring not to push a 0 if a score is 0 to avoid what happened the first time.
-Adjust your weightings to something different save then change it back, the purpose of this is to have all of your historical overall scores recalculate so they take the Null into account. - Thanks you to Scott Morris for this part.
At this point you should be good to go.
The other option you can do. You are sourcing a MDA table and pushing values, I assume you have it setup Account id, scorecard measure 1, scorecard measure 2, etc.
If you have nulls in those column you should just be able to run the rule from the other table. But, it sounds like you may have 0's there. So, if you do an export on that object, update 0's to nulls, update the MDA table, then rerun the rule this should also fix the issue. But, you would still need to adjust the weightings to get the overall score to take effect historically.
FYI, this is an advance job and should only be handled if you know your way around SFDC and the backend.
-You have to do an export of your SFDC usage data object, where your historical scorecard data lives, bring in the id column and the scorecard columns, these columns are not easily identifiable as they are 'SC_'SFDC ID'__C. To know which columns are which you have to look at the JBCXM__ScorcardMetric__c object. Looking at the ID column and name you can associate the usage object SC sfdci id columns to its metric.
-Do a find and replace on the scorecard columns in excel, Find 0 replace with nothing for the columns you are trying to null out.
-Perform an update on those columns on the usage object
-Rerun your load historical values rule that you talked about in the other community post, ensuring not to push a 0 if a score is 0 to avoid what happened the first time.
-Adjust your weightings to something different save then change it back, the purpose of this is to have all of your historical overall scores recalculate so they take the Null into account. - Thanks you to Scott Morris for this part.
At this point you should be good to go.
The other option you can do. You are sourcing a MDA table and pushing values, I assume you have it setup Account id, scorecard measure 1, scorecard measure 2, etc.
If you have nulls in those column you should just be able to run the rule from the other table. But, it sounds like you may have 0's there. So, if you do an export on that object, update 0's to nulls, update the MDA table, then rerun the rule this should also fix the issue. But, you would still need to adjust the weightings to get the overall score to take effect historically.
Hi everyone - I was able to complete this successfully thanks to Wayne's guidance 🙂. Fortunately all of our raw data was stored in an MDA table so it made my task a lot easier. I created a calculated field that set the values to NULL when required and re-ran the historical data load. The NULL values overwrote the existing values where appropriate. Here's what it looks like now.
Cheers!
Adding my use case here, as I'd find this feature to be very helpful.
We use rules engine to identify the latest renewal opportunity for each of our customers. We then load this to Customer Info, and use it to allow us to set renewal probability directly from Scorecard 2.0.
The problem is that I need to be able to clear out the "latest renewal opp" field if the renewal opp for a given customer is closed as won or lost. This is because I do not want these opportunities to be considered as 'in play' going forward, and I don't want any changes to be made to them based on changes made in Scorecard.
To clarify, I've set some conditions on the rule that updates opp probabilities from Scorecard to ignore opps that are closed. However, this still doesn't close the loop as a value (the opp id) remains on Customer Info linking to the opp that _used to be_ open but is now closed. The creates the possibility that invalid actions may be triggered on this particular opp. Writing a text value into the opp id field on Customer Info will technically address the issue, but adds unnecessary complexity to rule design (addl action and filter steps).
I can also minimize this risk by joining in and filtering on values from the opp table in every rule that references this field, but this is approach is also cumbersome and error prone. It would be much cleaner to simply write a null value to this field when there are no open renewal opps for a given customer.
Thanks for considering my use-case. Please keep us posted if this feature is to be included in the product roadmap~
We use rules engine to identify the latest renewal opportunity for each of our customers. We then load this to Customer Info, and use it to allow us to set renewal probability directly from Scorecard 2.0.
The problem is that I need to be able to clear out the "latest renewal opp" field if the renewal opp for a given customer is closed as won or lost. This is because I do not want these opportunities to be considered as 'in play' going forward, and I don't want any changes to be made to them based on changes made in Scorecard.
To clarify, I've set some conditions on the rule that updates opp probabilities from Scorecard to ignore opps that are closed. However, this still doesn't close the loop as a value (the opp id) remains on Customer Info linking to the opp that _used to be_ open but is now closed. The creates the possibility that invalid actions may be triggered on this particular opp. Writing a text value into the opp id field on Customer Info will technically address the issue, but adds unnecessary complexity to rule design (addl action and filter steps).
I can also minimize this risk by joining in and filtering on values from the opp table in every rule that references this field, but this is approach is also cumbersome and error prone. It would be much cleaner to simply write a null value to this field when there are no open renewal opps for a given customer.
Thanks for considering my use-case. Please keep us posted if this feature is to be included in the product roadmap~
1 person likes this
Sign up
If you ever had a profile with us, there's no need to create another one.
Don't worry if your email address has since changed, or you can't remember your login, just let us know at community@gainsight.com and we'll help you get started from where you left.
Else, please continue with the registration below.
Welcome to the Gainsight Community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.