Good afternoon, continuing my journey with flows and I am trying to solve the following (Apologies for the long post).
 
Order // Order Products // Price Bools and Inventory Monitoring.
 
The scenario is as follows:
The firm has many Products which in turn have many Product Items (serialised)
The firm has many Price Books
The firm sells Products to Accounts and wants to incorporate Inventory Monitor and also streamline - automate the process for the user.
 
How to provide automation to the users - Steps below
 
1. Created Contracts and related them to Accounts and to Price Books so that when creating an order no price book selection needed.
2. For a first level Inventory monitoring created a trigger on Product Item to roll up - COUNT - the quantity of Product Items to the related Product on a custom field - Available_Quantity__C - so that users when choosing desired products they can see how many are available.
 
The Issue I am facing now is the following, how to automatically adjust the Quantity Field on Product Items once an order is - Activated. I know that this will not solve the issue for when multiple ORDERS enter the system at the same time. For now I will leave it aside - Baby steps.
 
So I thought of my good old trusted friend the visual flow, but I ran on some issues
 
Flow designs

First Attempt
1. Fast LookUp Order Item (varTorderId passed from Process Builder)
2. Decision Element - Check if  SObjCvarProductItem is NOT empty
3. If NOT empty Loop through the SObjCvarProductItem and pass the details to SObjVarOrderItems
4. Assign the details of the SObjVarOrderItems to the SObjVarIndividualOrderItems plus some more details
5. Assign the SObjVarIndividualOrderItems TO a new SObjCvarCapturedOrderItems
 
The issue with this solution is that I cannot do an SOQL look up with flow based on a collection variable
 
Second attempt
1. Fast LookUp Order Item (varTorderId passed from Process Builder)
2. Decision Element - Check if  SObjCvarProductItem is NOT empty
3. If NOT empty Loop through the SObjCvarProductItem and pass the details to SObjVarOrderItems
4. Assign the details of the SObjVarOrderItems to the SObjVarIndividualOrderItems plus some more details
5. Decision Element - Check if SObjCvarProductItem is Empty 
   If NO - Back to loop
   If YES - Fast LookUp
where: {!SObjCvarOrderItem} is null {!$GlobalConstant.True}
 
Complete failure
 
Third attempt
 
1. Fast LookUp Order Item (varTorderId passed from Process Builder)
2. Assign element that will count the captured records
    {!varNcountSObjCvarOrderItems} equals counts {!SObjCvarOrderItem}
2. Decision Element - Check if  SObjCvarProductItem is NOT empty
3. If NOT empty Loop through the SObjCvarProductItem and pass the details to SObjVarOrderItems
4. Assign the details of the SObjVarOrderItems to the SObjVarIndividualOrderItems plus some more details.
I also included in this element the following: 
{!varNcountSObjCvarOrderItems} subtract 1
 
5. Decision Element - Check if varNcountSObjCvarOrderItems is Emptu
{!varNcountSObjCvarOrderItems} is null {!$GlobalConstant.True} 
   If NO - Back to loop
   If YES - Fast LookUp
where: {!SObjCvarOrderItem} is null {!$GlobalConstant.True}
 
Success the loop stops correctly BUT the issue now is that because my Assignment element is within the flow it only retains the last values passed from the flow.
 
I know that best practises dictate that record create / record lookup elements should not be nested within a flow
 
Any assistance on the design so that I can fast Lookup all necessary records will be much appreciated


With regards
Dimitrios