Sometimes data is stored in a way that makes reporting difficult. In this example, the Interests Summary field on the Appointment record is a 32,000 character text field, however it contains Quantity data that we'd like to pull into a Report and Sum the numeric values for the Quantity.
We will need to use several formulas to extrapolate the Number from the String.
Because the Interests Summary field is 32,000 characters we need to use a Field Update automation to get the Text string to the right of the “colon space” characters. In some cases this text will include Description and other data after the Quantity value within the text string, but other times does not have extra data after the Qty value.
The formula we use:
RIGHT(i360__Interests_Summary__c,(LEN( i360__Interests_Summary__c)-FIND(": ", i360__Interests_Summary__c)))
The output of this Formula for the same records shown above:
Next, we will use a formula field to get the first “word” within this field. This formula is dependent on a space after the first “word” within a text string. But, if there is no space in the text string, the formula returns a Blank value.
LEFT ( Interest_Qty_Text__c , FIND (" ", Interest_Qty_Text__c ) - 1)
Here is the output value of this formula for the same Appointment records, notice how records that did not have a space after the numerals return no data:
Last, we will create the final formula and the Field you’ll want to use within List Views and Reports.
This formula states that IF the “Qty from Interest” field is Blank, then use the Numeric Value from the “Interest Qty Text” field, otherwise if not blank, give the Numeric Value of “Qty from Interest field”.
This formula assures that if there are no spaces found, we are still able to garner the Qty value from the First formula field which we updated via the automation.
IF (ISBLANK(Interest_Qty_Text__c) ,0,
IF(ISBLANK(Qty_from_Interest__c), (VALUE(Interest_Qty_Text__c)),
VALUE(Qty_from_Interest__c)))
Here is the final “Interest Qty” formula output for the same Appointment records, alongside the managed Interests Summary field for comparison:
To summarize, you’ll want to use the Interest Qty field and disregard the other fields. They are necessary, but will not display wholistic data. For this reason, you may want to add the words “do not use” in the Field Name or Remove visibility of the field from specific Profiles using the Set Field Level Security button, to ensure Users do not accidentally choose the wrong field.