I am working on building a BAQ in the system which will look at high level inventory totals, last trans date, aging months etc. I am also wanting to include the buyer but what happens is I get duplicate rows due to a main site and a service site for this company.
Is there anyway to make this only return one buyer without adding criteria? I am thinking this may be difficult to achieve without breaking it out by site. The reason I want this consistent is so it can be used for multi-companies (others only have one site so my current BAQ works fine).
It sounds like there is a table join in your Query Builder that needs an additional Table Relations line added to link the Site/Plant for the 2 tables. Can you share an image of your Phrase Build?
I am looking to show by part, total qty OH for that part regardless of warehouse/site, the value and last trans date along with aging months since last transactions. With that information, I was hoping to put the buyer and vendor, but in doing so I am bringing in Part Plant which will then bring in multiple rows.
I am just trying to find a creative way to only bring in one plant so this BAQ can be consistent as this will be multi-company and only one company has multiple sites. I see this may not be possible so was reaching out to confirm I guess.
I think there are a couple ways to accomplish that, but it might get a bit more complex. One possibility here is that there could be a last trans date for multiple sites. Do you just want 1 record with the last trans date, regardless of which site, or do you want the last trans date for each site?
So I am looking to just have 1 line and 1 last trans date for the company. Financially thinking for obsolete/slow moving inventory, when was the last time each part was transacted.
So yes, the first option you mentioned. The problem is service site doesn’t usually have a buyer or vendor put in, but, I don’t want to just put a criteria on it because I am trying to make it multi-company compatible for a dashboard without creating separate ones.
You could do an aggregate MAX(PartTran.TranDate), although it might not work without getting the Transaction ID and Max(TranDate) in a Subquery, and then using that. Got a meeting but I’ll play with it for a minute after.
Okay thank you, yeah I have the max trans date and everything working as it should, it’s just I’m stumped what to do because one company has multiple sites. It works great on the ones that don’t but the multi site is duplicating rows because of partplant / vendors / buyer for each plant (site).
Then in the main query, instead of connecting part directly to PartPlant and PartCost, Part is joined to the PartTran table, and PartCost / PartPlant are left-joined through that.
On the PartTran table, I added the criteria TranNum = <selected> value(s) of the <specified> subquery using ANY value and the Calculated_MaxTranNum field on the subquery.
