Glenn,

This would be something you would want to work with a consultant on. They are trained in SQL writing and would be the best source for help. If you need resources for that I'd bet happy to recommend some. Just send your company's info to [email protected] and I'll reply back with some additonal details.

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

Typically you would use a SELECT statement to extract from another table, as long as they are related. Can you post your calculation you are using?

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

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.

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

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.

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.  

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.

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.