How to create report with record that has lookup field and subgrid?

(0) Share

Report

Posted on by USA80

Would like to get a report some how that will show me all opportunities with Food = Pizza, and see all the Restaurant Types for these opportunities within a report.
The Restaurant Types are in a subgrid on the Opportunity form. I can't seem to pull an advanced find query to make this happen.

The Food field on the Opportunity form is a lookup to the Food entity

There is a N:N relationship between Opportunity and Restaurant Types.

Any help is appreciated, thank you.

I have the same question (0)

All responses (6)

Answers (1)

Sort by

Verified answer

Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi USA80,

You can use Report Wizard to create a report which contains Food lookup field and sub-grid (N:N relationship).

Here are steps:

1.Go Settings > Customizations > Customize the system > Entities > Opportunity > N:N relationship to find the relationship between opportunity and Restaurant Types.

In Other Entity section, Display Option was set as "Do not Display" by default, you need change it, the N:N relationship can be available in Report Wizard while creating report.

2.Reports entity are not show in the sitemap, you can find it in advanced Find(Or you can add it to sitemap directly with app designer).

3.Create ‘Report Wizard’ button in new report page, then select entities.

4.Set opportunity filter criteria as your need---show all opportunities with Food = Pizza, and add fields you need from these two entities

5.Run report after setting all.

6.Test result:

Regards,

Leah Ju

Please mark as verified if the answer is helpful. Welcome to join hot discussions in Dynamics 365 Forums.

Was this reply helpful? Yes No
USA80 26 on at

Like (0)

Report
Copy link

Link copied!

I can't see the "Food" field to select at all. This field is on the Opportunity record, and is a lookup to Food Entity. Why can't I see/select this field?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi USA80,

Can you provide screenshot about it? Which step you can’t select ‘Food’ lookup field?

Only ‘Food’ lookup field can’t be selected? Other Lookup fields can be select or not?

Regards,

Leah Ju

Please mark as verified if the answer is helpful. Welcome to join hot discussions in Dynamics 365 Forums.

Was this reply helpful? Yes No
USA80 26 on at

Like (0)

Report
Copy link

Link copied!

I can't see it from the layout area of the wizard. When I click on Add column, the lookup field doesn't appear, so I can't select it. It is only this lookup field that I can't see in the drop down of fields.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi USA80,

It’s so strange, all fields of opportunity will show in the dropdown list by default.

1.Are you sure Food and opportunity is 1:N relationship and lookup field has created.

2.Make sure 'Record type' is opportunity.

3.Try to use 'Food' as record type value and select 'name' field of food entity as column.

4.Create new custom entity and build 1:N relationship with opportunity, then check it's lookup field can appear in the list or not.

Regards,

Leah Ju

Please mark as verified if the answer is helpful. Welcome to join hot discussions in Dynamics 365 Forums.

Was this reply helpful? Yes No
Suggested answer

USA80 26 on at

Like (0)

Report
Copy link

Link copied!

The field was not searchable, so I had to have searchable on the field = Yes, then I could see it.

Was this reply helpful? Yes No