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!
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?
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?
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!!
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.
Already have an account? Login
No account yet? Create an account
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
Sorry, we're still checking this file's contents to make sure it's safe to download. Please try again in a few minutes.
Sorry, our virus scanner detected that this file isn't safe to download.