1

We are using a Scheduled Trigger Flow to update and insert some records every night. We were under the impression that the Flow would be uniquely invoked for every record that matches the condition requirements.

However, we're hitting SOQL query limits of 101 which, to us, implies that the Flow is being invoked for batches of records at a time. A single pass of the Flow makes about 5-7 SOQL queries and maybe 2-3 inserts.

Is there a way to reduce the "batch size" or some other way entirely you're supposed to implement a scheduled trigger that does need to make several SOQL queries and then inserts?

Example:

For context, consider the following feature as representative of what we're doing. There are four objects:

  • Subscription
  • Subscription Line Item
  • Invoice
  • Invoice Line Item

Every night, we run a scheduled trigger that checks to see which Subscriptions are still active and need to be renewed for another month. This involves SOQL queries against Subscription and Subscription Line Items, as well as a few related fields that are necessary to build up the Invoice.

Once the appropriate fields have been fetched, we can insert an Invoice and Invoice Line Items for the next month.

When invoked with a single Subscription Id, the Flows work fine. But when run as a Scheduled Trigger, we constantly hit SOQL query limits.

We're aware that you're not supposed to issue SOQL queries inside loops and what not, but at some point you need to "loop through all records" and query some data to perform some actions.

What is the canonical way to implement this pattern using Scheduled Trigger Flows?

Edit

I'll add that the total number of Subscriptions in the system is less than 200. And the number of Subscriptions in the system that would match the Flow's condition requirements would be less than 50%.

7
  • 3
    Is there a specific reason you went with a flow? Does your team have Apex expertise? Flows are still missing the concept of map-based variables, which is the sort of thing you need here, to create an efficient process that uses the minimum number of queries and DMLs.
    – Phil W
    Commented Mar 24 at 15:50
  • Actually, Apex would be great! But let's just say that the person or persons likely to take this Org over might not be so comfortable with programming, so there's a desire to use the highest level tools possible. The previous admin did not know Apex and the budget for the next Admin isn't much different.... /shrug
    – kennyc
    Commented Mar 24 at 17:23
  • 1
    If the code is well commented, structured and named, it should at least be readable by an admin with little knowledge. It feels to me that using flow here isn't a good fit and putting queries in loops will teach the next admin bad habits.
    – Phil W
    Commented Mar 24 at 17:48
  • 1
    I cannot answer that specific question. But going back to the point that this is going to teach bad habits and incorrect approaches, could you consider a combination of flow and apex, moving the parts that require map-like data management into invocable methods and keeping the trivial stuff in the flow?
    – Phil W
    Commented Mar 24 at 19:52
  • 1
    BTW, one of the major benefits of Apex is the ability (indeed, requirement) to create a set of tests that are repeatable and that must be satisfied before the code can be promoted or packaged.
    – Phil W
    Commented Mar 25 at 8:30

1 Answer 1

0

As mentioned in the comments, this approach is not best practise and should only be a temporary fix. I would recommend you ensure the people taking over the org know that this solution is not scalable and Apex should be considered as a long term solution.

Be that as it may, you can use a Wait element inside a scheduled flow to break the transaction up.

Salesforce help article here is quite useful.

You can put Wait elements strategically before/after SOQL queries that will reset your SOQL governor limits.

Also, have a look at this Salesforce help article which discusses the use of the IN operator in your Get Elements. It might be useful to use these in order to avoid having to use SQL queries inside your loop elements.

Good luck!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .