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

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

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

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

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

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?