Use of SQL in Sage 100 Contractor Report Writer

Question

Can anyone tell me how to create a calculated field that has a SQL statement bringing in data from an unrelated table? I've looked at fields on other reports that I think are doing approximately the same thing but when I modify the SQL statement I keep getting errors. I have yet to find training that addresses the SQL syntax for Sage 100 C and gives examples.

Char DeLange · Answer

It's called inner join, try looking it up that way.

ConnieLynn · Answer

[Select Sum(JobCst.CstAmt)from JobCst Where JobCst.Status =1 And JobCst.JobNum = {ActRec.RecNum}] - [Select Sum( acpinv.invbal)FROM Acpinv WHERE acpinv.status < 5 and acpinv.jobnum = {actrec.recnum}] 
 Here is an example of the Select Sum - From - Where statement. Hope it helps.

Sage Construction & Real Estate

Use of SQL in Sage 100 Contractor Report Writer

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING