Tables linking Sales Order Number and Customer Number

Question

The SO_SalesOrderDetail has just about every field I need except the customer number. What other table can I use to link them and report this field in Sage Intelligence 100? I am using Microsoft Query.

David Speck · Accepted Answer

If you are unfamiliar with writing SQL statements manually then one of the easiest way to build a SQL statement is to let software like Crystal Reports build it for you after adding the tables, linking the tables,entering record selections, and adding fields to the report. Add tables. Define links. Add record selections. Add fields to report. Refresh report to display data (required to build SQL statement). View SQL statement. Copy SQL statement. On a side note, from what i have learned, the ProvideX ODBC driver for Sage 100 is most similar to the SQL-92 standard. There are some scalar functions implemented as well as some regular functions like SUM and COUNT. Table joins however are a little weird, for a simple INNER JOIN, you can use the WHERE clause to define the common columns as seen in the last screenshot above or you can define the INNER JOIN or LEFT OUTER JOIN using another method used in the FROM clause. Here are examples for both SQL statements. Using WHERE clause. SELECT "SO_SalesOrderHeader"."SalesOrderNo", "SO_SalesOrderHeader"."OrderDate", "SO_SalesOrderDetail"."ItemCode", "SO_SalesOrderDetail"."ItemCodeDesc", "SO_SalesOrderDetail"."QuantityOrdered", "SO_SalesOrderHeader"."OrderType" FROM "SO_SalesOrderHeader" "SO_SalesOrderHeader", "SO_SalesOrderDetail" "SO_SalesOrderDetail" WHERE ("SO_SalesOrderHeader"."SalesOrderNo"="SO_SalesOrderDetail"."SalesOrderNo") AND "SO_SalesOrderHeader"."OrderType"<>'Q' Using FROM clause. SELECT "SO_SalesOrderHeader"."SalesOrderNo", "SO_SalesOrderHeader"."OrderDate", "SO_SalesOrderDetail"."ItemCode", "SO_SalesOrderDetail"."ItemCodeDesc", "SO_SalesOrderDetail"."QuantityOrdered", "SO_SalesOrderHeader"."OrderType" FROM {oj "SO_SalesOrderHeader" "SO_SalesOrderHeader" INNER JOIN "SO_SalesOrderDetail" "SO_SalesOrderDetail" ON ("SO_SalesOrderHeader"."SalesOrderNo"="SO_SalesOrderDetail"."SalesOrderNo")} WHERE "SO_SalesOrderHeader"."OrderType"<>'Q'

Tyler Christensen · Answer

Link SO_SalesOrderHeader, this has the customer number in it.

David Speck · Answer

If you have both SO_SalesOrderHeader and SO_SalesOrderDetail, you need to link them with an INNER JOIN on SalesOrderNo. 
 Since SO_SalesOrderDetail contains the line data, you will have the customer number repeated for every line on the sales order. 
 I'm not sure what your end goal is but to only have one row per order and customer, you would need to remove the SO_SalesOrderDetail table from the query.

Sage 100

Tables linking Sales Order Number and Customer Number

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING