Adding fields to the SO report cursor

There are many occasions when you might want to export SO information to a spreadsheet. One of the frustrations with this is that only the SO item number comes through and there's no shipping info. These fields are in the SO rels table so it's relitively straight forward to add them to the report cursor. This program code adds the part number, description, shipped qty and job order number from the SORELS table to the report cursor.

This is a case where I would modify the original report program directly rather than using a copy. This is the easiest approach so to preserve the menu and button printing for sales orders. Be sure to make a backup copy of rpso.prg before starting and a backup copy of your changes after you are through. When you upgrade, rpso.prg will be overwritten with the standard version and you'll either need to do the changes again or copy your backup copy over the new version. If you really don't like making changes to the standard versions of the report you can use a copy, but it makes printing more difficult.

lcSql = "SELECT Somast.fsono, somast.fcustno, somast.fstatus, Somast.fprinted, somast.fccurid, "lcSql1 = "('SLCDPM ' + somast.fcustno + 'S' + Somast.fshptoaddr) as Syadmaskey,
"lcSql2 = "('SLCDPM ' + somast.fcustno + 'S' + sorels.fshptoaddr) as syadrelkey,
"lcSql3 = "sorels.finumber, sorels.frelease, SoRels.fshptoaddr, SoRels.flistaxabl, SoRels.flJrDif,
"lcSql4 = "SoRels.fDueDate, (sorels.fsono + sorels.finumber + sorels.frelease) as Sorelskey,
"lcSql5 = "(Soitem.fsono + Soitem.finumber) as Soitemkey, SoItem.fmultiple, soitem.fpartno, CAST(soitem.fdesc AS char(35)) AS pndesc, SoRels.fORDERQTY, SoRels.funetprice,
"lcSql6 = "SoRels.funettxnpric, SoRels.funeteuropr, Slcdpm.ftype, Somast.fterm, sorels.fcpbtype,
"lcSql7 = "pbdtl.fnamnt AS pbAmt, pbdtl.fntxnamt AS pbTxnAmt, pbdtl.fnEuroAmt AS pbEuroAmt,
"lcSql8 = "pbdtl.filmarkup, pbdtl.fimmarkup, pbdtl.fddate AS pbDate, pbdtl.fitemno AS pbItemNo, CAST(0 AS bit) AS MinPb,
"lcSql9 = "SYADDR.fccompany as Scompany, SYADDR.fmstreet as Saddress, SYADDR.fccity as Scity,
"lcSql10 = "SYADDR.fcstate as Sstate, SYADDR.fczip as Szip,
"lcSql11 = "SYADDR.fccountry as Scountry, sorels.fshipbook+sorels.fshipmake+sorels.fshipbuy AS shipped, sorels.fstatus as jobno FROM somast INNER JOIN
"lcSql12 = "sorels ON somast.fsono = sorels.fsono INNER JOIN
"lcSql13 = "soitem ON sorels.fsono = soitem.fsono AND sorels.finumber = soitem.finumber INNER JOIN
"lcSql14 = "slcdpm ON somast.fcustno = slcdpm.fcustno LEFT OUTER JOIN
"lcSql15 = "syaddr ON syaddr.fcalias = 'SLCDPM' AND syaddr.fcaddrtype = 'S' AND somast.fcustno = syaddr.fcaliaskey AND
"lcSql16 = "sorels.fshptoaddr = syaddr.fcaddrkey LEFT OUTER JOIN
"lcSql17 = "pbdtl ON pbdtl.fcdocno = sorels.fsono AND pbdtl.finumber = sorels.finumber AND
"lcSql18 = "pbdtl.frelsno = sorels.frelease
"lcSql19 = "WHERE " + FIXVFPWHERE(lcFilter) + " AND " + FIXVFPWHERE(lcSortRange) + "
"lcSql20 = "AND " + FIXVFPWHERE(lcAdvFilter) + "
"lcSql21 = "Order by " + lcSortOrder

lnReturn = SQLEXEC(lnHandle, lcSql + lcSql1 + lcSql2 + lcSql3 + lcSql4 + lcSql5 + lcSql6 + lcSql7 + ; lcSql8 + lcSql9 + lcSql10 + lcSql11 + lcSql12 + lcSql13 + lcSql14 + lcSql15 + ;
lcSql16 + lcSql17 + lcSql18 + lcSql19 + lcSql20 + lcSql21, 'csrsorels')
IF lnReturn <>
RETURN
ENDIF

SELECT fsono, fcustno, fstatus, fprinted, fccurid, UPPER(Syadmaskey) AS Syadmaskey, UPPER(syadrelkey) AS syadrelkey, ; finumber, frelease, fshptoaddr, flistaxabl, fljrdif, fduedate, UPPER(Sorelskey) AS Sorelskey, UPPER(Soitemkey) AS Soitemkey, ; fmultiple, fpartno, pndesc, fUNetPrice, fUNetTxnPric, fUnetEuroPr, (IIF(ftype = 'C', 'S', 'D') + fTerm) AS UTTermKey, ;
IIF(flistaxabl, CalcItemTax('SO', fsono, finumber, frelease, flJrDif, TTOD(fDueDate), fORDERQTY*funetprice), ROUNDCURR(0)) AS fTax, ;
IIF(flistaxabl AND oCsGenl.fMultiCurr AND NOT EMPTY(fccurid) AND fccurid <> oCsGenl.fccurid, ;
CalcItemTax('SO', fsono, finumber, frelease, flJrDif, TTOD(fDueDate), fORDERQTY*funettxnpric, fccurid), ROUNDCURR(0)) AS fTxnTax, ;
IIF(flistaxabl AND oCsGenl.fMultiCurr AND oCsGenl.flEuro AND NOT EMPTY(fccurid) AND fccurid <> oCsGenl.fccurid, ;
CalcItemTax('SO', fsono, finumber, frelease, flJrDif, TTOD(fDueDate), fORDERQTY*funeteuropr, 'EUR'), ROUNDCURR(0)) AS fEurTax, ;
fcpbtype, pbAmt, pbTxnAmt, pbEuroAmt, filmarkup, fimmarkup, pbDate, pbItemNo, ;
IIF(EMPTY(fcPbType), MinPb, MinPbItem('SO', fsono, finumber, frelease, pbitemno)) AS MinPb, ; Scompany, Saddress, Scity, Sstate, Szip, Scountry, shipped, jobno ;
FROM csrSorels ;
INTO CURSOR SoRels2