The last purchased cost data that is accurate is coming from PO Details and Part Trans?
Yes, supposedly, but not in this case.
I assemble the tables in this way this morning and it helped to reduce some of the duplicates:
PartOpr + Sub-query (SubShipmentD) + PODetail
In the sub-query, i used the formula maximize the PONum, it does get rid of a lot of the duplicates, but not all of them. do you know why?
thank you,
Judy
Lets do one thing at a time. What does the query look like? Does the subquery return desired results?
When do the duplicates start? Adding one table at a time will tell you when your join has issues.
Dear Judy,
I’m having trouble finding agreement between the screenshot with the three tables, and the select statement you posted. Also, I haven’t worked with the MFG-VEN transaction type before. Are these subcontract POs? If so, there may be a more effective method for getting the latest subcontract PO cost for each subcontract operations of the PartOpr (latest approved rev).
Might have to use the null compare trick twice (only the latest approved rev and only the latest PO receipt) but still should be doable.
In the past I have tried using the ‘distinct’ modifier and while I’m sure it has its place, I’ve never actually had it do what I needed.
HTH
…Monty.
Hello Monty,
I have got the two parts of the tables together and the only thing left is to get the most recent Quote related info and most recent PO related PO in that BAQ and I have tried a lot of ways.
I used the max() for Quotenum and PONum, it worked for parts with only one opcode but for a part with multiple opcodes, the opcodes are duplicating and the results are duplicating.
how could i possibly work that part out?
appreciate it your help very much.
Judy
Dear Judy,
QuoteOpr is a table that allows you to engineer a quoted part on-the-fly; in other words, to sell a manufactured item that’s either not in the part/rev/BOM/BOO database at all, or that you’ve pulled into the quote and customized. I just wanted to warn you that there may not always be a match between QuoteOpr and PartOpr fields for that reason. If you’re OK with that, would you please post the connection parameters between tables 2 and 3? In other words, which fields are you matching on, for that connection?
Thank you
…Monty.
So there is a logic behind the information, hmm…
I did connect the two tables through opcode and oprseq
What are you trying to accomplish? Those tables you have listed (QuoteOpr->PartOpr->SubShipD->PODetail), there will be join issues as they don’t match up directly. Which is why you are seeing mixed results with your query. There are common looking fields, but they are not the same.
In the end you might be presenting bad data, which is what Monty was saying.
The QuoteOpr and PartOpr are used to create the JobOper, which even then users can modify so the link may not work all the time there as well.
Are you looking for Job Material Costs? Part Costs? Last Purchased Part Costs? Material Quoted Cost?
Depending on what you are looking for you might need a few subqueries.
Dear Judy,
Yes what Ken is saying. You may get results but not the correct ones. I can show you how to limit QuoteOpr/PartOpr to a single value (first or last) even without a subquery, but the result may not be the operation that pertains to subcontract work. I see that you have a criterion on QuoteOpr. Is that supposed to limit the output to only those operations which have the subcontract POs you are researching? And would you post that criterion please? Also I would echo Ken’s question on what information you are trying to get here.
Thanks
…Monty.
Hello Ken
The goal of the BAQ is to extract out the most recent Quote info (including unit cost + min charge + quote date) and the most recent PO ( unit cost + PO) PER PART PER OP.
I finished the quote part and PO part desperately, and the two BAQ worked out; but I would like to assemble them in one BAQ.
From my past experience, I know that tables sometimes do not play well with one with the other but I did not know why.
Do you think BAQ could possibility accomplish the goal?
Thank you,
Tracy
Hello Monty,
For the subcontract ops, the opcodes will be in 200 range and the criteria is to pull those ops.
Thank you,
Judy
If the current BAQ’s are working. This could be fairly straight forward.
You might be able to create a new BAQ with two sub queries (which will be your current BAQ’s). Then have another TopLevel that will combine the two sub queries.
Can you post both of the BAQ’s? That way the group can work though this with you!!
I appreciate your help.
The PO BAQ:
The Quote BAQ
Both I ran out every quote and every PO associated with the part per op.
please let me know if you need to see any other info.
Thank you,
Judy
are you able to export the baqs and post them?
I’m with Ken. If the queries are working separately, then a subquery would be the way to go here.
Oh, they are attached.
TH-SUBPOHISTORY.baq (98.1 KB)
TH-TEST6.baq (16.5 KB)
Please let me know if you need anything else.
Thank you,
Judy
This BAQ is just the SUBPOHISTORY one. Import it. let me know if this works. The PartOpr table you were using is not correct. It should be JobOpr. There are a few cost fields from the Job that can be used, take a look to see if this is the same value from the Quote?
If so, we don’t need to combine BAQ’s!!!
TH-SUBPOJOBOPR.baq (103.3 KB)
Your Epicor Version is higher than mine; that is why i could not import it into my system…
Nothing is easy.
could you please show the tables and connections?
thank you,
Judy