How to fix PowerPoint Chart Data Refreshes if You Used the “Use Destination Theme and Link Data” Option.


So, this is a Salesforce blog BUT sometimes you come across a tidbit of off topic information that is worth sharing with others. This may be of interest to anyone who crunches data in excel and pastes those charts into PowerPoint. I ran into an issue with charts pasted into PowerPoint using the “Use Destination Formatting and Link Data” option. Googling the issue yielded less than optimal answers that just couldn’t be the only way to fix this. I found that the easiest way to “fix” it wasn’t called out anywhere. Also, imagine my surprise and disappointment at the snarky and condescending responses on many of these boards to people just asking for help not ‘tude. That’s another reason I am sharing my findings and hope it helps someone else out there.

Background – I am working on a market research related project. The major tasks at hand are collecting and collating the responses for both quantitative and qualitative questions, creating a PowerPoint with charts and analyzing commentary for call-outs and trends. Drawing on prior experience working for a premier market research company, I was able to create an excel tab in format that makes it easy to transcribe Yes/No and multiple choice questions. That input is now countable data points and in turn those become colorful charts to summarize all this great data.

Those nice charts need to make their way into a PowerPoint presentation – easy. Now this where you will find multiple schools of thought on how to do this and refreshing those linked charts. I’m going with the two most prevalent.

The first option is to copy the chart from Excel using copy>paste special>Microsoft excel chart object. The appeal of this method is that when the data changes in excel and the PowerPoint file is opened the chart updates without any additional clicks. You’re all set if you are happy with how the charts look when pasted this way. I’m not a huge fan because charts can become distorted with resizing and the color palettes do not always to translate exactly.

ref1

The second option is the one I prefer. In Excel, copy the chart and in PowerPoint >right click on the slide >pick either Use Destination Theme and Link Data OR Keep Source Formatting and Link Data.

ref2

I’ve found that PowerPoint often times does a better job determining a palate that is more visually appealing than Excel but either link data option will work.

Problem – Now, you would think by picking and option that says LINK DATA that the data would refresh on its own. Design flaw or not, it does not refresh automatically! OMG! Many people to post frantic messages on various boards asking for help. I was a bit surprised at the snarky responses on many of these boards so hopefully this helps someone later.

Ok – solution time. Most replies told people they had two options. One, paste the charts all over again and use method 1. Ugh – not pleasant if you just created a massive file! Two, refresh EACH chart one at a time. Again, ugh, you can’t be serious. Luckily there is a third!

In PowerPoint 2013: Click File> Options> Quick Access Toolbar.

Change the drop down to “All Commands” and scroll to “Edit Links to Files”>ADD>OK

ref4

The Edit Links to Files is now in the quick access menu – Click it.

Click on each chart and check the “Automatic Update” You must do this for each chart.

ref5

The next time you open the file a pop up will appear asking if you would like to update now. Click Yes.

ref6

Ok – you’re all set and you don’t need to copy and paste in all of those charts again!

Copy Dependent Picklists Quickly and Easily From Salesforce

Do you have field dependencies and you’d LOVE to copy the edit screen for that dependent field? I know I do and from what I’ve seen many others do too.  A Google search on this topic will bring up a litany of Salesforce Ideas for an exportable or printable view and countless questions about how to get a usable version of this list out of Salesforce. The most common use case seems to be the need to put the list into a file format, excel for example, where it can be reviewed, sorted, edited etc. It’s a convenient way to share the list with people, add to documentation or embed in training slides.

The best and most flexible solution that I’ve found is a Google Chrome plugin called Copytables. It’s great because it works on other tables in addition to Salesforce. It’s a multitasker – Yay!

Copytables – Grab the plugin here and follow along with the steps. https://chrome.google.com/webstore/detail/copytables/ekdpkppgmlalfkphpibadldikjimijon?utm_source=chrome-app-launcher-info-dialog

Copytyableimg

  • Pick a field with a dependency and go the edit screen.
    • Right click over the table
    • Click TABLE then Select Table

This will highlight the entire table!

Copytyable1

  • Click TABLE > COPY > AS IS

I like to use AS IS to retain the colors in the table. Why? In Excel, you can sort by color so it is quick work to weed out irrelevant values. Very cool and very fast!

Copytyable2

  • In a blank excel worksheet, use PASTE or CTRL V to paste your table.

Presto – there it is! Not only is it easy to copy and paste but it’s in a usable format!

Copytyable3

  • Lastly, Enjoy 🙂

Finally! A Use for Invocable Processes For Our Org….

There was a lot of excitement in the Process Builder community when invocable process were released last year. I attended the webinars, read the release notes, checked out the blogs with the hope of being ready to streamline existing processes or build better ones later with less effort. Tick, tick, tick….many months later and I still hadn’t found that use case that worked for us – until two weeks ago.

A very engaged set of users bent on improving the system presented some ideas they had formulated. This group is great because they really do want to improve how they use Salesforce and if the idea is extendable to other units all the better. It’s that innovative group that every admin fears when the email pops up but at the same time the one we love the most because they are as engaged as we are. Ok – enough flattery for them – on to one of the ideas that started all of this.

Can a Salesforce trigger change the “Close Date” for us?

Ok so let’s break that down a little more –

Current State

  1. The Sales team selects today’s date from the calendar or manually types in the Close Date when an Opportunity is changing to a closed stage (Lost/Won/Cancelled). Can it just update for us so we don’t have to enter it?
  2. Saving the opportunity triggers a validation rule: Close Date <> TODAY (). The incorrect date results in an error message stating “You have closed this opportunity but the date provided is in the past or future. Please enter today’s date.”

Request The Sales team would like the Close Date to populate with today’s date to eliminate a) small amount of data entry and b) eliminate the chances of encountering the validation error should that date be incorrect.

Ideal State The Close Date will auto-populate with today’s date upon Save.

Challenge The business allows users with special permissions to update CLOSED records. Implementing a Process Builder that adds the requested date will fail for most users because the edit of the date would occur after the record is flagged as IS CLOSED.

So there you have it – a need to update the record with the Closed Date using Process Builder but there is that pesky validation rule waiting in the background with the dreaded process flow error.

This is where invocable processes, a solution from a knowledge article and an update process converged for an All Clicks, No Code solution!

Part 1 – Bypass the Validation Rule

I looked on Google, of course, for a solution some time ago and did find ideas but they all used Apex or flows. Apex, beyond what I’ve done on Trailhead, is out of my wheelhouse and I have attempted flows which was not an overwhelming success.  A renewed, last-ditch search effort yielded a new result scaring up a knowledge article from Salesforce titled Bypass Validation Rules for Process Builder. What Luck! The article walks through two scenarios and offers two workarounds. The workaround implemented in this case is the second.

The use case in the article centers on Accounts and the automatic updating of related Contact mailing addresses.

“On Account you have a multi-select picklist and you use Process Builder to update the same field on the related Contacts. Now if some Contacts do not have a valid Mailing Address, you will get an “ALL_OR_NONE_OPERATION_ROLLED_BACK” error.”

The solution was:

“Implement a field which allows bypassing validation rules for Processes, on Account have a checkbox field BypassValidationForProcessBuilder__c, default unchecked, not shown on the Page Layout.”

Adjust the Validation Rule to:

AND( NOT(Account.BypassValidationForProcessBuilder__c) ,

OR(

ISBLANK( MailingStreet ),

ISBLANK( MailingCity ),

ISBLANK( MailingCountry )))

 In your Process:

  1. In the immediate action block, first use a record update for the concerned Account record to set our Bypass field to TRUE.
  2. Within the same immediate action block, you can now have an action to update the related Contacts, use as many actions as needed.
  3. Then at the end have another update action to set the Bypass field back to FALSE.

Within one immediate action block, the actions are done in order and separately.

That’s great…. if I was trying to solve the problem of Account mailing address changes cascading to Contacts but this workaround could be readily adapted to suit the addition of the Close Date for users. Swapping out the Contact Object for the Opportunity Object is all you need to accomplish this. Let’s build this reusable Bypass functionality out step by step and then apply it to update the Close Date on the Opportunity.

Create the Field – The first thing we are going to do is create the field that will be referred to in the Bypass processes and validation rules.

  1. Go to Set-up>Customize>Accounts>Fields
  2. Click NEW>Checkbox>Next
  3. Name the field. How you name it is up to you but having Bypass in the name will help identify it later. I went with BypassValidationRule to shorten up the Salesforce name but keep it obvious.
  4. Specify Visibility and Page View settings
    1. For visibility, I set that to admin only and did not add it to page views. Note – In the sandbox, I did add it to the page views while testing.

Now we build the to invocable PB processes. First we will create the Bypass = Yes process.

Create the Processes – This first process will set our Checkbox to True.

  1. Go to Set-up>Create>Workflows and Approvals>Process Builder (or type process builder in the Quick find to get there faster)
  2. Click NEW and Name the process. I named mine Bypass Validation Rules YES. YES or True in the names makes it easy to know which one to us later.
  3. Select – The process starts when – It’s invoked by another process and click Save
    1. You will notice on the next screen the process name has (invocable) at the end
  4. Pick Account as the OBJECT
  5. Criteria = No Criteria
  6. Click + to add an Immediate Action:
    1. Action Type = Update Record
    2. Action Name = Set Bypass to Yes
    3. Record Type = Select the Account record that started your process
    4. Criteria for Updating Records = No criteria—just update the records!
    5. Set new field values for the records you update
      1. Field = BypassValidationRule (add your field’s name; this is mine)
      2. Type = Boolean
      3. Value = True
    6. Save and Activate

Now, create the second flow, Bypass = No This process will set our Checkbox to False.

While in your newly created YES process, click Clone and select “A New Process”.

  1. Name the process- I named mine Bypass Validation Rules NO.
  2. Click on the Immediate Action – Set Bypass to Yes
  3. Change the name to Set Bypass to No
  4. Change the Value to = False
  5. Save and Activate

Now we have our bypass field and processes all set-up. Time to apply them to a process and update our Close Date without triggering the validation rule!

Bypass PB 1

Create the Process – This is where we set the close date.

  1. Go to Set-up>Create>Workflows and Approvals>Process Builder (or type process builder in the Quick find to get there faster)
  2. Click NEW and Name the process. I named mine GLOBAL – Autofill Close Date on Close Opps. Global is the designation used when something applies to all business units in our org.
  3. Select The process starts when – a record changes and click Save
  4. Pick Opportunity as the OBJECT
  5. Criteria – Give your criteria node a name
    1. In this case, you’ll see that we are applying this to records based on stages, not ISCLOSED. This was done based on other articles stating that using ISCLOSED is not the best practice and that we also only need to trigger this one time when the stage changes to one that is closed. The stages will vary based on your stages and org. In our case, once it’s closed; it stays closed. On to my criteria:
Field Operator Type Value
StageName IsChanged Picklist True
StageName Equals Picklist Closed Cancelled
StageName Equals Picklist Closed Lost
StageName Equals Picklist Closed Won
StageName Equals Picklist Closed No Bid
  1. Customize the logic – 1 AND (2 OR 3 OR 4 OR 5)
  2. Immediate Action 1:
    1. Action Type = Processes
    2. Action Name = Set Bypass to Yes
    3. Process = Select the Bypass Validation Rules YES process
    4. Set Process Variable
      1. Type = Reference
      2. Value = Account ID
        1. Note – Do not pick the Account ID> option. This will not work. Scroll down to the Account ID without the ” >”
      3. Click Save
    5. Immediate Action 2:
      1. Action Type = Update Record
      2. Action Name = ADD TODAY
      3. Criteria for Updating Records = No criteria—just update the records!
      4. Set new field values for the records you update
        1. Field = CloseDate
        2. Type = Formula
        3. Value = TODAY()
      5. Click Save
  1. Immediate Action 3:
    1. Action Type = Processes
    2. Action Name = Set Bypass to Yes
    3. Process = Select the Bypass Validation Rules No process
    4. Set Process Variable
      1. Type = Reference
      2. Value = Account ID
        1. Note – Do not pick the Account ID> option. This will not work. Scroll down to the Account ID without the “>”
      3. Click Save
    5. Go to a separate tab or window and update any validation rules that need to be bypassed with the addition of the NOT(Account.BypassValidationForProcessBuilder__c) edited to suit your field name.
    6. Now ACTIVATE this process!

Bypass PB 2

 So what about those “other things” this could be applied to that made the invocable useful right away?

Using the Bypass processes allowed us to add a name to a record which is only done at time of closure. This stopped the triggering of a delayed action process builder error email that served as a prompt to go update the record manually. This occurred when the user closing the record lacked the permissions to edit after close. It was a minor improvement to the data timeliness and took a bit of work off the administrator. Not earth shattering but hey, a win is a win.

Earlier I said “That’s great…. if I was trying to solve the problem of Account mailing address changes cascading to Contacts”. Well, the bypass is getting added to that process and related validation rules. We rarely see that error anymore but it will be even nicer to never have a user or myself see it again!

 

Simple but Functional – Reduced Data Entry = Happy Sales People

This was one of those things that had been languishing on my to-do list for a while. It was a nice thing to do when time permitted but something always took precedent even though it didn’t take long to do. Sometimes a poke from a user helps elevate things in the list.

Request from SF Ticket – I have been working in SF today and noticed that when you move a contact from one account to another the contact’s address doesn’t automatically change to that of the new account – is there any way to change this so that it happens automatically without having to manually go in and edit the contact and update the new address?

Solution – A new process was built that will automatically copy the 10 related address fields when the Contact’s AccountID is changed.

  • Accounts billing address details copied to the Contacts Mailing address and
  • Accounts shipping address details copied to the Contacts Other address

Step 1 – Create a New Process with Process Builder.Select Contact as the Object.

c1

Step 2 – Enter the criteria to trigger the process. The contacts Account ID will change when the Account is changed so our criteria is: Contact.Account ID IS Changed = TRUE.

c2

Step 3 – Match up the Account Address Fields to your Contact Address Fields. In this case, Account Billing Address maps to Contact Mailing Address fields and Account Shipping Address maps to Contact Other Address. Note the highlights portion below regarding mapping.

c3

c4

Step 5 – Sit back and watch happy users.

A funny thing happened….

A funny thing happened to me on the way to my next blog post. I was planning to write about wearing many hats that I alluded to in the last post but something a lot neater came along. I was stuck on a problem with a field validation rule and was getting nowhere with the solution I wanted / needed.

So where do you go when the great Google fails?…the community!…So off I went to the Who owes me a beer?!? user group (one of many I belong to).

The question I posted was pretty straight forward:

“I have a text field that is 6 characters long and I want to prevent users from entering a leading 0, like 012345. I Googled and read as much as I thought it would help so now I come to ask the real experts. I’m assuming RegEx is the way to go? And just what may that way look like. REGEX(Number__c, What goes here?)”

The first response came within two hours from Jeremiah Dohn. He more than addressed my question. His reply came with not only an answer; it also came with a link to a great presentation that he had given previously. It does a wonderful job of explaining RegEX.

“You could do this with:
NOT(ISBLANK(Number__c)) &&
NOT(REGEX(Number__c, “[1-9]\\d{5}”))

This is assuming that you are requiring that there are exactly 6 numbers. If you want to have less numbers required, you can use\\d{1,5}, which assumes that you have two total digits, counting the initial one…“

I’ve attached the link to the presentation so if you have a Salesforce login you can access it. This is a good read to get more familiar with the topic.

The second suggestion came from John Sadler that used a much simpler but equally effective solution. It was a bit of a head-smacker where I read it.

“If struggling with REGEX you could go with a much simpler solution LEFT(Number__C,1)=”0″. “0” because this is a Text Field. If the ” ” are left off you will get a syntax error because the rule is expecting text. Only works for Leading 0, so it will allow a 0 elsewhere in your text field….”

I want to thank both of them for their help and for giving me something fun to write about. What made this fun? In my last post I said “I’m not saying it’s the right answer or wrong answer but it is an answer that I arrived at. That’s the wonderful thing about SF.com’s user community – we love to share.” This was such a great real world example of how two equally valid solutions to the same question came from two people willing to take the time to help out another person and share the benefits of their knowledge. I hope you enjoyed the story.