Using Conditional Field Mapping to Avoid Overwriting Data with Null Values

Application integration is often harder than it seems. Even simple tasks can get complicated quickly. However, we recently added a new feature to the Connect capability of TIBCO Cloud Integration that can really simplify your integration logic. It checks to see whether the value in the field of a source record is null so you don?t overwrite data in that field in a target system.

To see how it works, let?s consider the common use case of integrating leads from a marketing automation system into a CRM system. The process looks something like this.

1. Get new and changed leads from the marketing system that are ready for the sales team

2. Upsert the leads into the CRM system

3. Repeat periodically

In the real world, an integration like this would also have to handle the fact that CRM systems make a distinction between leads and contacts, and we might also want the data to flow back in the other direction. But let?s keep things simple for the sake of this exercise.

Once the lead is in CRM, the salesperson may talk to the prospect and add new information like address and phone number that the marketing system did not have. If the marketing system also gets new information about the lead (favorite color, perhaps?), the integration will repeat the upsert step for the record. If the address and phone number fields are mapped but contain no data, those fields will be overwritten in the CRM. How can we avoid this?

First Approach - IF Blocks

TIBCO Cloud Integration supports conditional logic. We can use an IF block to test whether a source field is null and omit it from the integration to avoid overwriting the data.

This solution is OK if there is only one field to test. But the address alone may be spread across 6 or more fields:

• Address line 1

• Address line 2

• City

• State

• Postal Code

• Country

Testing for null values in each of these fields separately would be impractical. There are 2⁶ = 64 possible combinations of finding null or not null in each of these 6 fields. The best we could do would be to test one that is most likely to have data, such as state, and assume that if it?s empty, the others are too. That would probably work in most cases, but it?s not foolproof. We can do better.

An Improvement - Conditional Field Mapping

TIBCO recently introduced a new feature in the Connect capability of TIBCO Cloud Integration that takes care of this without complex conditional logic. The field mapping screen on the Upsert and Update/Insert operation blocks have a new checkbox called ?Skip Mapping if Null,? which does just what it says. If you check this box and the value in a source field is null, the integration will not overwrite any data on the target site with the null value when you map the source field to a target field.

This feature allows you to map as many fields as you wish without worrying about which might contain null values in the data. If they do, we just won?t map them.

But Wait, There?s More!

If you want different behavior for individual fields instead of treating them all the same way, you also have a few more options.

• Enable Skip If Null on individual fields

• Test a condition to determine whether to write the source value to the target

• Check whether the source field exists using IsFieldSet, which is also available as a formula

To learn more, check out the documentation. You can also reply to this post with questions in the comment field below. Have you found an innovative way to use these new capabilities or solved a particularly tricky problem? Let others know.