Skip to Content

Query for BOM

I need a query which displays for an spicific BOM child item

all its parent items, for example the Table ITT1 consists of following rows

child_item parent_item

-


I1 P1

I1 P2

P1 P3

P2 P4

I1

the result query should display

child_item parent_item1 parent_item2 parent_item3

-


I1 P1 P3

I1 P2 P4

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

3 Answers

  • Sep 01, 2008 at 01:50 PM

    Dear Inna,

    here there is a first quick query that you can try. Please, let me know if it is ok for you.

    SELECT T0.Father, T0.Code FROM ITT1 T0 WHERE T0.Code = 'CD-RW'

    Showing the parents horizontally

    child_item parent_item1 parent_item2 parent_item3

    it is convenient because you risk to have a long list and to have to scroll to your right to see the parent items.

    With my query you will have the list vertically and you can click on the arrow to check the production order.

    do you have a particular reason why you want the list from left to right instead of creating it from top to bottom?

    Kind Regards,

    Marcella Rivi

    SAP Business One Forums Team

    Kind Regards,

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 01, 2008 at 01:52 PM

    Hi,

    here is the query :

    
    SELECT T1.[Code] "Child_Item", 
    T1.[Father] "Parent_item1", 
    Parent_item2 = (SELECT T2.[FATHER] FROM ITT1 T2 
    WHERE (T2.FATHER = 'P3' and t2.code = t1.father)
     or (T2.FATHER = 'P4' and t2.code = t1.father))  
    FROM OITT T0  INNER JOIN ITT1 T1 
    ON T0.Code = T1.Father 
    WHERE T1.[Code]  = 'i1' 
    
    

    let me know if it is not work well. I tested in B1 2007A version and it worked.

    Rgds,

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 02, 2008 at 01:25 AM

    Hi Inna

    This query not give you exactly what you need, but can give you an idea of who to get the result you want.

    select S0.ItemCode, S0.ItemName, isnull(sum(S5.Quantity * S6.AvgPrice), isnull(sum(S2.Quantity * S3.AvgPrice), S0.AvgPrice)) 'Costo del artículo'

    from OITM S0 left join OITT S1 on S0.ItemCode = S1.Code

    left join ITT1 S2 on S1.Code = S2.Father

    left join OITM S3 on S2.Code = S3.ItemCode

    left join OITT S4 on S3.ItemCode = S4.Code

    left join ITT1 S5 on S4.Code = S5.Father

    left join OITM S6 on S5.Code = S6.ItemCode

    group by S0.ItemCode, S0.ItemName, S0.AvgPrice

    order by S0.ItemCode

    Hope this help you

    Slds.,

    Esteban

    Add comment
    10|10000 characters needed characters exceeded