Flow to update a fields on Opportunity based off of Opportunity Product lines

[RugerSR762]

Let me start off my saying that I am brand new to this platform.  I need to build a flow which will ultimately set 3 values on the Opportunity form based off of the sum of Opportunity Product line items - by Product Type.

So, for example, lets say that an Opportunity has 5 product line items, each product lookup has a Type set: 

Oppty Line Item	Product Type (product table)	Amount
Network Security	SERVICE	$100
Web App Design	SERVICE	$150
Office E3	LICENSE	$10
Dynamics 365	LICENSE	$75
External SATA SSD	HARDWARE	$50

There are 3 custom fields on the Oppty:

• Service Amount
• License Amount
• Hardware Amount

The system should ultimately update (on create or update of the Opportunity Product entries), as follows:

• Service Amount: $250
• License Amount: $85
• Hardware Amount: $50

I constructed the flow below, and it is erroring at the list step: "The $filter expression must evaluate to a single boolean value."

{

  "status": 400,

  "message": "The $filter expression must evaluate to a single boolean value.",

  "error": {

    "message": "The $filter expression must evaluate to a single boolean value."

  },

  "source": {org},

  "errors": [],

  "debugInfo": "clientRequestId: 375e6d60-6150-46eb-a651-96f10093e756"

}

It is retrieving the correct Opportunity GUID.

Using PA, I cannot seem to understand how to have the system perform this calc by looking at the OLI's product type value.  Here is what I started with (attached)

[jlindstrom]

create the flow from a solution

use the connector called Common Data Service current environment by creating the flow in a solution.

Why use current environment connector? because you can do fetch XML filters, which can query across related entities (as opposed to Odata, which can only query from a single entity).

Also get the xrm toolbox (xrmtoolbox.com) and use the fetch XML builder to build your queries.

The following is the a fetch query you can use 

<fetch top="50" >
  <entity name="opportunityproduct" >
    <filter type="and" >
      <condition attribute="producttypecodename" operator="eq" value="Service" />
      <condition entityname="opportunity" attribute="opportunityid" operator="eq" value="c739a002-94a5-4a37-a1a2-d8498b07d358" />
    </filter>
    <link-entity name="product" from="productid" to="productid" />
    <link-entity name="opportunity" from="opportunityid" to="opportunityid" />
  </entity>
</fetch>

So here are the steps:

1.  create a solution\
2. add a flow
3. add trigger common data service current environment
4. Trigger: create, update, or delete of a recor
5. Trigger: create, opportunity lines entity, add the above query to the fetch XML query above
6. Add step to get record, entity: opportunities, select opportunity in get record step
7. Add list records step: entity opportunity lines
8. In list record add above fetchxml code to the fetch XML field
9. Replace the GUID in line 5 of fetch with reference to the opportunity in line 5 on the code sample above
10. Add a compose step. In the compose step add an expression to sum the value of the list records. This will give you the total of service products linked to the opportunity.
11. repeat steps 7-10 for the other two product types, replacing Service in line 4 of the XML with Hardware or License.
12. You now have three compose outputs, one for each product type
13. Update opportunity with these three values in the appropriate fields.

@RugerSR762  If this answers your question, please mark as solution

[RugerSR762]

Thanks J.  I am just not clear on a few things:

1.) Create the flow from a solution: When I click on solutions, it never loads.  The processing circle just keeps spinning.

I have tried multiple browsers/incognito, etc.  All other areas of the system seem to be running fine.  I am attempting to build the flow within the current environment though.  

2.) I am not seeing a CDS trigger which will support create, update and delete as a single trigger.  It seems you'd need to create/add three actions for each.  Is this correct?

3.) The "When a record is updated" trigger seems to be the only of the 3 events which supports filter.  Please see the attachment.

4.) In step 9 of your instruction, would the GUID need hard coded in the fetch block or would it be dynamic based off of the records currently being processed by the flow?

[RugerSR762]

@jlindstrom NVM... I signed into another environment just to test the issue with the solution.  I can now begin to follow your instructions in a different tenant.  So there appears to be a problem on the live tenant which we may need to contact MS support.  Anyways, thanks for your instructions.  I will try in this other org and let you know!

[jlindstrom]

Re solution make sure you aren’t in the default environment

[RugerSR762]

@jlindstrom I am using the active environment, not the default.  I am however, extremely confused around how to capture the margin values.  So each line will have an amount and manual discount.  I cannot simply sum extended amount as that also includes tax.  Tax must be ignored in this case.  So, First I need to figure out how to capture the difference (margin) by subtracting the manual discount value from amount, then summing each margin value from all items returned in the list.  

In an apply to each step, using the sub expression, 

sub(triggerOutputs()?['body/baseamount'],triggerOutputs()?['body/manualdiscountamount']), would this give me the difference per line then use another expression somewhere to add/sum all lines?

[jlindstrom]

See if you can create a calculated field on opportunity lines https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/calculated-rollup-attribute...

and then have power automate sum that field. I think that should work