This was a talk at Japan Dreamin' 2020.
This presentation is on YouTube.
This is about three or more ways that I often use to do bulk updates of data. The reason for this presentation is that I'm often finding myself having to update Key data for my clients because the data has calculations that are very complex and mostly updated via field updates because the formulas get bigger than the 5000 character compile limit.
So I’ve set up a mock scenario that has some aspects of processes that I have set up for my clients orgs.
A consistent way to update one or multiple records
Be within limits so no errors occur
Be easy for Admins to modify if the business logic changes
Be easy for Users to run if needed
As I was preparing for my talk, the massive bush fires were raging across many areas of the country, so I thought I would incorporate that into my scenario. So this is an imaginary scenario with some basis of fact. The fact is that in Australia, our fire services in the country areas consist primarily of volunteer fire fighters. The larger cities have paid fire brigades. We as home owners need to pay for our fire services. My imaginary scenario is that we pay for these fire services as a levy when we buy a new house. This levy is a complex calculation based on the assessed value of the land component only.
Now, after the bushfires, the Federal Government wants to impose a National Fire Levy on top of the Local Government and State Levies, to assist in the rebuilding of nationally significant structures, and rehabilitation of crown land and the animals on that land. They’ve made the new Levy based of the full price of the House and Land. It starts on January 27th, for all houses sold after this date.
So, we have to update all our pricing to use the new Levy after that date.
So I've set up a spreadsheet for how these complex calculations work. Whenever my clients have very complex calculations, it is really important to have a spreadsheet like this where all of the formulas are there in a really easy to understand way so they know exactly how the formulas work. Then, when you build this these calculations into Salesforce you always have the the sample data to go back to so that you always know the calculations and if you make changes to the calculations in Salesforce, that those calculations are now saving the correct data.
The calculation is:
Get the Assessed Price of the Land from the Land Titles Office
Get the levy from the Local Government Area, if it has one
Apply the LGA Levy
Get the levy from the State, if it has one
Apply the State Levy
After the introduction of the National Fire Levy
Get the Base Price plus the LGA Levy plus the State Levy and apply the National Levy
Add all the Levies together
Add the rounded Total Levy to the Base Price
Add any Extras
This is the List Price
The List Price is updated onto our website as soon as it is updated in Salesforce
Deduct any Discount
This is the Asking Price
(This is an imaginary and overly complex calculation).
This is based off the Dreamhouse App - just using the Properties Object
The Property has a status of Available, and a lookup to the Local Government Area. Only Properties that are Available ever have their pricing updated.
The new National Levy, and the State Levy values are stored in Custom Metadata Types.
I know, if this was in the USA with many many states, Custom Metadata Types may not work, so I would probably revert to code. But in Australia with only 6 states, and two Territories, it is manageable. |
The Local Government Area Levy is stored on the LGA Account record that is linked to the Property.
The Pricing Fields on the Property are all Currency or Text Fields. There are no formulas.
Sometimes, calculations in real world business scenarios end up being so difficult and so involved and use custom metadata types and complex formulas and at some point in time down the chain of all the formula fields the formulas will go over the 5,000 compiled character limit. When that happens is just, No way out, it's a hard limit that you can't get Salesforce to change it. So then you have to then revert to workflow rules, or code, or something to calculate the data elsewhere and store the values in your record. So for anything like this for complex pricing or complex calculations, I tend to always do them as fields rather than formulas. Now that does allow more complications to come in because we have to then keep all the fields updated. So most often what I would do is create the formulas in simple workflows and fire them every single time the record is saved so it acts more looks like a formula. The new Spring '20 feature of Before Triggers on Flows might be very helpful to do these sorts of updates. But of course, there is always code, also. |
The Pricing is only updated by one field - Update Pricing - being set to true.
Why have a boolean field update the calculations? In this scenario we have values external to the record - the Custom Metadata Types. So this allows us to update the CMDT value, then just trigger the calculations by changing one field on the record. This allows us to very quickly update one record, or many records. |
The pricing calculations are all handled in one Autolaunched Flow.
Note: This flow can be as simple or as complex as needed, and can even call Apex via Invocable Actions and even make callouts to external systems via multiple methods (eg my TDX talk for External Services).
There is a Quick Action to quickly update the pricing on individual records. This can also be used on bulk records.
There is a Process Builder to invoke the Flow based on update of the Update Pricing field.
There is a List View to enable quick bulk updates, just like a spreadsheet.
There is a Screen Flow to call the calculate pricing Flow. This allows you to build in some niceties for the users as they update the flow, such as extra steps, warnings, or graphics.
Use List Views
Up to 50 records at a time
Can directly edit the Boolean field or run a Quick Action on each of the records.
Advantages
Easy setup
Users don’t need to leave Salesforce
Best for individual Sales Reps updating My Available Properties or similar
Disadvantages
Time consuming for more than 50 records
Can be a bit fiddly
Basically just initiates a Batch Apex Job
Advantages
Easy to set up
Can be scheduled
Disadvantages
Beware, it’s very easy to miss a criteria and run the flow on far too many records! Take my errors as advice
No way to see when it has run and what records it has run on
No way to set the batch size - it always runs one flow per record
It’s hard to do ad-hoc flows because you have to save the flow as a new version each time you change the date and time it runs
Always create a report with the exact same criteria as the flow so you can see which records the flow will be run on. And make sure they are kept consistent if you change the flow criteria or the report. |
From Xappex
Advantages
Great product - so versatile
Easy to set up for Admins to use
Easy to download reports to use as the basis for updates
Once set up, it’s easy for users to run a bulk update
Can be scheduled - but it’s a bit trickier to set up
Good Logging, if the file is saved after it is run
Can set the batch size
Disadvantages
Additional Cost
Can be fiddly to set up for non-System Admin users to use
Note
Enabler4Excel has a free version where you can download reports and SOQL statements into Excel (but not update back to Salesforce. But it’s only for up to 2000 records in one sheet. So well worth paying for enterprise version.
PowerQuery for Excel, standard in Excel 2016 and later can download Reports and Objects from Salesforce directly - again with a 2000 row limit.
You can use Data Connector for Google Sheets to achieve similar, including updates, for no cost. See below. |
Mass Action Scheduler. A community supported app, By Doug Ayers
This is quickly becoming my No. 2 go-to-app to do cool things in Salesforce. The No. 1 app is of course Declarative Lookup Rollup Summary
Advantages
Great product - so versatile
Can run with reports and flows, or SOQL or Apex and many combinations
Can set the batch size
Easy to set up
Easy to change the report to change the which records the flow is run on
The only key things about these reports, they must have an ID and they must have be a standard list report not a matrix or group report
Can be scheduled - easy configuration
If set up well, key users could run bulk updates
Great logging
Bell notification when finished
Disadvantages
Community supported product only
Sometimes batch jobs go in to holding mode
Tips
Keep your reports locked for editing from most users, as it might change the way the MAS is run without you knowing
Don’t change the report after you have kicked off MAS and it’s in holding, it will run on the wrong set of records (so be very aware if multiple users are using this)
This creates a Batch Apex Job so you can look at the status of the job either by the logging or the Apex Jobs tab in Setup
Since you have one checkbox to run your flow, you can use many other methods to update your calculations. A grid tool like Conga Grid, GridBuddy, or Apsona for Salesforce. Record Hunter Labs App that can run flows for multiple records (I have not tried this). Data Connector for Salesforce - to connect Google Sheets to Salesforce. But I would still try Xappex’s G-Connector if your only option was to use Google Sheets. Or even just use Dataloader or Dataloader.io to update the checkbox field to true, but that seems like a lot of work.
So, with a simple checkbox, and a flow, and one or more easy to use tools you have a great way to update many records either ad-hoc or on a schedule.
so hopefully that will help hopefully you've got some tips there as to how to do some bulk about dating in your org/
Trailmix created for this content