If you’ve seen any of my articles before, you may have seen that I speak about the Connected Data feature where you can use Excel to push data into your Word/Powerpoint/PDF template. The possibility is endless when it comes to Connected Data so it may be difficult to figure out what you can do with it.
One use case that I encountered was removing the country code from the phone number if the country code is filled out. If it is not, then we want the use the phone number as is. One option that admins could do is to create a Salesforce field to determine this but I have encountered many that do not want to create a new field either because of having too many already to maintain or not wanting to create a field with the only use of it is to be used for DocGen.
In my specific example, I will have a field where the user may or may not enter the country code on the contact record. In the scenario where the user have entered a value in the country code, then we want to remove that many numbers from the phone number. In my Excel file, I have tagged the fields from Salesforce with the country area code and the phone number
The third cell will contain an Excel formula:
=IF(ISBLANK(Contact_country_area_code__00),Contact_Phone,MID(Contact_Phone,LEN(Contact_country_area_code__00)+1,10))This formula is determining couple things:
- Whether the country code is blank. If it is blank, then it will just pull the contact_phone value as is
- If the country code is not blank, then it will determine how many numbers are in the country code and remove that many numbers from the beginning from the phone number
The value outputs based on how many numbers are in a country code. If it has 1, then it will remove the first number; if it has 2, then it will remove the first two numbers…
The cell will then be given a unique name (make sure it does not overlap with what you have in Salesforce. I recommend using something without an underscore to ensure it does not overlap). In this scenario, I have used “phonenumber”.
Once that is done, make sure that the document is uploaded to the DocGen Package. If you do not want the users to be seeing this Excel file, you can set the pages from 0 to 0. The document will still process, however the user just won’t be seeing it in the output. I recommend having the Excel document as the first template in the package.
The Excel part has been completed. Now you will just need to go to the Word/PowerPoint/PDF document that you have an tag the document with the name that you called the cell. In my case, I have called my cell “phonenumber” so in my Word/PowerPoint I will tag as <<phonenumber» (In PDF, you will just need to do it without the << >>):
And that’s it! The DocGen Package will then manipulate the data based on what you have done in your Excel and output that value in your template.
It’s ultimately up to you on what you want your connected data to do. The cool thing about is that you can use the complex formulas that Excel offers but display that in a nicely formatted template in Word or PowerPoint or even PDF.
