Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Picklist Report and BI...
Microsoft Dynamics GP (Archived)

Picklist Report and BIN location

(0) ShareShare
ReportReport
Posted on by Community Member

The picklist report already has a one-to-many relationship. But then I add a link from Item Master to Item Quantity Master which has multiple lines per item. One for for each site it's assigned to. I happen to only be interested in one site MAIN.

On the report I would like the BINnumber to show for each item. But it's taking the wrong line from all sites (the one without a bin number assigned) instead of MAIN site.

Are there any good articles showing how to do proper table linking? The one provided with Report Writer doesn't go into detail on this subject.

Also, why can't you modify a link unless you're adding a new one? You can only remove or add new links, not modify them.

Here's the current structure.

01. Manufacture Order Master

02. -Picklist File*

03. --Item Master

04. ---Item Quantity Master

*This post is locked for comments

  • L Vail Profile Picture
    L Vail 65,271 on at
    Re: Picklist Report and BIN location

    Are you using Multi=bin inventory? This is where each warehouse has multiple bins available for each item.

    Leslie

  • Deepesh Profile Picture
    Deepesh 65 on at
    Re: Re: Re: Picklist Report and BIN location

     If you are using multibins the Bin number field from the item master is not used. Look for the default bins like the Issue from bin etc that are defined as the default bins.

  • Community Member Profile Picture
    Community Member on at
    Re: Re: Picklist Report and BIN location

    Thanks Richard,

     Unfortunately when I add this restriction IV_ITEM_MSTR_QTYS.Location Code = "MAIN", the resulting report shows up with no line items.

     My guess is that this happens because it's not a one-to-many relationship. This report already has a one to many relationship, and the documentation for linking tables says this:

    "You can have only one one-to-many relationship among the tables used
    for the report.    "

    Maybe I could create a SQL view where it only lists the location MAIN and link to that instead?

     Any thoughts?

     Thanks

  • Richard Whaley Profile Picture
    Richard Whaley 25,195 on at
    Re: Picklist Report and BIN location

    You need to add a restriction to show only records where Locncode='MAIN'

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,430 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans