Jump to content
Changes to the Jaspersoft community edition download ×

Show kit title only on Fishbowl report


matt.stone

Recommended Posts

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?

Link to comment
Share on other sites

  • Replies 10
  • Created
  • Last Reply

Top Posters In This Topic

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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....

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...