How can I get around the "too many SOQL queries" error in my flow?

Question

All,

Not new to flow at all but can't figure out a workaround for this.

I have what I believe to be a simple flow. It queries the OpportunityLineItem object to see if a record has any OpportunityLineItemSchedules records associated with it.

This is what I'm wanting the flow to do

• Get OpportunityLineItem records with OpportunityLineItemSchedules
• If there's a related OpportunityLineItemSchedules record, then grab the MAX Schedule date record (SOQL syntax SELECT Revenue From OpportunityLineItemSchedule ORDER BY ScheduleDate DESC LIMIT 1) and store the Revenue and OpportunityLineItemId values into variables
• Loop through all of the OpportunityLineItem records and assign the Last Period Revenue value from the Revenue variable we stored in the previous step
• Lastly, update all OpportunityLineItem records in the collection with the variables specified

I'm hitting a Too Many SOQL Queries 101 error. From what I gathered, it isn't best practice to have a get records element inside of a loop. What I'm trying to wrap my head around is how to overcome this. When I put the OpportunityLineItemSchedules Get Records element outside the loop, it only grabs one record. When it's inside the loop, it grabs each record I expect it to, but errors out.

I've read I could implement a Pause element OR create another loop to get the OpportunityLineItemSchedules. I'm trying to wrap my head around how to implement either of those solutions.

Wondering if I could get some guidance.

Answer 1

The solution to this problem without using Apex would be to create another automation on the OpportunityLineItemSchedule object that, when that record is created or updated, it would update a date field on the parent opportunity line item. With this separate automation you remove the need to query for the schedule record within your loop, because you'll have this information on the item itself.

To work with this, besides creating this automation, you would have to update the existing line items too.