viking79

Hello, thanks for the responses. I tried the DECODE, but it gave me an error. I have included my code with the DECODE line in it, maybe I did it wrong. Any suggestions? Thanks again Code:SELECT PO."NUM", CUSTOMFIELDVIEW."INFOFORMATTED", CUSTOMFIELDVIEW."CFTABLEID", CUSTOMFIELDVIEW."CFNAME", PO."ID" AS PO_ID, PO."VENDORCONTACT" AS PO_VENDORCONTACT, CUSTOMFIELDVIEW."CFSORTORDER" AS CUSTOMFIELDVIEW_CFSORTORDER, CUSTOMFIELDVIEW."CFID" AS CUSTOMFIELDVIEW_CFID, CUSTOMFIELDVIEW."CFDESCRIPTION" AS CUSTOMFIELDVIEW_CFDESCRIPTION, CUSTOMFIELDVIEW."CFTYPEID" AS CUSTOMFIELDVIEW_CFTYPEID, CUSTOMFIELDVIEW."CFREQUIRED" AS CUSTOMFIELDVIEW_CFREQUIRED, CUSTOMFIELDVIEW."INFO" AS CUSTOMFIELDVIEW_INFO, CUSTOMFIELDVIEW."RECORDID" AS CUSTOMFIELDVIEW_RECORDID DECODE(Customfieldview.cfsortorder,'2','APPROVED',")FROM "CUSTOMFIELDVIEW" CUSTOMFIELDVIEW INNER JOIN "PO" PO ON CUSTOMFIELDVIEW."RECORDID" = PO."ID"WHERE (((CUSTOMFIELDVIEW.CFTABLEID) = 397076832)) and Customfieldview.cfsortorder = 2 and po.num = '10010'

Hi, thanks for the response. I got it working using parameters. The goal was to have the user put in the dates that they wanted the information from.

Here is my code so far: Code:SELECT po.vendorcontact, po.vendorso, po.vendorid,customfieldview.info, /*Ship To*/ po.shiptoname, po.shiptoaddress, po.shiptocity, shipState.code AS shiptostate, po.shiptozip, shipcountry.name AS shiptoCountry, /*Bill To*/ po.remittoname, po.remitaddress, po.remitcity, billstate.code AS billtostate, po.remitzip, billcountry.name AS billtocountry, SUM(poitem.totalcost) AS totalcost, po.note, po.id AS recordid, po.totalTax AS totalTax,COALESCE(UPPER(sysProperties.sysValue), 'US') AS qbVersionFROM po INNER JOIN poitem ON po.id = poitem.poid LEFT OUTER JOIN stateconst AS shipState ON po.shiptostateid = shipState.id LEFT OUTER JOIN stateconst AS billState ON po.remitstateid = billState.id LEFT OUTER JOIN countryconst AS shipCountry ON po.shiptocountryid = shipCountry.id LEFT OUTER JOIN countryconst AS billCountry ON po.remitcountryid = billCountry.id LEFT OUTER JOIN sysProperties ON sysProperties.sysKey = 'QBVersion' AND UPPER(sysProperties.sysValue) = 'AU' LEFT OUTER JOIN customfieldview on po.id = customfieldview.recordid and customfieldview.cftableid = 397076832 and customfieldview.cfname = 'Approved Date mm/dd/yyyy'WHERE po.num = '10010'GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,18,19,20,21

I am getting the dates from a column called customtimestamp.info.

Thanks, I changed that and then I set the pattern and it worked. Now, what I would like to do is be able to filter it by the date as in search by a date range for example if I only wanted November's approved POs. I have tried a few things, but still have not got that working. Thanks again for all you help so far

Hi, thanks for the response. I'm not exactly sure how do edit the expression. Is that a parameter I need to create? Thank you

Thanks for the reponse. Actually I am pulling the date from a column in the database. The report gives a summary of all the approved Purchase Orders and I just need the date for each one, not the time. I'm trying to get it to put the dates in the report without the times. Thank you

Hello, In my report, I have it getting the date, but it also brings back the time. Is there a way to have only the date come back but without the time. Thank you

So you can't do this a parameter in ireport? Post Edited by viking79 at 01/06/2012 17:30

Hello, I am trying to get the report I'm working on to prompt the user for a date range and only pull the items that fall within the range. This is all based off the po.datecreated field. Any help is greatly appreciated, Thanks

Hello, I am writing a report that brings back approved Purchase orders. When someone writes a PO, we have a checkbox they select to indicate that it has been approved. I found the column where this information is and can sort PO's that have a 2 in that column. What I would like to do is if a 2 is in that column, I want it to come back as Approved when the report is ran. Here is the query as it is now: SELECT PO."NUM", CUSTOMFIELDVIEW."INFOFORMATTED", CUSTOMFIELDVIEW."CFTABLEID", CUSTOMFIELDVIEW."CFNAME", PO."ID" AS PO_ID, PO."VENDORCONTACT" AS PO_VENDORCONTACT, CUSTOMFIELDVIEW."CFSORTORDER" AS CUSTOMFIELDVIEW_CFSORTORDER, CUSTOMFIELDVIEW."CFID" AS CUSTOMFIELDVIEW_CFID, CUSTOMFIELDVIEW."CFDESCRIPTION" AS CUSTOMFIELDVIEW_CFDESCRIPTION, CUSTOMFIELDVIEW."CFTYPEID" AS CUSTOMFIELDVIEW_CFTYPEID, CUSTOMFIELDVIEW."CFREQUIRED" AS CUSTOMFIELDVIEW_CFREQUIRED, CUSTOMFIELDVIEW."INFO" AS CUSTOMFIELDVIEW_INFO, CUSTOMFIELDVIEW."RECORDID" AS CUSTOMFIELDVIEW_RECORDID FROM "CUSTOMFIELDVIEW" CUSTOMFIELDVIEW INNER JOIN "PO" PO ON CUSTOMFIELDVIEW."RECORDID" = PO."ID" WHERE (((CUSTOMFIELDVIEW.CFTABLEID) = 397076832)) and Customfieldview.cfsortorder = 2 and po.num = '10010' Any help would be greatly appreciated, thank you