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.

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?

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'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.

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!