Use of SQL in Sage 100 Contractor Report Writer

SUGGESTED

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.

Parents
  • 0
    SUGGESTED

    [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.

  • 0 in reply to ConnieLynn

    Is it possible to embed the Select statements if they are accessing different tables?  

    For example, in the post above, instead of "={actrec.recnum}" at the end of the statement I would like to use the Select command to get a value from yet another table.  I tried each Select command separately and they work, but if I try to embed one within the other, then I get an error.

  • 0 in reply to smaris

    I think you have to keep the {actrec.recnum} because it is the one thing that ties the AR invoices and AP invoices together.  If you still need additional info, it would be and AND [select x].  How about telling us exactly what info you are trying to extract

  • 0 in reply to Char DeLange

    I'm trying to pull the grid lines of parts info from a PO/invoice/inventory allocation, and list it on an invoice.  Essentially recreating the report 3-10-3-61.   Our clients need to see the itemized list of parts, not just a giant job cost.

    So I know that from the job cost line I can use the Link and Source to connect to the original transaction (either a PO, A/P invoice, or Inventory Allocation).  

    [Select invalc.recnum  FROM invalc WHERE invalc.lgrrec = {jobcst.lgrrec}]

    Now I need to pull the lines from the invtln table.  Though since the invtln table does not have a "Link", then I need to connect the invtln.recnum with the invalc.recnum that I just returned.

    [Select invtln.prtnum  FROM invtln WHERE invtln.recnum = {invalc.recnum}]

    I need that first select statement to replace the {invalc.recnum} in the above statement.  

    Does that make sense?  Then I need to redo that with the A/P and Inventory Allocation, and then embed again in a  IF statement based on the Source.

  • 0 in reply to smaris

    Have you tried an IF THEN statement?  

    IF it is a an invoice then select  the info from the AP invoice allocation otherwise select the info from the allocation.  

  • 0 in reply to Char DeLange

    I will try the IF statement after I get the lines from the PO.  I can't get the following statement to even get the grid lines from the PO yet:

    [Select invtln.prtnum  FROM invtln WHERE invtln.recnum = {invalc.recnum}]

    I get this error, and Sage quits and closes.

    So I try to embed the first Select Statement b/c I need to first find the invalc.recnum

    [Select invtln.prtnum  FROM invtln WHERE invtln.recnum = {[Select invalc.recnum  FROM invalc WHERE invalc.lgrrec = {jobcst.lgrrec]}]

    but it won't let me save - says there is a SQL error.

  • 0 in reply to smaris

    So I obviously have the SQL syntax wrong, but I have searched for hours and can't find any help on the syntax.  Some sites say you can embed multiple Select statements (for other programs), but I can't find any help on the specific rules for Sage 100.

Reply Children
No Data