ISTech Support Forum - Associate record with BOM Component, not Parent

David Waldmann

Active Member

Live to work, or
work to live?

Posts: 1924
Gender: male

Associate record with BOM Component, not Parent
09/01/09 at 05:06:31

I am trying to write a report on completed WOs. I have grouped by WO Pre/Suffix, and want to calculate the cost of the actual issued components at the current Average Cost. However, when I put the Average Cost field in the detail section, it is pulling the Average Cost of the Parent, not the Component. How do I specify which one it is looking at?

I am using the following tables:
WORKORD
WOMAT
BKISMSTR

With the following links:
WORKORD.MTWO_WIP_CODE-->BKISMSTR.BKIC_PROD_CODE
WORKORD.MTWO_WIP_WOPRE-->WOMAT.WOMAT_WOPRE
WORKORD.MTWO_WIP_WOSUF-->WOMAT.WOMAT_WOSUF

I've tried adding WOMAT.WOMAT_PCODE-->BKISMSTR.BKIC_PROD_CODE (and vice versa) but then I get nothing in the report.

Back to top

David N Waldmann
President
Vermont Hardwoods
Chester, VT

Evo-ERP, 5 user
IST Build: 3/4/19, patched 04/30/19
Pervasive v11.31
Server 2012 / Win10 x64
Crystal Reports v11

IP Logged

GasGiant

Administrator

How can I help?

Posts: 1523
Gender: male

Re: Associate record with BOM Component, not Paren
Reply #1 - 09/01/09 at 06:09:59

I assume you are purposely not using the costs in WOMAT.

For what you are seeking, I'm not sure why you need WORKORD at all. You do need to connect womat_pcode to bkic_prod_code in order to get the average cost of the component, but you do not need any connection with the parent.

I'm unsure what you are trying to report on exactly, or I could be more specific.

Back to top

IP Logged

David Waldmann

Active Member

Live to work, or
work to live?

Posts: 1924
Gender: male

Re: Associate record with BOM Component, not Paren
Reply #2 - 09/01/09 at 06:19:03

I am trying to calculate what it would cost to make the same item today, using the same Quantity it took at the time it was made at some point in the past.

The reason is that our material usage varies according to yield. We have changed our process and want to try and figure out the net effect. We know our labor cost has gone up because the new process is more labor intensive. We also know our material usage has gone down. However, we are having a hard time getting a handle on whether it is actually worth it. Especially since material costs have changed significantly (in some cases by 30%), I can't look at the actual costs on the WO.

So I want to take the Quantity Issued (of component) x Current Average Cost / Qty Completed (of parent) and compare those over time.

Back to top

David N Waldmann
President
Vermont Hardwoods
Chester, VT

Evo-ERP, 5 user
IST Build: 3/4/19, patched 04/30/19
Pervasive v11.31
Server 2012 / Win10 x64
Crystal Reports v11

IP Logged

GasGiant

Administrator

How can I help?

Posts: 1523
Gender: male

Re: Associate record with BOM Component, not Paren
Reply #3 - 09/01/09 at 07:13:06

Then yes, only join codes bkic_prod_code to womat_pcode, plus the wopre and wosuf joins you have.

Something like

Code:

SELECT WORKORD.MTWO_WIP_CODE, WOMAT.WOMAT_WOPRE, WOMAT.WOMAT_WOSUF, 
       WOMAT.WOMAT_PCODE, WOMAT.WOMAT_QTYISSUED, WOMAT.WOMAT_COST, 
       BKICMSTR.BKIC_PROD_AVGC
    FROM BKICMSTR BKICMSTR, WOMAT WOMAT, WORKORD WORKORD
    WHERE WOMAT.WOMAT_PCODE = BKICMSTR.BKIC_PROD_CODE 
        AND WORKORD.MTWO_WIP_WOPRE = WOMAT.WOMAT_WOPRE 
        AND WORKORD.MTWO_WIP_WOSUF = WOMAT.WOMAT_WOSUF 
        AND WORKORD.MTWO_WIP_WOPRE=50505

I tried this and it gave me three lines with all of the data needed to do the calculations. It might be quicker to use Excel for reports like this than CR, unless you really need pretty reports.

Back to top

IP Logged

David Waldmann Active Member Live to work, or work to live? Posts: 1924 Gender:	Re: Associate record with BOM Component, not Paren Reply #4 - 09/01/09 at 07:17:41 Colin - thanks. I'll give that a try. And I guess I should try using Excel. It's just that I've only started using it instead of Quattro Pro so I am not very familiar with it, and not at all with the database connectivity.
Back to top	David N Waldmann President Vermont Hardwoods Chester, VT Evo-ERP, 5 user IST Build: 3/4/19, patched 04/30/19 Pervasive v11.31 Server 2012 / Win10 x64 Crystal Reports v11 IP Logged

GasGiant Administrator How can I help? Posts: 1523 Gender:	Re: Associate record with BOM Component, not Paren Reply #5 - 09/01/09 at 07:33:06 Then stick with what works for you. I don't bother much with CR because I am more comfortable with other things, including Excel for speed and number crunching or PHP for flexibility and accessibility across the organization. Personal preference
Back to top	IP Logged

kevind Active Member I was more than willing to "pony up", how b'out U? Posts: 639 Gender:	Re: Associate record with BOM Component, not Paren Reply #6 - 09/01/09 at 08:49:59 Here's a CR I threw together this morning that I think has the data and Grouping that you want. This report uses the Work Order History Files.
Back to top	« Last Edit: 09/01/09 at 11:26:17 by kevind » Kevin Damke Spectronics Corporation ISTECH 2004.1 7/26/13 SP1 - 20 user Evo-ERP Build 7/25/13 T7 -7i R6 - 3 user (Prev version was 2004.1 10/24/12) (All Patches Installed as of 7/26/13) Pervasive 2000i SP4 - Crystal Reports V10 IP Logged

David Waldmann Active Member Live to work, or work to live? Posts: 1924 Gender:	Re: Associate record with BOM Component, not Paren Reply #7 - 09/01/09 at 09:38:09 Kevin, I am unable to download the file. Is it in a public location? thanks! -d
Back to top	David N Waldmann President Vermont Hardwoods Chester, VT Evo-ERP, 5 user IST Build: 3/4/19, patched 04/30/19 Pervasive v11.31 Server 2012 / Win10 x64 Crystal Reports v11 IP Logged

kevind Active Member I was more than willing to "pony up", how b'out U? Posts: 639 Gender:	Re: Associate record with BOM Component, not Paren Reply #8 - 09/01/09 at 11:27:11 Oops. Our 'PCI' Compliance Scan forced us to close that link I moved the files and corrected the post above.
Back to top	Kevin Damke Spectronics Corporation ISTECH 2004.1 7/26/13 SP1 - 20 user Evo-ERP Build 7/25/13 T7 -7i R6 - 3 user (Prev version was 2004.1 10/24/12) (All Patches Installed as of 7/26/13) Pervasive 2000i SP4 - Crystal Reports V10 IP Logged

David Waldmann

Active Member

Live to work, or
work to live?

Posts: 1924
Gender: male

Re: Associate record with BOM Component, not Paren
Reply #9 - 09/01/09 at 12:33:48

Thanks, Kevin. I can get it now.

By looking at how the tables and links are set up it looks like you added BKICMSTR twice, once linked to WORKHORD and once to WOHMAT?

And then I see you have three Groups - is it that third level (group by WOHMAT.WOMAT.PCODE) that tells it to get the cost for .PCODE instead of .PRODCODE?

I haven't re-written it for everything I need but it looks like it will do the job. I'd just like to have a better understanding so (hopefully!) I can do it myself next time.

Always learning...

Back to top

David N Waldmann
President
Vermont Hardwoods
Chester, VT

Evo-ERP, 5 user
IST Build: 3/4/19, patched 04/30/19
Pervasive v11.31
Server 2012 / Win10 x64
Crystal Reports v11

IP Logged

kevind

Active Member

I was more than
willing to
"pony up",
how b'out U?

Posts: 639
Gender: male

Re: Associate record with BOM Component, not Paren
Reply #10 - 09/01/09 at 14:56:26

With Linking, you can link a given table as many times as you need too. All that is required is that you give each instance a different Alias. You can link a table to itself if you needed too (like in an exploded BOM).

The reason I have it linked in twice is because in one of my iterations, I was showing the Average Cost of the parent item for comparison with the calculated cost of the material. Later, I decided that was of no use because the parent average includes labor. I just forgot to remove the link. So, all the fields in BKICMSTR.xxx are the Item information for the WO PARENT item.

WOHMAT.PRODCODE is the product code of the PARENT item. WOHMAT.PCODE is the product code of the item being issued. You link WOHMAT.PCODE to BKICMSTR_1.PCODE so that you can get the average cost of the item issued.

Also Always learning ...

Back to top

Kevin Damke
Spectronics Corporation
ISTECH 2004.1 7/26/13 SP1 - 20 user
Evo-ERP Build 7/25/13 T7 -7i R6 - 3 user
(Prev version was 2004.1 10/24/12)
(All Patches Installed as of 7/26/13)
Pervasive 2000i SP4 - Crystal Reports V10

IP Logged

David Waldmann Active Member Live to work, or work to live? Posts: 1924 Gender:	Re: Associate record with BOM Component, not Paren Reply #11 - 09/03/09 at 04:25:33 Just wanted to let you guys know I had success. I've said it before and I'll say it again: without this forum DBA would have died long ago.
Back to top	David N Waldmann President Vermont Hardwoods Chester, VT Evo-ERP, 5 user IST Build: 3/4/19, patched 04/30/19 Pervasive v11.31 Server 2012 / Win10 x64 Crystal Reports v11 IP Logged