I am trying to use the parent/child option of the FML report to create an Indented Bills of Material report and running into a problem.

It almost recreates the indented parent/child relationship correctly, but pulls in ALL children that exist even if the parent of that level is not related to the original parent.

For example.

This is what is happening....
Part_Num Parent_Part
0002 0001
..0003 0002
..0003 0016
..0003 0022

This is what I want to happen....
Part_Num Parent_Part
0002 0001
..0003 0002

Not sure if this makes sense, but the idea is that there are 3 records of part 0003, with a different parent related to each and webfocus is returning all the records, where I only want the one returned that has a parent of prior level that called for it.

I would think this the basic idea of how this is supposed to work, no clue why its returning this way. I will provide any more info you need to hopefully resolve this.

We are on version 7.6.2.

Thanks,
Chet

Looks like the relationship is not properly defined ( now i sound like a psychiatrist.) It appears that you are showing that part 0003 has three different parents. We would have to look at your hierarchy table and report code to be able determine the problem.

About the only FML I do is using Across, but looks as if your parent on the report may be a child in a join structure?

Its a single table I am pulling from. I have setup the table like so...

FIELDNAME=PARENT_PART_NUMBER, ALIAS=PARENT_PART_NUMBER, USAGE=A25, ACTUAL=A25V,
REFERENCE=COMPONENT_PART_NO, PROPERTY=PARENT_OF, $

FIELDNAME=COMPONENT_PART_NO, ALIAS=COMPONENT_PART_NO, USAGE=A25, ACTUAL=A25V, $

FIELDNAME=COMPONENT_PART_DESCRIPT, ALIAS=COMPONENT_PART_DESCRIPT, USAGE=A65V, ACTUAL=A65V,
REFERENCE=COMPONENT_PART_NO, PROPERTY=CAPTION, $


Report Code....

DEFINE FILE DW_BILL_OF_MATERIALS_FIN
CURRENT_TIME/HHISa=HGETC(8, 'HHISa');
ASSEMBLY_QTY_ENTERED/D20.2=&Assem_Qty;
Assem_Qty_Calced/D20.2=DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.QUANTITY_PER * ASSEMBLY_QTY_ENTERED;
END
TABLE FILE DW_BILL_OF_MATERIALS_FIN
SUM
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.ITEM_NUMBER'
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.COMPONENT_PART_NO'
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.PARENT_PART_NUMBER'
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.BUSINESS_UNIT'
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.SUBSTITUTE_PART'
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.SOURCE_CODE'
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.UNIT_OF_MEASURE'
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.DELIVER_TO_OPR_SEQ'
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.QTY_PER_TYPE'
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.Assem_Qty_Calced'
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.SCRAP_RATE'
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.EFF_START_DATE'
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.EFF_CLOSE_DATE'
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.COMPONENT_PART_NO'
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.PARENT_PART_NUMBER'
FOR
'DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.COMPONENT_PART_NO'
'&PART_NUM' GET CHILDREN ALL AS CAPTION LABEL R1
HEADING
"Indented Bills of Material"
"Parent Part No: <+0>&PART_NUM"
"Job: <+0>&Job_Num"
"Assembly Qty: <+0>&Assem_Qty"
FOOTING
""
WHERE DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.REPORT_ENTITY EQ '&REPORT_ENTITY.(FIND REPORT_ENTITY IN DW_BILL_OF_MATERIALS_FIN).REPORT_ENTITY.';
WHERE DW_BILL_OF_MATERIALS_FIN.DW_BILL_OF_MATERIALS.JOB_NUMBER EQ '&JOB_NUMBER';
ON TABLE SET PAGE-NUM OFF
ON TABLE SET FORMULTIPLE ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET BLANKINDENT ON
ON TABLE SET HTMLCSS ON
*more style code after this*

I allow them to enter the starting point of the BoM with a part_num variable. Which seems to work great, because even if I replace this with a hardcoded value it still gives the incorrect results.

As I mentioned, it does not appear that your data exists in a completely valid hierarchy (i.e. children can only have a single parent.) According to your post, part 0003 has parents 0002, 0016, and 0022.

If you do the following, what is the result?

SET BLANKINDENT=ON
TABLE FILE DW_BILL_OF_MATERIALS_FIN
SUM PARENT_PART_NUMBER
FOR COMPONENT_PART_NO
0001 WITH CHILDREN ALL
END

Well bummer. Yea a part can have multiple parents since a part, say a screw of some type, can be used in multiple assemblies builds.

Anyone know another way to handle this then?

Actually, I have figured it out using SQL instead. Thanks for all the help.

Hi Chet,

Do you mind sharing your solution? I need to drill down to the lower levels of bill of materials.

Thanks,

Hua

P.S. Is it true WebFocus can not handle a child have multiple parents?

Yes, It seems to be true from what I have researched, but you can use sql to build a report from, which is what the end result for me ended up being.

Here is an example of the sql and the hold file i put it in to use...

SET EMPTYREPORT = ON
ENGINE SQLORA SET DEFAULT_CONNECTION datawh
SQL SQLORA PREPARE SQLOUT FOR
select replace(substr(lpad(' ',2*level)||component_part_no,1,35), ' ', '&|nbsp&|nbsp&|nbsp&|nbsp') as "component_part", parent_part_number, level + 1 as "level1" , item_number, parent_part_description, deliver_to_opr_seq, component_part_descript, unit_of_measure, source_code, planning_code, qty_per_type, quantity_per * &qnty as "Quantity_Per" , substitute_part, product_code, scrap_rate_reference, report_entity, status_flag, job_number from (select distinct(component_part_no), parent_part_number, level, item_number, parent_part_description, deliver_to_opr_seq, component_part_descript, unit_of_measure, source_code, planning_code, qty_per_type, quantity_per, substitute_part, product_code, scrap_rate_reference, report_entity, status_flag, job_number from dw_bill_of_materials where job_number = '&job_num' and report_entity = '&REPORT_ENTITY' connect by parent_part_number = prior component_part_no start with parent_part_number = '&part_num') connect by prior component_part_no = parent_part_number start with parent_part_number = '&part_num' ORDER SIBLINGS BY &part_or_itemnum;
END

TABLE FILE SQLOUT
PRINT
REPORT_ENTITY
JOB_NUMBER
component_part
PARENT_PART_NUMBER
level1
ITEM_NUMBER
PARENT_PART_DESCRIPTION
DELIVER_TO_OPR_SEQ
COMPONENT_PART_DESCRIPT
UNIT_OF_MEASURE
PLANNING_CODE
SOURCE_CODE
QTY_PER_TYPE
Quantity_Per
SUBSTITUTE_PART
PRODUCT_CODE
SCRAP_RATE_REFERENCE
STATUS_FLAG
HEADING
" "
" "
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS BOM_TEMP
END

I can try to explain it to you if you need more help, but the main part of it is the
"connect by parent_part_number = prior component_part_no start with parent_part_number = '&part_num'"

and

"ORDER SIBLINGS BY &part_or_itemnum"

If I remember right I did an inner select to reduce on the parent part they enter then outer to add the "indention" and correct ordering, either by part number or there own item ordering method, which we allow them to choose, thus the amper var.

Also, I had to setup ways to handle the output. Since HTML only handle the html tab code,
COMPUTE ReplaceTab/A700V = IF '&WFFMT' EQ 'EXL2K' THEN STRREP(840, component_part, 5, '&|nbsp', 1, SP, 840, 'A150V') ELSE STRREP(840, component_part, 5, '&|nbsp', 1, ' ', 840, 'A150V'); NOPRINT

COMPUTE new_component_part/A700V = IF '&WFFMT' EQ 'HTML' OR '&WFFMT' EQ 'AHTML' THEN component_part ELSE ReplaceTab;

Sorry this is so long. =p

Thanks Chet.

I will try to translate it into my own file/fields. I am not quite familiar with SQL, but I presume that the nested select clause will drill down to all levels of the indented materials.
When I get stuck, I'm sure I'll get back to you.

Thanks again

Hua

quote:

P.S. Is it true WebFocus can not handle a child have multiple parents?

Only when using FML functionality. FML was designed to be a hierarchy where a child can only have a single parent.

If you want to go the JOIN route, a child can have multiple parents, but you have to define the relationships yourself.

Thank you for your confirmation.

It is unfortunate that FML has this limitation. We have the Bill of Materials, Financial internal/external hierachies where a child can have 2(or more) sets of grandparents.

Hua

How would that result in the report?

Can you show an example of data?
If this is in the General Ledger system, would that give the same result in two different rows?

I am more an IT person, can not explain why the accountants want to present the same information in certain ways. There might be a better way to set up our GL hierarchies.
But currently, we have multiple hierarchies in the file, one hierarchy represents the grouping for external financial reports, and internally, the same lower level accounts are re-arranged to rollup very differently than the external one. So for example, we have 4 different hierachies in the file, before FML, we will ask the users to select a hierachy, then we will extract the all the lower levels of accounts within the given hierarchy to produce the appropriate reports.
A hierachy in our file defines a subset of all the accounts in the system.
Can we archieve our needs with single hierachy?

Hua

We have more or less the same challenge...

I have set up a hierarchy database that has an extra key field for the hierarchy ID.

We have build one report and based upon the hierarchy it creates the total report.

Hint: use Views.

Pre-defined views? Our temporary solution is to use one view per hierachy. Ideally, I don't want to create new views every time a new hierarchy is added to the file, although it does not happen very often.

WHERE-based join?

We do it the same way as Frank has suggested. The extra field allows me to select only the hierarchy records that pertain to the hierarchy selected by the user. So for a given hierarchy, there is only a single parent. This also requires that the child record exist in the file for EACH hierarchy that may be used.

It it nice to hear this can be done. We have the names for the hierachies too.
I am very new to FML, there could be other reasons why our case does not work. Our consultants have sugguested using views to overcome it. I will have to write my own FML reports soon.

Hey Hua,

Not sure where you stand on this at the moment, but I will give a little more information about how we did it through sql and use smaller statements that maybe you might be able to work with. Knowing what fields you use in the tables to create the relationships would help as well. I bring this up because you mentioned a Bills of Material and the topic keeps reverting to FML, which ended up being not what we could use for this.

At the most basic level we use Component_Part_No (child part) and Parent_Part_No (parent part) in a table to show relation.

What you can do at this point is a sql statement like so:

select distinct(component_part_no), parent_part_number from dwadm1.dw_bill_of_materials connect by parent_part_number = prior component_part_no start with parent_part_number = '0001';

If you have a similar child, parent field structure like ours, this should return back only the components parts/parents that roll up into my entered "top" parent part number, '0001'.

(Sorry about the spacing on these paste...)
COMPONENT_PART_NO PARENT_PART_NUMBER
---------------- -------------------------
9336083 0001
25327-2 31251
25327 31251
25328-2 31251
25328-1 31251
25327-6 31251
12525107-1 0001
12525043 0001
12524925 0001
25327-5 31251
8597489 0001
12525045 12525043
12524976 0001
31251RM 31251
25328-3 31251
12525044 12525043
12525032-1 0001
100334-1 0001
31251 9336083
25327-1 31251
29341 31251
25327-3 31251
12526678 0001
12525016 0001
30168 31251
25327-7 31251
X12525043 12525043
25328-4 31251
25328 31251
25327-4 31251
12524882 0001

31 rows selected

Now, to prove that this is working like we think, lets use the same statement with one of the child parts from 0001, that is also a parent with children. We see from the list that part '12525043' is a child of 0001 but also will be a parent in the BoM structure.

select distinct(component_part_no), parent_part_number from dwadm1.dw_bill_of_materials connect by parent_part_number = prior component_part_no start with parent_part_number = '12525043';

COMPONENT_PART_NO PARENT_PART_NUMBER
------------------------- -------------------------
12525045 12525043
12525044 12525043
X12525043 12525043

3 rows selected


So now using the same SQL statement, I only getting back the parts that are a child of this part.

The key to all this is that at any time, one child part could have multiple lines with different parents. This sql eliminates that issue.

This is basic reduction of the parts I use in the inner select before I start creating the "indented" look with the outer select. If you try this and it works for you, I would be glad to continue walking through the rest of the process.

The end result for us on all this is that we have an Indented BoM that our users can view through all the webfocus output options and it is VERY fast, which is always a big deal with reports.

This message has been edited. Last edited by: Chet,

I forgot to add that we use zero views to help us limit/structure this before using the sql. In case you were curious.

Chet,

My feet are on two boats! I could drown myself, iterally, drown with all the information flooded in. But I truely appreciate everyone's help.

We got the Webfocus not long ago. So we have to write quite a few financial reports as the priority. And I have another project to monitor the daily cost changes in row materials or purchasing parts that affect any tractors models we make, before the cost rollup(done once a week). The more we talk about FML, the more I feel this is not applicable to the BOM. FML seems like another excel spread sheet allows you to do any row/column manipulations anywhere you want. I wish there is a stand alone command or function such as WITH ALL CHILDREN...

You already gave me a good starting point. I need to get my head straight as how to present the information, as you know for sure, each tractor has thousands of parts, some parts appears in various assembly parts...
Because I don't know WF enough, I feel like program everything in RPG, and do the final reporting in WF.

Thanks.

Hua