Query Builder: Restrict duplicate records when using Join

Hello Brendan,

If I remember correctly there should be a reference to the PO number inside the purchase_req_line views that you can use to get a better join condition. Try looking for fields pertaining to order_no and line_no/release_no.

This query is dumbed down so I can troubleshoot how to use queries.  I am not looking for a specific answer / work around on my query, I am looking for an understanding on how to avoid duplicate records.  I will be creating many queries and I need to understand this.  

In SQL, I would have used DISTINCT, but with this query builder I do not see how to accomplish this.  

Brendan,

My answer was how you prevent duplicate records, but I will generalize this a little. In order to eliminate the chances of duplicates you have to have a strong join condition defined. This is due to the lack of a “DISTINCT” keyword as you are not writing out a query using SQL syntax. In my experience this means that a little research has to be done in order to discover that strong link. 

I am new to IFS, so I am not sure what you mean by a Strong Link.  In my example, Requisition No = Requisition No would not be considered strong?   Do you know of an article or help document that talks about this?   

Also, is there a way, or a module in IFS where I can query using SQL syntax? I have seen in advanced searches for a single view, but that does not allow you to select data outside of that view.

I do appreciated you responding so quickly!!

Brendan,

A strong link would be using multiple shared columns between different views as part of the join condition. The more columns that you use that are shared, the more correct the join will be. I recommend doing some research on database joins and database relationships as some further reading. This will help you fill in the blanks a bit.

For SQL Syntax you can use SQL Query Tool but the above will still apply. Its better performance and usually better data if you use a correctly written join condition as opposed to relying on DISTINCT.

By a Strong Link, he means the conditions have to result in a unique join if you want only one record returned.  If every Purchase Requisition has only one line and each requisition line then is only used to create one PO line, then you would have a natural Strong Link that is one to one between Requisition Line to PO line.

However, every requisition can have multiple lines, but all with the same requisition number, thus any PO that is connected to that requisition is going to have the combination of however many requisition lines there are times the number of PO lines, each can be joined with the other mulitiple times.

If you were using the Parts version of PR/PO, then I would say requisition number and part number are the minimum you would need to make a unique join.

However, since you are using NoPart lines, the part number doesn’t exist.  The only other thing then that is truly unique is the description, but that is only true if the requisition line description is not altered on the PO line - which is possible, just depends on the purchasing process.

The only other fields you have the might be useful are Supplier ID and Price, but they are not foolproof either.

So my goal is to have no duplicate records, not keep the query down to 1 record. In my case of trying to join Purchase Requisition with Purchase Order; If I do a query with just 1 of the views based on a Req #, I will get 3 hits (3 Line items in my test case).  I than switch the query to use the other view, and do a search base on the same Req #, I again just get 3 hits.  When I combine the views and use a Join, I then get 9 hits, with three sets of duplicate records.  The resulting data Joined correctly, just duplicated.  

So after joining, I want to see just the 3 hits for the 3 line items in the Req.  I do not know how to make a stronger link then Req number in this case.  I tried adding other joining rules; Site, Company, etc… with my result being the same every time.  

Is this not possible in the Query Builder Module?    

Brendan,

Below is an example of what I mean by do a correct join…in sql syntax. There are order_no and line no columns in purchase req line nopart views to give yourself a better join. These tie directly to the purchase order order no and line no and come about from when the req is converted to a PO. 

select prl.ORDER_NO, prl.ASSG_LINE_NO, pol.ORDER_NO, pol.LINE_NO from 
purchase_req_line_nopart prl
join purchase_order_line_nopart pol on prl.REQUISITION_NO = pol.REQUISITION_NO
and prl.ORDER_NO = pol.ORDER_NO and prl.ASSG_LINE_NO = pol.LINE_NO

So I was ability to get what I want from The SQL Query Tool, so thank you for that advice.

To close this topic, can someone please suggest a strong link for joining the views of Purchase Order Link Nopart and Purchase Requis Line Nopart?  

Thank you! 

Adding Line_No was all I needed to remove duplicates in this query.  I will have to work on my query building skills!