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.

Building a Better Mouse Trap

I’ve been looking forward and a bit hesitant to make my first technical post but it’s got to happen sometime. It’s not a ground breaker and honestly I don’t expect to post at that level. I wanted to share some of the things I come across that I think are cool or may help someone else. 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.

One great aspect to being a solo admin is that you get to wear many hats in the role – Project Manager, Business Analyst, Administrator, End User, Data Analyst etc., etc. which is why I picked this little tidbit. It illustrates how a very simple and benign request can be built out into a more functionally rich solution by putting on those many hats. Sometimes the simplest answer is the right answer and other times you can just do better! Hopefully what you end up with is a user who sees their idea put into action beyond their expectations that can serve many other users equally well. Enjoy!

Initial Use Case –

We would like to identify companies that may be at risk of for timely payment so I have made a note on their account for upfront payment. The problem with that is we create (or should create) our opportunities through the contact, not the account, so people might not see this note when creating opportunities if they go straight to a listed contact.

Is there a way that if there is a payment history payment with a company and a note has been made to the account that this can be highlighted on each contact against that specific account?

Thought Process –

Users are not accustomed to reading through record notes and looking for this type of information. It’s just not how we normally operate so using Notes was not the best solution. Adding a simple text field to a contact record that appears at the top of the page would have partially met the request but only at a single contact level. That is not optimal if you have a client with dozens or hundreds of contacts.

Looking at this from an ease of use angle, it’s a lot more efficient if I can update or display this information on each contact related to the account for the user. I opted to force the entry to occur at that account level so the changes flow down to all related contacts. A single new field on the contact would be read only. The nice part about the contact getting its value from the account record, Text (Account.Payment_Advice__c), is that any new contacts added after the advice is entered would display the correct advice information immediately without any additional processes required.

It occurred to me that as a user who opens an account and sees negative payment advice that I would want to know who flagged the record and when. Is this timely information and who can I follow-up with for more detail?  You can use history tracking if you have fields available to track left but a) there are only 20 and we used them all and b) users don’t want to scroll to the history of a record and read through it for information. There can be a lot of history to troll through.

Solution –

Create a set of fields and process that allow a user to record payment advice that will appear on the account and appear on the related contact records while capturing the recording user’s name, date the entry was made and make a post to Chatter.

THE FIELDS

  •  Add Picklist Field to Account – Payment Advice

Values –

  1. “No Payment Issues” or
  2. “Negative Payment History – Upfront Payment Recommended

PA1

  • Add Email Field to Account – Advice Added By

Email type was chosen as the value will be the $User.UserName which an email address. I did this so a user, could if they wanted to email the person that set the Advice, click the email and pop up and email window. Little bit of a workflow benefit.

PA2

  • Add Date Field to Account – Date Advice Added

PA3

  • Add Formula Text Field to Contact – Payment Advice

Field is read only at contact level. Values – value recorded on Account.

PA4

The Process Builder

This process is kicked off when a user CHANGES the value of the “Payment Advice” field. Nice and simple. The values for the Date Advice Added and Added By are updated with the Update Record Option.

PA5

PA6

PA7

The Result

The user makes one selection, Payment Advice, and the newly added related field’s auto-populate after saving. The same advice note is now listed on the contact.

PA8

“Dropping” into the blog space

How many web or blog pages have you visited? How many of those pages visited did you find yourself contemplating the landing page images? Honestly, I couldn’t recall any and never gave it much thought until now.

Inspired to create a blog page, I jumped into WordPress not knowing what to expect. The learning curve was there but not insurmountable even for an “old” guy like myself. Many clicks, back buttons, and exacerbated sighs later the basic layout was done, a theme was settled on and links added. ‘Hey, this isn’t so hard’ I thought! Then came….the images…specifically the banner.

Kudos to Salesforce branding because as soon as you think SF.com you think of those clouds so that’s the road I started down. I mean after all it is a Salesforce blog. Finding public stock photos, saving, loading cropping previewing again and again until finally – success! I sat back and looked the screen expecting to be satisfied with my blog build acumen – wohoo time to celebrate!…Or not.

I starred at the screen for a while trying to figure out what was wrong. Why didn’t it feel right? What was missing? What was amiss? It was the image!

Those darn clouds! Not because there is anything wrong with them of course. As SF admins we love the clouds, but for me, on this space – nah-uh. I wanted something that represented me and us – the solo admins.  I took a lot longer to find an image that really fit with the image in my mind but eventually I found one that was synonymous. The water drop.

Solo Administrators who work alone, whether it’s supporting one or hundreds of users, are a drop in a pool but a drop that has a large impact. The things we do, the things we build, the innovations we make or the things we simply maintain have an impact that spread across our organizations like ripples in a pond. Most people won’t notice a single drop but they will be glad when they have water not knowing exactly how it got there. That may be a bit convoluted or overly philosophical but hopefully makes the point.

So there you have it. That’s how I picked my header image and the angst and consternation it caused me. The next time you’re surfing blogs take a second to think what the images on that page say to you.water-drop1