First I am self taught writing BAQ’s using the GUI interface.
With that said I am trying to accomplish the following:
In our environment under PartLot we use the ParentLot_c as a reference to the PO the material was purchased on.
I’m writing a BAQ that reports Lots that are in stock using the DocUnitCost that the material came in on.
I am converting the ParentLot_c field to the PONum (nvarchar to integer).
Unfortunately some of the ParentLot_c fields contain alpha-numeric fields and a field that says NONE. I cannot figure out how to convert these cleanly.
Does anyone have some time to help me figure this out? Please remember I am self taught so try not to be too technical !
Try not converting the PartLot_c nvarchar value at all, rather convert the PONum from integer to nvarchar. Then you’ll be able to do your joins still and avoid the error.
Hmmm, I thought I was…so, here is the code…but when you respond could you tell me how to fix it in “GUI” language? Please?
select
[PartTran].[PartNum] as [PartTran_PartNum],
[PartTran].[LotNum] as [PartTran_LotNum],
[PartLot].[ParentLot_c] as [PartLot_ParentLot_c],
[PODetail].[PONUM] as [PODetail_PONUM],
[PODetail].[DocUnitCost] as [PODetail_DocUnitCost]
from Erp.PartTran as PartTran
inner join Erp.PartLot as PartLot on
PartTran.Company = PartLot.Company
and PartTran.PartNum = PartLot.PartNum
and PartTran.LotNum = PartLot.LotNum
inner join Erp.PODetail as PODetail on PODetail.PONUM = PartLot.ParentLot_c
PODetail.Company = PartLot.Company
and ( PODetail.PONUM = PartLot.ParentLot_c )
where (PartTran.PartNum like ‘%HST’)
group by [PartTran].[PartNum],
[PartTran].[LotNum],
[PartLot].[ParentLot_c],
[PODetail].[PONUM],
[PODetail].[DocUnitCost]
Thank you all so much…I am excited to try your suggestions, however I’m going to have to pick this up again tomorrow as I need to attend to other matters!
Well…I’m not having much luck, I’m sure it has something to do with my inexperience.
Ken, I’m not sure how to accomplish this:
You will need to create a custom calc field. But to be clear is the Calc field using the “convert” parameter?
Then use the new custom field where you are doing the join PODetail.PONUM = PartLot.ParentLot_c
I understand how to create a Calc field but I do not understand how to add it to a Join unless one of the tables are in a SubQuery?
Adam - sort of the same question, I’m not sure how to add the convert(varchar,PONum) - and I’m not sure what you mean by: change LotNum to your UD field ParentLot_c?
Please know that I appreciate your help and patience - I may have to cut out of work early today so I may not get back to this until tomorrow.
I tried to remake your query in the screenshot, but since I didn’t have your UD field ParentLot_c in my database I used LotNum instead. I meant for you to swap out where I had LotNum in the join and put your UD field ParentLot_c:
For the convert part, I just created a standard table relation. You’ll select your PartLot.ParentLoc_c field from the dropdown like normal. You also have the option to enter an expression in those text boxes (See where it says PODetail or any expression).
So for the PODetail field in that table relation, you can just paste in: convert(varchar, PONum)
Your query may be pulling in more records than you are expecting as the PODetail records could be multiple for each PONUM. Plus you are joining to the PartTrans table which will have multiple records per part. So, combined this makes the potential for duplicates amplified. Make note of this incase you are seeing results that are not making any sense. Granted I see you using group by, so the duplicates will not be shown in the results, even though the duplicates be there behind the scene making the query a bit slower.
@jpol Could I ask why you didn’t use the PartTran.PONum field and filter it by PartTran.LotNum?
When the part is received on PUR-MTL, PUR-STK or PUR-INS you would be able to tell what PO the lot was purchased on and the unit cost (based on the PO).
We have Lot Numbers that came in from our old system. The company wanted to maintain the PO Number when they adjusted the material into the system. So they entered the PO Number as the ParentLot_c.
I would have done it differently but it is what it is…
Apparently you can use the conversion in the table relationship. It just needs to have the table name too (see next post).
I learn something new every day.
And if you do have some odd relationship that can’t be solved with a simple convert(), then use the following.
end edit
You’ve been misguided by the fact that the Calc field is a result of this subquery. Therefor can’t be a field in a table relationship in its own subquery.
Create new subquery of type InnerSubquery.
Add the single table PODetail (it will warn you that it’s in use - that’s okay, it’ll add a “1” to the table name).
Add all the fields you want in displayed in your top level subquery
!
