Duplicate lines in view

(0) Share

Report

Posted on by Community Member

Hello All,

I am trying to create a view using Sales Table, Sales Line, Invent Dim & Cust Packing Slip Trans. In this view, majority of fields are from Sales Line. The problem I have is that for Instance, a SO has 3 lines, view is duplicating 3 lines and making each line appear twice!!

I'm sure it is because of me linking the tables wrong was perhaps? I think it is to do with Invent Dim Table however, I need that table to be get InventLocation correct.

Can anyone help me find how can I link those table together?

3302.View.JPG

Thank you in advance

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Sort by

Martin Dráb 239,082 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

I see two problems.

You join SalesLine to CustPackingSlipTrans just by SalesId - you completely miss any identification of the concrete line! Also, don't forget that one line can be used in several packing slips,

Also, filtering InventDim.InventDimId by two different tables is really strange. I think your intention was joining InventDim for both tables, therefore you need two InventDim datasources, one for dimensions of SalesLine and one for CustPackingSlipTrans.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Thanks Martin, I have now fixed those 2 problems, and Now I only see single line not double which is good. However I have another problem. I have an order which has 3 lines with 2 lines being same Item Number but dispatched from different warehouse.

When I look at the view, It is only showing me 2 lines but does not show the 3rd line! The Invent Location I have taken from Invent Dim Table.

Can you help me with this?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Was this reply helpful? Yes No
Martin Dráb 239,082 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Sorry, I don't have access to your data, so I can't say what's filtered out and why. The picture also doesn't tell me (nor you) how the complete query looks like.

Looking at the query string will help you to understand what query you've generated and how it works. You can also tune the query and see what changes you must make to get the result you want. And you'll be able to share the code with us if you fail to fix it by yourself.

Therefore go to SQL Server Management Studio, find the view, right-click it and select Design. If you don't know it, each view is backed up a SQL query, which you'll see there (and you can copy it and start playing with it).

Was this reply helpful? Yes No
dynamics developer 2 on at

Like (0)

Report
Copy link

Link copied!

Are you sure all three lines are shipped? i.e there is a CustPackingSlipTrans line for all the 3 sales lines?

Was this reply helpful? Yes No