How to filter products by unit group for reporting

(0) Share

Report

Posted on by barbieruelas

Hi everyone!

I have created 2 different product groups in our CRM's product catalogue: one for Equipment and the other for Accessories. (The identifying field is Unit Group)

I'm trying to create a report on Opportunity Products, but I only want to see unit group: Equipment. (not Accessories)

I've tried creating a report with the report wizard using Opportunities and Opportunity Products, but the Unit Group field is not in there.

Then I tried creating a view in the Opportunity Products section so I could filter the Unit Group from there but again, no Unit Group field in sight.

For the life of me, It's gotta be there, I can't be so blind. Would you please help?

All responses (10)

Answers (1)

LuHao 40,872 on at

Like (0)

Report

RE: How to filter products by unit group for reporting

Hi barbieruelas,

1. You mean, why the 2nd flow is create in the solution, right?

This is because the "When a record is created, updated or deleted" trigger is only available in flow which is in the solutions.

2. Copy and paste uomid into "Filtering attributes", does this work?

In my opinion, this should be no problem.

BTW, check if the name of Unit lookup field is uomid.

Finally, all scopes of the trigger in flow are best set to Organization.
barbieruelas 4 on at

Like (0)

Report

RE: How to filter products by unit group for reporting

My goodness gracious, YOU ARE A GENIUS and extremely talented at explaining this whole thing. I truly appreciate your patience and walk through. I can confirm both flows are now working. Just out of curiosity: why was the 2nd flow created in a "test" environment instead of my main one? The flow is working just fine they way you instructed I created it but I'd to like know why you though of it that way.

Also, in this step (below), the Filtering attributes field would not let me change it to uomid, per your original instruction. It would not let me change it, period. The way that is now, works OK,
Verified answer

LuHao 40,872 on at

Like (2)

Report

RE: How to filter products by unit group for reporting

Hi partner,

There is extra text "value" in "Apply to each", please delete it.
barbieruelas 4 on at

Like (0)

Report

RE: How to filter products by unit group for reporting

Hi! Absolutely. Here goes:
LuHao 40,872 on at

Like (0)

Report

RE: How to filter products by unit group for reporting

Hi partner,

Could you please share the screenshot of each step in the flow?
barbieruelas 4 on at

Like (0)

Report

RE: How to filter products by unit group for reporting

Hi Hao!

You are right, the relationship between Product and Opportunity Product could not be created.

So I managed to include the Flow menu in my interface and I created the first flow. I tried to run it on one Opportunity Product record and though I'd like to think I created the flow following your instructions very precisely, however I ran into an error:

When back to the flow to test it and this is the error msg:
Suggested answer

LuHao 40,872 on at

Like (0)

Report

RE: How to filter products by unit group for reporting

Hi partner,

1. Could you see if there is a Flow button in the Opportunity Line form?

I have set the "List all Opportunity Line records" step in the first flow, so no matter in which Opportunity Line you run this flow, it can be executed for all Opportunity Lines. And the first flow only needs to be run once to be closed.

2. This idea is good, but unfortunately, it is not possible to map the "Unit Group" field between "Product" and "Opportunity Product".

I have tested that when creating an Opportunity Line from Opportunity, the "Unit Group" field will not be mapped.
barbieruelas 4 on at

Like (0)

Report

RE: How to filter products by unit group for reporting

Hi!! Sorry for the delay.

First of all. THANK YOU, that was a very thorough step-by-step explanation. I do appreciate you taking the time.

I kind of got stopped at the creation of the first flow. My version of Dynamics does not have the same layout (I'm using D365 both Legacy and UI - we are transitioning right now) so I tried to create the flow using PowerApps, selected NEW and then + Automated from blank, but it will not give me the option of creating a flow "When a record is selected". The screenshots below show what I see:

This is my interface:

This is what I see in PowerApps:

This is the menu after selecting + Automated from blank

While I was reading your take on this it got me thinking: wouldn't it be easier to create a N:1 relationship between 'Product' and 'Opportunity Product' and map the 'Unit Group' fields so the information is transferred?

I did see an existing N:1 relationship between Unit Group and Opportunity Product so, I'm not sure if I this relationship would override a new one between 'Product' and 'Opportunity Product' if created.

No flows, no solution copies needed. What do you think?

Thanks again, for your time.
LuHao 40,872 on at

Like (0)

Report

RE: How to filter products by unit group for reporting

Hi partner,

Has this problem been solved?

Please click Yes under "Did this answer your question?" to close this thread.

Thanks. :)
Suggested answer

LuHao 40,872 on at

Like (0)

Report

RE: How to filter products by unit group for reporting

Hi barbieruelas,

The reason why we cannot select the Unit Group of the Unit as the filter when searching for Opportunity Line is because the relationship between Unit Group and Unit is "Do not Display", which cannot be changed.

In addition, there is no direct relationship between Opportunity Line and Unit Group, so we need to create this relationship for them first.

Solution:

New a lookup field "Unit Group" for Opportunity Line entity, select Target Record Type to Unit Group.

Then create two flows, one is to update the existing Opportunity Line records and only run once manually, the other is to update the new Opportunity Line records automatically.

1. The first flow:

In the Opportunity Line form, click Flow > Create a flow.

Then set the flow as follows.

Overview:

Detail:

The Unit Group in "Update a record" is the Unit Group in "Get record".

After saving the flow, go back to D365 Opportunity Line, refresh page, click Flow > the flow just created.

Wait for the flow to run finish. If successful, then turn off this flow.

In this way, all existing Opportunity Line's lookup field "Unit Group" should contain data.

2. The second flow:

In the power automate interface, go to Solutions, open a custom solution. If there is no custom solution, new it.

Then in the solution, new a flow.

Search for "common", select "When a record is created, updated or deleted".

Then set the flow as follows.

Overview:

Detail:

Create or update an Opportunity Line to test this flow.

In this way, the new Opportunity Line's lookup field "Unit Group" should contain data.

Finally, you are able to use the lookup field "Unit Group" in the report.