FROM ((((units RIGHT JOIN (probs RIGHT JOIN ((([SELECT "1" AS ctype, rates.partnum AS LABPN, travelrates.partnum AS TRAVPN, probs.notes AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id AS probnum, probs.wolink AS wonum,  "Service: " & users.first & " " & users.last & " - " & IIf(labor.hours>0,labor.hours & " @ " & Format(rates.rate,"Currency") & " (pn:" & rates.partnum & ")","") & IIf(labor.nchours>0," (" & labor.nchours & " no charge)","") & IIf(labor.travhours>0,", " & labor.travhours & " travel @ " & Format(travelrates.rate,"Currency") & IIf(IsNull(travelrates.partnum),""," (pn:" & travelrates.partnum & ")"),"") AS item, (labor.hours*rates.rate)+(labor.travhours*travelrates.rate) AS linetotal, labor.details , labor.start AS START, labor.stop AS STOP, labor.hours AS REGHOURS, labor.travhours AS TRAVHOURS, labor.nchours as NCHOURS

FROM users INNER JOIN (rates AS travelrates INNER JOIN (rates INNER JOIN (labor INNER JOIN probs ON labor.link = probs.id) ON rates.id = labor.rate) ON travelrates.id = labor.travrate) ON users.id = labor.tech WHERE (((probs.wolink)<>0))  UNION ALL (SELECT "2" AS ctype, "na" AS LABPN, "na" AS TRAVPN, probs.notes AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id AS probnum,  probs.wolink as wonum, "Parts: " & IIf(IsNull(woparts.misc), parts.partnumber & " " & parts.description & ":  " & woparts.quantity & " @ " & Format(woparts.price,"Currency") & " ea.(sn:" & woparts.sn & ")",woparts.misc & ":  " & woparts.quantity & " @ " & Format(woparts.price,"Currency")& " ea.(sn:" & woparts.sn & ")") AS item, woparts.quantity*woparts.price AS linetotal, "" AS details, #03/12/1968# AS START, #03/12/1968# AS STOP, 0 AS REGHOURS, 0 AS TRAVHOURS, 0 as NCHOURS

FROM (probs INNER JOIN woparts ON probs.id = woparts.link)  LEFT JOIN parts ON woparts.partnum = parts.id WHERE (((probs.wolink)<>0)) UNION ALL (SELECT  "3" AS ctype,"na" AS LABPN, "na" AS TRAVPN, probs.notes AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id AS probnum, probs.wolink as wonum, "Third party service: " AS item, subrepair.charge AS linettotal, "" AS details, #03/12/1968# AS START, #03/12/1968# AS STOP, 0 AS REGHOURS, 0 AS TRAVHOURS, 0 as NCHOURS

FROM probs INNER JOIN subrepair ON probs.id = subrepair.link WHERE (((probs.wolink)<>0) AND ((subrepair.charge)<>0))))]. AS wom LEFT JOIN wo ON wom.wonum = wo.id) LEFT JOIN clients ON wo.client = clients.id) LEFT JOIN clients AS headoffices ON clients.headoffice = headoffices.id) ON probs.id = wom.probnum) ON units.id = probs.unit) LEFT JOIN unitmodels ON units.model = unitmodels.id) LEFT JOIN nonclients ON unitmodels.manufacturer = nonclients.id) LEFT JOIN projects ON wo.project = projects.id ) LEFT JOIN wotypes ON wo.type = wotypes.id WHERE (((wo.created) Between #~STRT# And #~END_#) AND ((wo.client)~CUST) AND ((wo.project)~PROJ) AND ((wo.type)~CAT_)) ORDER BY wo.id;

thank you
Carlo

A suggestion for when you want to add additional fields is to copy the SQL code from within AyaNova CE (log in as manager, select Reports, select the report that has the existing query, click on the Show SQL button -> this shows both the <Master query (in rptsmaster table)>and <Report query (after token substitution)>.

Copy the <Report query (after token substitution)> and pass into a new query in the database (in Queries). Than you can see the Design and see how it is done with other data fields as well.

I have posted below the query as edited in the AyaNova CE database Queries section : (text in bold is the new part of the query to include the status field from the wo table, which provides the ID that is than linked back to the probstat table's notes field:

SELECT " <Any date>  < All Clients >  < All Projects >  < All Categories > " AS CRITERIA, wo.id AS wonumber, "Work required: " & [probs].[brief] & IIf([probs].[unit]<>0,Chr(13) & "Unit: " & [nonclients].[company_person] & " " & [unitmodels].[description] & " (" & [unitmodels].[model] & ") SN: " & [units].[sn],"") AS probheader, "test" AS compname, wom.probnum, wom.ctype, wom.LABPN, wom.TRAVPN, wom.PROBNOTES, wom.PROBBRIEF, wom.item, wom.linetotal, wo.clientrefnum, wo.clientcontact, wo.ourref, wo.created AS wodate, wo.invoice, wo.notes, wo.prob_reported, wo.prob_found, wo.action_taken, clients.bizphone, clients.extension, clients.fax, clients.email, clients.company AS clientname, wom.details, wom.START, wom.STOP,wom.REGHOURS, wom.TRAVHOURS, wom.NCHOURS, projects.name AS PROJNAME, projects.notes AS PROJNOTES, wo.client, wo.project, wo.type, wotypes.category AS CATEGORY, probstat.notes AS probstat
FROM probstat RIGHT JOIN  (((((units RIGHT JOIN (probs RIGHT JOIN ((([SELECT "1" AS ctype, rates.partnum AS LABPN, travelrates.partnum AS TRAVPN, probs.notes AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id AS probnum, probs.wolink AS wonum,  "Service: " & users.first & " " & users.last & " - " & IIf(labor.hours>0,labor.hours & " @ " & Format(rates.rate,"Currency") & " (pn:" & rates.partnum & ")","") & IIf(labor.nchours>0," (" & labor.nchours & " no charge)","") & IIf(labor.travhours>0,", " & labor.travhours & " travel @ " & Format(travelrates.rate,"Currency") & IIf(IsNull(travelrates.partnum),""," (pn:" & travelrates.partnum & ")"),"") AS item, (labor.hours*rates.rate)+(labor.travhours*travelrates.rate) AS linetotal, labor.details , labor.start AS START, labor.stop AS STOP, labor.hours AS REGHOURS, labor.travhours AS TRAVHOURS, labor.nchours as NCHOURS FROM users INNER JOIN (rates AS travelrates INNER JOIN (rates INNER JOIN (labor INNER JOIN probs ON labor.link = probs.id) ON rates.id = labor.rate) ON travelrates.id = labor.travrate) ON users.id = labor.tech WHERE (((probs.wolink)<>0))  UNION ALL (SELECT "2" AS ctype, "na" AS LABPN, "na" AS TRAVPN, probs.notes AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id AS probnum,  probs.wolink as wonum, "Parts: " & IIf(IsNull(woparts.misc), parts.partnumber & " " & parts.description & ":  " & woparts.quantity & " @ " & Format(woparts.price,"Currency") & " ea.(sn:" & woparts.sn & ")",woparts.misc & ":  " & woparts.quantity & " @ " & Format(woparts.price,"Currency")& " ea.(sn:" & woparts.sn & ")") AS item, woparts.quantity*woparts.price AS linetotal, "" AS details, #03/12/1968# AS START, #03/12/1968# AS STOP, 0 AS REGHOURS, 0 AS TRAVHOURS, 0 as NCHOURS FROM (probs INNER JOIN woparts ON probs.id = woparts.link)  LEFT JOIN parts ON woparts.partnum = parts.id WHERE (((probs.wolink)<>0)) UNION ALL (SELECT  "3" AS ctype,"na" AS LABPN, "na" AS TRAVPN, probs.notes AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id AS probnum, probs.wolink as wonum, "Third party service: " AS item, subrepair.charge AS linettotal, "" AS details, #03/12/1968# AS START, #03/12/1968# AS STOP, 0 AS REGHOURS, 0 AS TRAVHOURS, 0 as NCHOURS FROM probs INNER JOIN subrepair ON probs.id = subrepair.link WHERE (((probs.wolink)<>0) AND ((subrepair.charge)<>0))))]. AS wom LEFT JOIN wo ON wom.wonum = wo.id) LEFT JOIN clients ON wo.client = clients.id) LEFT JOIN clients AS headoffices ON clients.headoffice = headoffices.id) ON probs.id = wom.probnum) ON units.id = probs.unit) LEFT JOIN unitmodels ON units.model = unitmodels.id) LEFT JOIN nonclients ON unitmodels.manufacturer = nonclients.id) LEFT JOIN projects ON wo.project = projects.id ) LEFT JOIN wotypes ON wo.type = wotypes.id) ON probstat.id = wo.status
WHERE (((wo.created) Between #01/01/1900 12:00:00 AM# And #01/01/2500 11:59:59 PM#) AND ((wo.client)<>-1) AND ((wo.project)<>-1) AND ((wo.type)<>-1)) ORDER BY wo.id;

Than to be able to place into the rptsmaster table for the allwo.rpt file, need to substitute tokens as Step 3 in the Customizing Reports in the AyaNova CE Manual section:

SELECT ~CRITERIA AS CRITERIA, wo.id AS wonumber, "Work required: " & [probs].[brief] & IIf([probs].[unit]<>0,Chr(13) & "Unit: " & [nonclients].[company_person] & " " & [unitmodels].[description] & " (" & [unitmodels].[model] & ") SN: " & [units].[sn],"") AS probheader, "test" AS compname, wom.probnum, wom.ctype, wom.LABPN, wom.TRAVPN, wom.PROBNOTES, wom.PROBBRIEF, wom.item, wom.linetotal, wo.clientrefnum, wo.clientcontact, wo.ourref, wo.created AS wodate, wo.invoice, wo.notes, wo.prob_reported, wo.prob_found, wo.action_taken, clients.bizphone, clients.extension, clients.fax, clients.email, clients.company AS clientname, wom.details, wom.START, wom.STOP, wom.REGHOURS, wom.TRAVHOURS, wom.NCHOURS, projects.name AS PROJNAME, projects.notes AS PROJNOTES, wo.client, wo.project, wo.type, wotypes.category AS CATEGORY, probstat.notes AS probstat
FROM probstat RIGHT JOIN (((((units RIGHT JOIN (probs RIGHT JOIN ((([SELECT "1" AS ctype, rates.partnum AS LABPN, travelrates.partnum AS TRAVPN, probs.notes AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id AS probnum, probs.wolink AS wonum,  "Service: " & users.first & " " & users.last & " - " & IIf(labor.hours>0,labor.hours & " @ " & Format(rates.rate,"Currency") & " (pn:" & rates.partnum & ")","") & IIf(labor.nchours>0," (" & labor.nchours & " no charge)","") & IIf(labor.travhours>0,", " & labor.travhours & " travel @ " & Format(travelrates.rate,"Currency") & IIf(IsNull(travelrates.partnum),""," (pn:" & travelrates.partnum & ")"),"") AS item, (labor.hours*rates.rate)+(labor.travhours*travelrates.rate) AS linetotal, labor.details , labor.start AS START, labor.stop AS STOP, labor.hours AS REGHOURS, labor.travhours AS TRAVHOURS, labor.nchours as NCHOURS FROM users INNER JOIN (rates AS travelrates INNER JOIN (rates INNER JOIN (labor INNER JOIN probs ON labor.link = probs.id) ON rates.id = labor.rate) ON travelrates.id = labor.travrate) ON users.id = labor.tech WHERE (((probs.wolink)<>0))  UNION ALL (SELECT "2" AS ctype, "na" AS LABPN, "na" AS TRAVPN, probs.notes AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id AS probnum,  probs.wolink as wonum, "Parts: " & IIf(IsNull(woparts.misc), parts.partnumber & " " & parts.description & ":  " & woparts.quantity & " @ " & Format(woparts.price,"Currency") & " ea.(sn:" & woparts.sn & ")",woparts.misc & ":  " & woparts.quantity & " @ " & Format(woparts.price,"Currency")& " ea.(sn:" & woparts.sn & ")") AS item, woparts.quantity*woparts.price AS linetotal, "" AS details, #03/12/1968# AS START, #03/12/1968# AS STOP, 0 AS REGHOURS, 0 AS TRAVHOURS, 0 as NCHOURS FROM (probs INNER JOIN woparts ON probs.id = woparts.link)  LEFT JOIN parts ON woparts.partnum = parts.id WHERE (((probs.wolink)<>0)) UNION ALL (SELECT  "3" AS ctype,"na" AS LABPN, "na" AS TRAVPN, probs.notes AS PROBNOTES, probs.brief AS PROBBRIEF, probs.id AS probnum, probs.wolink as wonum, "Third party service: " AS item, subrepair.charge AS linettotal, "" AS details, #03/12/1968# AS START, #03/12/1968# AS STOP, 0 AS REGHOURS, 0 AS TRAVHOURS, 0 as NCHOURS FROM probs INNER JOIN subrepair ON probs.id = subrepair.link WHERE (((probs.wolink)<>0) AND ((subrepair.charge)<>0))))]. AS wom LEFT JOIN wo ON wom.wonum = wo.id) LEFT JOIN clients ON wo.client = clients.id) LEFT JOIN clients AS headoffices ON clients.headoffice = headoffices.id) ON probs.id = wom.probnum) ON units.id = probs.unit) LEFT JOIN unitmodels ON units.model = unitmodels.id) LEFT JOIN nonclients ON unitmodels.manufacturer = nonclients.id) LEFT JOIN projects ON wo.project = projects.id) LEFT JOIN wotypes ON wo.type = wotypes.id) ON probstat.id = wo.status
WHERE (((wo.created) Between #~STRT# And #~END_#) AND ((wo.client)~CUST) AND ((wo.project)~PROJ) AND ((wo.type)~CAT_)) ORDER BY wo.id;

Close the database.

Log into AyaNova CE as manager, select the Workorders report from the Reports screen and generate the TTX file as per step 4

Close the AyaNova CE program

Now edit the allwo.rpt file as needed to include this new data field - don't forget that you need to update the database location (menu Database -> Set Database Location etc as also outlined in the topic http://forum.ayanova.com/Topic59-33-1.aspx steps 14 on) to select the new allwo.ttx you just created so that the report knows to also include the probstat field you just added.

- Joyce

(Note: I removed the posting in the AyaNova v3 Printing & Report Templates section of this forum as that is for version 3, whereas this is a question in regards to AyaNova CE, the older previous version of AyaNova).

AyaNova Sales & Technical Support

http://www.ayanova.com