Views:

Scenario Overview

This is a bit of a strange business requirement but a customer was storing IP addresses in there CRM system & they needed to create a view which sorted the IP Addresses.We decided the easiest way was to pad the IP Address with zeros & place the result into a new field so we could sort on that field.

So in the example below we can see the following transformation,

 9.45.4.255 -> 009.045.004.255

 

N52 Formula Manager Solution

The Formula Manager solution works like this,

  • We create a formula of type 'Save - To Current Record' for the Account entity
  • This formula will be executed whenever the IP Address field changes or gets created initially
  • The formula checks to make sure that the IP Address is in the correct format by ensuring it has 3 periods
  • It then splits the IP Address up into 4 parts by using a period 
  • For each part it pads it out with zeros
  • Finally the result is placed in a new field called IP Address (Sort). 

N52 Formula Manager Steps

  • Create a new formula of type 'Save - To Current Record'
  • Set the Source entity to 'Account'
  • Set the Source Property to 'IP Address'
  • Set the Target Property to 'IP Address (Sort)'
  • Copy & paste the formula below into the formula description field & click save 
  • You are ready to test

Formula

if(CountCharacters([account.new_ipaddress], '.') = 3, 

    PadLeft( Split([account.new_ipaddress], '.', 0), 3, '0') + '.' +
    PadLeft( Split([account.new_ipaddress], '.', 1), 3, '0') + '.' +
    PadLeft( Split([account.new_ipaddress], '.', 2), 3, '0') + '.' +
    PadLeft( Split([account.new_ipaddress], '.', 3), 3, '0')

, 'NoOp')

Wizard - CountCharacters

Please see below the wizard you can use to create the CountCharacters() function call used in this formula. 

Note to find the value for Equals Value, you will need to go to the Source tab > Account > IP Address