Multiple ways to run Flows for multiple records

Owned by Jodie Miners
Last updated: Jan 24, 2020
7 min read

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.

You want

  • 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

The Imaginary Scenario

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.

The Spreadsheet

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.

Here is the spreadsheet.

The Calculations

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).

The Setup

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.

Bulk Updates

Standard Functionality

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

Method 1 - Scheduled Flows

Introduced in Winter '20

  • Basically just initiates a Batch Apex Job

  • Help Notes

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

Method 2 - Enabler4Excel (XL-Connector)

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.

Method 3 - Mass Action Scheduler

Mass Action Scheduler. A community supported app, By Doug Ayers

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

Other Methods

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). https://tddprojects.atlassian.net/wiki/spaces/SF/pages/501579779 - 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.

Conclusion

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/

Resources

 

Â