Show kit title only on Fishbowl report

[matt.stone]

Hi,

I have a fishbowl report that lists Sales orders by SO number, showing customer name, creation date etc. and below this a table listing the items on the corresponding sales order.

The problem I have is if I add a kit of items to the SO, I only want to show the kit title on the report and not all of the items in that kit.

Is there anyone out there who could offer some guidance with this please?

[narcism]

You need to describe your input data and your desired output in order for us to be of any help.

[matt.stone]

Hi narcism,

ok, on fishbowl I have set up a kit of parts for example the kit is called PC and that kit contains the following:

1 x Custom built PC

1 x UK power lead

1 x HDMI cable

1 x 23" Monitor

1 x USB keyboard

1 x USB mouse

Now, when I add this kit to a sales order in fishbowl, I have set it to show kit title only (PC). So a typical SO might look like this:

ITEM No.                   Part Number                  Description                  Qty               Price

1                                 PC                                    Custom PC                   1                 £899.99

2                                Warranty                           3yr Extended                 1                 £65.50

So when I run my report it shows the Order number etc and the items on the SO but it lists all of the kit items (pc, monitor, leads etc) and I only want it to show the kit title (PC)....

Hope that makes sense...

Here's  the SQL:

SELECT $P!{cbGroupBy} AS GROUPBY, LOCATIONGROUP.NAME AS LG, CUSTOMER.NAME AS CUSTOMER, SO.NUM AS SO, SO.DATEISSUED, SOSTATUS.NAME AS STATUS, so.salesman AS "user",
    SOITEM.PRODUCTNUM AS PRODUCTNUM, UOM.CODE AS SOITEMUOMCODE, SOITEMSTATUS.NAME AS SOITEMSTATUS, SOITEM.DESCRIPTION, SOITEM.ID AS ITEMID,
    (SOITEM.unitprice * soitem.qtytofulfill) AS OPRICE, (SOITEM.QTYTOFULFILL) AS QTYTOFULFILL,
    ROUND(((SOITEM.QTYFULFILLED * SOITEM.UNITPRICE) + ((CASE SOITEM.QTYTOFULFILL WHEN 0
                                                                                 THEN 0
                                                                                 ELSE SOITEM.ADJUSTAMOUNT / SOITEM.QTYTOFULFILL
                                                                                 END) * SOITEM.QTYFULFILLED))*10, 1)/10 AS FPRICE,

    MIN(SOITEM.DATESCHEDULEDFULFILLMENT) AS DATESCHEDULEDFULFILLMENT, SO.datefirstship AS ITEMFULFILLMENT,
    MIN(SO.DATEISSUED) AS MINDATEISSUED, SORDER.ORDERED, SORDER.SHIPPED, POSTSO.POSTDATE, SO.DATEISSUED, CURRENCY.SYMBOL, SO.CUSTOMERPO

FROM so
    INNER JOIN locationgroup ON locationgroup.id = so.locationgroupid
    INNER JOIN customer ON customer.id = so.customerid
    INNER JOIN sostatus ON sostatus.id = so.statusid
    INNER JOIN soitem ON soitem.soid = so.id
    JOIN qbclass ON qbclass.id = so.qbclassid
    LEFT JOIN currency ON currency.homeCurrency = 1
    LEFT JOIN (SELECT postso.soid AS soid, max(postso.postdate) AS postdate
                FROM postso
                WHERE postso.postdate BETWEEN $P{dateRange1} AND $P{dateRange2}
                GROUP BY 1) AS postso ON so.id = postso.soid
    INNER JOIN uom ON uom.id = soitem.uomid
    INNER JOIN soitemstatus ON soitem.statusid = soitemstatus.id
    INNER JOIN (SELECT SUM((soitem.unitprice * soitem.qtytofulfill) + soitem.adjustamount) AS ordered, SUM(ROUND(((soitem.qtyfulfilled * soitem.unitprice) + ((CASE soitem.qtytofulfill WHEN 0
                                                                           THEN 0
                                                                           ELSE soitem.adjustamount / soitem.qtytofulfill
                                                                           END) * soitem.qtyfulfilled))*10, 1)/10) AS shipped, soitem.soid
        FROM soitem
      WHERE soitem.typeid IN (10,11,12,$P!{ckIncludeReturns},21,30,31,50,60,80)
      GROUP BY soitem.soid) sorder ON so.id = sorder.soid

WHERE soitem.typeid IN (10,11,12,$P!{ckIncludeReturns},21,30,31,50,60,80)
  AND so.locationgroupid IN ($P!{locationGroupID})
  AND so.customerid LIKE $P{customerID}
  AND soitem.qbclassid LIKE $P{qbClassID}
  AND UPPER(so.salesman) LIKE UPPER($P{salesPerson})
  AND so.statusid IN ($P{ckEstimate},$P{ckIssued},$P{ckInProgress},$P{ckFulfilled},$P{ckClosedShort},$P{ckVoided},$P{ckExpired})
  AND $P!{cbApplyDateFilter} BETWEEN $P{dateRange1} AND DATE_SUB($P{dateRange2}, INTERVAL 1 SECOND)
  AND soitem.productnum LIKE $P{productNum}

GROUP BY so.num, locationgroup.name, customer.name, so.dateissued, so.statusid, sostatus.name, so.salesman,
    soitem.productnum, uom.code, soitemstatus.name, sorder.ordered,  itemfulfillment,
    sorder.shipped, soitem.description, soitem.id, soitem.totalprice, soitem.qtytofulfill, soitem.qtyfulfilled, soitem.unitprice,
    soitem.adjustamount, postso.postdate, qbclass.name, currency.symbol

 

ORDER BY 1, locationgroup.name, (CASE WHEN $P!{ckOrderByCustomer} = 1 THEN customer.name ELSE SO.NUM END), SO.NUM

[narcism]

How do you distinguish a kit from a kit item?

[matt.stone]

It's how its created in fishbowl. Either create a Product or a Kit.... I am going to assume you don't know fishbowl..>????

[narcism]

Fishbowl is not a Jaspersoft product. However, it seems to be based on JasperReports and that is where we could help, if possible.

[matt.stone]

The product table in Fishbowl has a Kitflag field (true or False) and a Kitdisplaytype table which contains the values

 10 Show all or 20 Only show header.

Here are the schema and realtionship table map links for fishbowl:

https://www.fishbowlinventory.com/files/databasedictionary/2017/index.html

https://www.fishbowlinventory.com/files/databasedictionary/2013-2014-Database-Dictionary.jpg

[narcism]

Altering the SQL alone may not solve your issue completely. Ideally, we would need some sample JRXML files and sample data to work on.

[matt.stone]

I know it can be done as there is another report that does this but I cannot reverse engineer the code to do what I need it to.

I will send code for that tomorrow and see if you have any luck with it...

[matt.stone]

Hi narcism,

Here is the code as mentioned above...

SELECT DISTINCT soitem.id AS itemid, soitem.solineitem, soitemtype.name AS itemtype, soitem.typeid,
    soitem.customerpartnum, soitem.productnum, soitem.description, soitem.note,
    soitem.qtypicked, COALESCE(soitem.qtytofulfill,0) AS qtytofulfill, uom.code AS uomcode,
    COALESCE(soitem.adjustamount,0) AS adjustamount,
    soitemtomoitem.soitemid AS soitemtomoitemid,
    soitem.datelastfulfillment, soitem.datescheduledfulfillment, soitem.revlevel,
    soitemstatus.name AS itemstatus, soitem.showitemflag, objecttoobject.recordid2 AS kitparentline,

    /* item amounts */
    soItem.unitPrice * soItem.qtyToFulfill + COALESCE(soitem.adjustamount,0) AS totalprice,
    soitem.unitprice, soItem.totalPrice AS subTotalPrice, so.totaltax, soitem.mctotalprice,
    soitem.unitPrice * soitem.qtyToFulfill + soitem.adjustAmount AS totalPriceNotRounded,

    /* Currency */
    currency.code AS currencyCode,
    currency.symbol AS Symbol,
    COALESCE(so.currencyRate, 1) AS currencyRate,

    CASE COALESCE(UPPER(sysProperties.sysValue), 'US')
        WHEN 'US' THEN (soItem.unitPrice / COALESCE(so.currencyRate, 1) + ((COALESCE(soItem.adjustAmount, 0) /
            (CASE WHEN soItem.qtyToFulfill = 0
            then 1
            else soitem.qtytofulfill end)) / so.currencyRate)) * COALESCE(uomConversion.factor /
            (CASE WHEN uomConversion.multiply = 0 THEN 1
            ELSE uomConversion.multiply END), 1)
        WHEN 'CA' THEN  (soItem.unitPrice / COALESCE(so.currencyRate, 1) + ((COALESCE(soItem.adjustAmount, 0) /
            (CASE WHEN soItem.qtyToFulfill = 0
            then 1
            else soitem.qtytofulfill end)) / so.currencyRate)) * COALESCE(uomConversion.factor /
            (CASE WHEN uomConversion.multiply = 0 THEN 1
            ELSE uomConversion.multiply END), 1)
        WHEN 'AU' THEN soItem.unitPrice / COALESCE(so.currencyRate, 1) * COALESCE(uomConversion.factor/
            (CASE WHEN uomConversion.multiply = 0 THEN 1
            ELSE uomConversion.multiply END), 1)
        ELSE 0.0 END AS currencyUnitPrice,

    CASE COALESCE(UPPER(sysProperties.sysValue), 'US')
        WHEN 'US' THEN ROUND(((soItem.unitPrice / COALESCE(so.currencyRate, 1) *
            COALESCE(uomConversion.factor/(CASE WHEN uomConversion.multiply = 0 THEN 1
            ELSE uomConversion.multiply END), 1)) * soItem.qtyToFulfill) + (COALESCE(soItem.adjustAmount, 0) / so.currencyRate), 5)
        WHEN 'CA' THEN ROUND(((soItem.unitPrice / COALESCE(so.currencyRate, 1) *
            COALESCE(uomConversion.factor/(CASE WHEN uomConversion.multiply = 0 THEN 1
            ELSE uomConversion.multiply END), 1)) * soItem.qtyToFulfill) + (COALESCE(soItem.adjustAmount, 0) / so.currencyRate), 5)
        WHEN 'AU' THEN (soItem.unitPrice / COALESCE(so.currencyRate, 1) *
            COALESCE(uomConversion.factor/(CASE WHEN uomConversion.multiply = 0 THEN 1
            ELSE uomConversion.multiply END), 1)) * soItem.qtyToFulfill
        ELSE 0.0 END AS currencyTotalPrice,

    /* CA and AU Tax Code */
    taxRate.code AS itemTaxCode,

    CASE COALESCE(UPPER(sysProperties.sysValue), 'US')
        WHEN 'AU' THEN ROUND((soItem.taxRate * soItem.totalPrice), 2)
        WHEN 'CA' THEN soItem.taxRate * soItem.totalPrice
        ELSE 0.0 END AS soItemTaxAmount,

     soItem.taxRate * soItem.totalPrice AS NonRoundedSOItemTax, homeCurrency.symbol AS homeCurrencySymbol

FROM so
    INNER JOIN soitem ON so.id = soitem.soid
    INNER JOIN soitemtype ON soitem.typeid = soitemtype.id
    INNER JOIN soitemstatus ON soitem.statusid = soitemstatus.id
    INNER JOIN uom ON soitem.uomid = uom.id
    INNER JOIN customer ON so.customerid = customer.id
    LEFT OUTER JOIN taxRate ON soItem.taxId = taxRate.id
    LEFT OUTER JOIN objecttoobject ON soitem.id = objecttoobject.recordid1 AND objecttoobject.typeid = 30
    LEFT OUTER JOIN soitemtomoitem ON soitem.id = soitemtomoitem.soitemid
    LEFT OUTER JOIN uomConversion ON uom.id = uomConversion.fromUomId AND soitem.uomId = uomConversion.toUomId
    LEFT OUTER JOIN sysProperties ON sysProperties.sysKey = 'QBVersion'
    LEFT OUTER JOIN currency ON customer.currencyid = currency.id
    LEFT OUTER JOIN currency AS homeCurrency ON homeCurrency.homeCurrency = 1

WHERE so.id = $P{soId}
AND soitem.showitemflag = 1

ORDER BY soitem.solineitem

The one thing I think is the key here (though I could be completely wrong) is the 'objecttoobject.recordid2 AS kitparentline' statement.

This is also used in a variable on the report:

($F{KITPARENTLINE} == null ? "" : "     ") +
($F{PRODUCTNUM} == null || $F{PRODUCTNUM}.length() < 1 ?
    "" :
    ($F{CUSTOMERPARTNUM} == null || $F{CUSTOMERPARTNUM}.length() < 1 || !$P{IncludeCustomerPartNumbers} ?
        $F{PRODUCTNUM} :
        $F{PRODUCTNUM} + " (" + $F{CUSTOMERPARTNUM} + ")"))

I have tried to replicate this in my report but I get an error:

Error:com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'objecttoobject.recordid2' in 'field list'

Frustrating to say the least....

[narcism]

With such complex queries and without an environment to reproduce/test the modifications it seems almost impossible to provide a solution.

You could try and reverse engineer from the report that has what youd want and add the missing pieces.