Adding Parameters to SQL Queries

At some point you'll want to be able to select your data using entries on a spreadsheet. That's where data parameters come in. It's a lot more complicated to explain than to actually use.

1. right click on the query data, click edit query. Add your criteria to the query, just add something like fsono = '002100'. Click the SQL button, replace '002100' with ? . this will generate a prompt that will ask for the fsono(for this example) when you refresh the data.

2. exit the query, and return to the spreadsheet. Enter the fsono that you want in a separate cell on the spreadsheet.

3. right click in the query data and click parameters. click the third option, Get the value from the following cell, and choose the cell where you put the fsono.

4. click OK, you're on your way!

Link Std Table to Fast Forms table

I can never remember how this goes. If I'm not at home, I have to find an existing report and look it up.....

m2mtable.identity_column = fastformstable.fkey_id

eg. inmast.identity_column = inmast_ext.fkey_id

Return most recent record from a table

SQL syntax - once you see this, it's sooooo obvious!

select top 1 *
from [data table]
where date <= @date
order by date desc

you can remove the where statement if you just want the latest date, if for example you're looking at the sales order backlog and your dates are in the future.

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

Back to basics -

The procedure for using SQL queries in Excel.

• Open Enterprise Manager
• right click on m2mviews, click New View
• create the query
• save it
• Open Excel
• Click Data, Import External Data, New Database query
  1. name it, something like m2mviews
  2. SQL Server
  3. click Connect, Click Options, choose m2mviews
  4. Click OK [ you only have to do this once... the data source will be there the next time. You can repeat this step for your data company if you want to use Excel to query the live data. ]
• Back to your spreadsheet.
• Click Data, Import External Data, choose the datasource you just created.
• Choose the view you've saved.
  the query wizard will appear where you can choose fields and sort and filter, etc.
  

It's pretty self explanatory from there. you can right click in the data to refresh from the query, or edit the query or change the data properties to auto refresh upon opening the spreadsheet.

SQL Date handling

These are formulas that return invoices for yesterday, Month to date, Quarter to date and Year to date. The formula for yesterday's invoices includes a calculation that excludes the weekend. The formula determines if today is Sunday or Monday [ CASE WHEN datepart(dw, getdate()) = 2 or 1 ] and if so subtracts 3 and 2 rather than just today [ getdate() ] less one.

Returns Yesterday's invoices (excluding the weekend)
WHERE (YEAR(M2MDATA01.dbo.armast.finvdate) = YEAR(GETDATE()))
AND (MONTH(M2MDATA01.dbo.armast.finvdate) = MONTH(GETDATE()))
AND (DAY(M2MDATA01.dbo.armast.finvdate) =
CASE WHEN datepart(dw, getdate()) = 2 THEN DAY(DATEADD(dd, - 3, GETDATE())) ELSE CASE WHEN datepart(dw, getdate()) = 1
THEN day(dateadd(dd, - 2, getdate())) ELSE day(dateadd(dd, - 1, getdate())) END END)

Returns MTD invoices
WHERE (YEAR(M2MDATA01.dbo.armast.finvdate) = YEAR(GETDATE())) AND (MONTH(M2MDATA01.dbo.armast.finvdate) = MONTH(GETDATE()))

Returns Quarter to Date invoices
WHERE (YEAR(M2MDATA01.dbo.armast.finvdate) = YEAR(GETDATE())) AND datepart(qq, m2mdata01.dbo.armast.finvdate) = datepart(qq, getdate())

Returns Year to Date invoices
WHERE (YEAR(M2MDATA01.dbo.armast.finvdate) = YEAR(GETDATE()))

Adding routing information to RPBKLG

The client wanted to add the scheduled finish date of the last operation to the Sales Backlog report. (jodrtg.factschdfn) This code is inserted in a copy of the RPBKLG program after the main SQL select statement.... You first start by finding the last operation on the 0000 job. Second, you'll create a cursor that stores the JO and the scheduled finish date. Third, you'll join that to the information created by the main SELECT statement and return the slbklg cursor.

The M2MSelect function will joins the foxpro cursor with data from a SQL table.

lcSQL2 = 'SELECT jodrtg.fjobno, MAX(jodrtg.foperno) as maxopno from jodrtg group by jodrtg.fjobno '

IF SQLEXEC(1, lcSQL2, "csrmaxop") <>

M2MSelect(' SELECT csrmaxop.fjobno, csrmaxop.maxopno, ' + ;
'jodrtg.factschdfn ' + ;
'FROM csrmaxop ' + ;'LEFT OUTER JOIN M2Mdata!JODrtg ' + ;
' ON csrmaxop.fjobno = jodrtg.fjobno ' + ;
' and csrmaxop.maxopno = jodrtg.foperno' + ;
' into cursor csrmaxop1',SET('DATASESSION'),,'')

SELECT slbklg1.*, csrmaxop1.factschdfn ;
from slbklg1;
LEFT OUTER JOIN csrmaxop1 ON slbklg1.fjobno = csrmaxop1.fjobno ;
INTO CURSOR slbklg