Unexpected consequence of BLANK = BLANK and list item deletion

duncan_pearson

I wanted to share something that could easily catch out the unwary and have unexpected consequences.

Let's say you have two line-items (say listFormattedLineItem1 & listFormattedLineItem2) formatted on the same list (formatList) and you compare them in a formula as part of an IF THEN ELSE - e.g.

IF listFormattedLineItem1 = listFormattedLineItem2
THEN expensive_value
ELSE BLANK

and all of the cells of both line-items are populated, then everything is fine.

Let's just suppose however that you have a data management process and it works like this

1. Delete all of the items of formatList using a Delete From List using Selection action where the selection is TRUE
2. Import new items for formatList
3. Import values into listFormattedLineItem1
4. Import values into listFormattedLineItem2

At the end of this process everything should be fine. However each step in the process is a transaction and the platform recalculates the model after each step. After step 1 there are no items in formatList and so all the values are cleared from all line-items that are formatted on that list. All the cells become BLANK.

As the title indicates, in Anaplan BLANK = BLANK evaluates to TRUE, and so in the formula now all the cells are calculating the expensive_value. The disadvantages of this could be that the values take to much time to calculate, or that the resulting values take up too much space if they are TEXT. In the first case the process would take too long and in the second it might fail with OutOfMemory.

 

The solution is simple. Just put a BLANK check in the formula.

IF ISNOTBLANK(listFormattedLineItem1) AND listFormattedLineItem1 = listFormattedLineItem2
THEN expensive_value
ELSE BLANK

 

In fact I would recommend the BLANK check in any case to make the behaviour of the formula clear in all cases.

JaredDolich

@duncan_pearson 

Brilliant. Sometimes the best solution is the simplest but it's not always obvious. This should go into the hall of fame for data integration best practices. Nicely documented too!