The GUMU™ for Sage CRM – Sage 100 Integration empowers businesses with real-time, bi-directional connectivity between Sage CRM and Sage 100 ERP. Among its many powerful tools, the Query Grid Manager stands out as a key feature that enables users to view ERP data directly within Sage CRM — without writing a single line of code.
This article walks you through the setup and configuration of the Query Grid Manager, demonstrating how to create a dynamic AR Invoice grid that displays invoice data from Sage 100 inside Sage CRM.
Overview of Query Grid Manager
The Query Grid Manager allows Sage CRM administrators to create, manage, and display custom data grids that fetch and display Sage 100 ERP information in real-time. The Query Grid Manager can be accessed under Administration → Sage 100 Administration → Query Grid Manager in Sage CRM.
Key Features
- Create custom grids in Sage CRM, using GUMU™ integration, to display real-time/synced data from Sage 100.
- Display ERP data in grid format within Sage CRM entities.
- Configure column visibility, sorting, and filtering.
- Gain real-time ERP visibility without programming or coding.
Prerequisites
Before setting up the Query Grid Manager, ensure the following requirements are met:
- Sage CRM Administrator Rights – Administrative access is necessary to configure and manage the Query Grid Manager.
- GUMU™ Integration Installed – Verify that the latest version of the GUMU™ Sage CRM – Sage 100 integration is installed and configured.
- Data Connectivity Setup – Sage CRM has a SQL backend, while Sage 100 may use a different backend. A connection (such as a linked server, ODBC) must be configured to enable data synchronization between Sage 100 and Sage CRM.
- Basic SQL Knowledge – Familiarity with SQL, particularly
OPENQUERY, is recommended for creating synchronization queries and managing CRM-side tables.
Let’s walk through an example of how to create a custom grid in Sage CRM using the Query Grid Manager to display AR invoices for a specific customer.
Configuration Steps Overview
- Create an SQL table in the Sage CRM database to store AR Invoice data.
- Define the grid query in the Query Grid Manager.
- Configure grid metadata for labels and formatting.
- Sync Sage 100 data into CRM for display.
Step 1: Create GTInvoice Table in Sage CRM Database
Run the following SQL script in the Sage CRM database to create a table for storing Sage 100 AR Invoice data:
Query:
CREATE TABLE GTInvoice (
GCompanyCode VARCHAR(50),
GCustomerNumber VARCHAR(50),
GInvoiceType VARCHAR(20),
GInvoiceNo VARCHAR(50),
GARDivisionNo VARCHAR(10),
GCUSTOMERNO VARCHAR(50),
GBillToCUSTOMERNO VARCHAR(50),
GBillToDivisionNo VARCHAR(10),
GInvoiceDueDate DATE,
GInvoiceDate DATE,
GCustomerPoNo VARCHAR(50),
GSalesOrderNo VARCHAR(50),
GShipDate DATE,
GSalespersonDivisionNo VARCHAR(10),
GSalespersonNo VARCHAR(50),
GInvoiceAmt NUMERIC(18,2),
GDiscountAmt NUMERIC(18,2),
GBalance NUMERIC(18,2),
HeaderSeqNo VARCHAR(50),
ModuleCode VARCHAR(50)
);
Step 2: Define Grid Query in Query Grid Manager
- Navigate to Administration → Sage 100 Administration → Dynamic Grid Query Manager.
- Create a new record with Query Option = Show Data.
- Set Caption = GTInvoice Grid.
- Enter the query to fetch and display data from GTInvoice table.
Query:
SELECT
CASE
WHEN ISNULL(GInvoiceType,”) IN (‘FC’,’PP’,’PY’,’BC’,’BF’) THEN ‘#’
ELSE ‘&Tran=’ + ISNULL(GInvoiceType,”)
+ ‘&TranId=’ + ISNULL(GInvoiceNo,”)
+ ‘&CustomerNumber=’ + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ISNULL(GARDivisionNo,”) + ‘-‘ + ISNULL(GCustomerNo,”), ‘%’, ‘%25’), ‘ ‘, ‘%20′),””,’%27′),’&’, ‘%26′),’$’,’%24′),’#’,’%23′)
+ ‘&InvoiceType=’ + ISNULL(GInvoiceType,”)
+ ‘&HeaderSeqNo=’ + ISNULL(HeaderSeqNo,”)
+ ‘&sBillToCustomerNo=’ + ISNULL(GBillToCustomerNo,”)
+ ‘&BillToDivisionNo=’ + ISNULL(GBillToDivisionNo,”)
END AS Details,
GInvoiceNo,
CAST(GInvoiceDate AS DATE) AS InvoiceDate,
CAST(GInvoiceDueDate AS DATE) AS InvoiceDueDate,
GCustomerPoNo,
GSalesOrderNo,
CASE WHEN GShipDate IN (‘1753-01-01’, ‘1900-01-01’) THEN NULL ELSE CAST(GShipDate AS DATE) END AS GShipDate,
GSalespersonDivisionNo + ‘-‘ + GSalespersonNo AS Salesperson,
GInvoiceAmt,
GDiscountAmt,
GBalance,
CASE
WHEN GBalance = 0.00 THEN ‘Fully Paid’
WHEN GBalance IS NOT NULL THEN ‘Open’
ELSE ‘History’
END AS sType
FROM GTInvoice
WHERE GARDivisionNo = ‘^^’
AND GCustomerNo = ‘~~’;
This query retrieves Sage 100 invoice data and formats it for display inside CRM.
Step 3: Configure Grid Metadata
Open the CustomgridConfig table in the Sage CRM SQL database (Right click on CustomGridConfig table>Edit Top 200 Rows) and add metadata configuration entries for the GTInvoice grid, such as field names, display titles, formatting, and link templates. This ensures the grid columns appear correctly within CRM and hyperlinks function properly.
| Column | Value |
| cugc_fieldnames | Details,GInvoiceNo,InvoiceDate,InvoiceDueDate,GCustomerPoNo,GSalesOrderNo,GShipDate,Salesperson,GInvoiceAmt,GDiscountAmt,GBalance,sType |
| cugc_titleNames | View Details,Invoice No.,Invoice Date,Invoice Due Date,Customer Po,Sales Order No.,Ship Date,Salesperson,Amt,Discount,Balance,Type |
| cugc_templeteName | <a href=\'"+DetailPageURL+"#=Details#\'><B>View</B></a>,,,,,,,,,,, |
| cugc_formatNames | ,,d,d,,,d,,{0:C2},{0:C2},{0:C2}, |
| cugc_DtlpageMethodName | RunARDetailsKendo |
| cugc_jsfunname | GetCustomSQLStaticGrid |
Note:
- d represents a date format.
- {0:C2} represents a currency format.
Step 4: Sync ERP Data into CRM Table
Before displaying the grid, ERP data from Sage 100 must be synchronized into CRM using linked server queries.
- Truncate Old Data
- Query Option: Sync Data
- Caption: TruncateGTInv
- Query: IF EXISTS (SELECT TOP(1) * FROM GTInvoice WHERE GCompanyCode = ‘MAS_ABC’) DELETE FROM GTInvoice WHERE GCompanyCode = ‘MAS_ABC’;
- Fetch Data from Sage 100
- Query Option: Sync Data
- Caption: GTInvoice
- Queries:
- IF OBJECT_ID(‘tempdb..#InvoiceHeader’) IS NOT NULL DROP TABLE #InvoiceHeader
IF OBJECT_ID(‘tempdb..#OpenInvoice’) IS NOT NULL DROP TABLE #OpenInvoice –Drop temporary tables if they exist
SELECT * INTO #OpenInvoice FROM OPENQUERY(MAS_ABC, ‘ SELECT InvoiceNo, InvoiceHistoryHeaderSeqNo, Balance FROM AR_OpenInvoice ‘) –Pull data from AR_OpenInvoice
INSERT INTO GTInvoice ( GCompanycode, GInvoiceNo, GARDivisionNo, GCUSTOMERNO, GBillToCUSTOMERNO, GBillToDivisionNo, GInvoiceType, GInvoiceDate, GInvoiceDueDate, GShipDate, GSalesOrderNo, GCustomerPoNo, GSalespersonNo, GSalespersonDivisionNo, GInvoiceAmt, GDiscountAmt, GBalance, HeaderSeqNo ) SELECT ‘MAS_ABC’ AS GCompanycode, H.InvoiceNo, H.ARDivisionNo, H.CUSTOMERNO, H.BillToCustomerNo, H.BillToDivisionNo, H.InvoiceType, H.InvoiceDate, H.InvoiceDueDate, H.ShipDate, H.SalesOrderNo, H.CustomerPoNo, H.SalespersonNo, H.SalespersonDivisionNo, (H.TaxableSalesAmt + H.NonTaxableSalesAMt + H.FreightAmt + H.SalesTaxAmt) AS GInvoiceAmt, H.DiscountAmt, O.Balance, H.HeaderSeqNo FROM #InvoiceHeader H LEFT JOIN #OpenInvoice O ON H.InvoiceNo = O.InvoiceNo AND H.HeaderSeqNo = O.InvoiceHistoryHeaderSeqNo –Merge into GTInvoice table
- IF OBJECT_ID(‘tempdb..#InvoiceHeader’) IS NOT NULL DROP TABLE #InvoiceHeader
Testing the Grid in Sage CRM
- Open any Company record linked to Sage 100.
- Navigate to Custom Grid → View ERP tab.
- Verify that the AR invoices display with accurate columns such as:
- Invoice No.
- Invoice Date
- Due Date
- Customer PO
- Salesperson
- Amount and Balance
Result:
After configuration, users can view a fully functional AR Invoice grid in Sage CRM displaying data from Sage 100. This enables financial visibility for sales and finance teams. The same process can be extended to create grids for Sales Orders, Payments, Shipments, and Quotes.
If you have any queries or customization requests for your Sage CRM – Sage 100 integration, feel free to contact us at sage@greytrix.com. Our team will be happy to assist you.
About Us
Greytrix – a globally recognized and one of the oldest Sage Development Partners is a one-stop solution provider for Sage ERP and Sage CRM organizational needs. Being acknowledged and rewarded for multi-man years of experience and expertise, we bring complete end-to-end assistance for your technical consultations, product customizations, data migration, system integrations, third-party add-on development, and implementation competence.
Greytrix has some unique integration solutions developed for Sage CRM with Sage ERPs namely Sage X3, Sage Intacct, Sage 100, Sage 500, and Sage 300. We also offer best-in-class Sage ERP and Sage CRM customization and development services to Business Partners, End Users, and Sage PSG worldwide. Greytrix helps in the migration of Sage CRM from Salesforce | ACT! | SalesLogix | Goldmine | Sugar CRM | Maximizer. Our Sage CRM Product Suite includes add-ons like Greytrix Business Manager, Sage CRM Project Manager, Sage CRM Resource Planner, Sage CRM Contract Manager, Sage CRM Event Manager, Sage CRM Budget Planner, Gmail Integration, Sage CRM Mobile Service Signature, Sage CRM CTI Framework.
Greytrix is a recognized Sage Champion Partner for GUMU™ Sage X3 – Sage CRM integration listed on Sage Marketplace and Sage CRM – Sage Intacct integration listed on Sage Intacct Marketplace. The GUMU™ Cloud framework by Greytrix forms the backbone of cloud integrations that are managed in real-time for the processing and execution of application programs at the click of a button.
For more information on our integration solutions, please contact us at sage@greytrix.com. We will be glad to assist you.
