In the previous blog, we explored Dynamic Grid Metadata and how field names, titles, formats, templates, mappings control the presentation and behavior of a Dynamic Grid.
Blog 1: Introducing the Enhanced Dynamic Grid Query Manager in GUMU for Sage CRM and Sage 100
Blog 2: Understanding Dynamic Grid Metadata: The Building Blocks of Dynamic Grids
As business requirements become more complex, a Dynamic Grid may need to combine data from multiple Sage 100 tables, perform calculations, apply business rules, and display interactive information. This is where SQL and staging tables become important.
Why Use SQL for Dynamic Grids?
A simple grid may display fields directly from a data source. However, advanced requirements may require:
- Data from multiple Sage 100 tables
- Complex joins
- Calculated values
- Conditional business logic
- Custom statuses
- Data transformation
SQL provides the flexibility to prepare this information before it is displayed in Sage CRM.
The basic architecture is: Sage 100 → SQL Processing → Dynamic Grid → Sage CRM
What Is a Synchronized SQL Table?
A synchronized SQL table is a SQL table used to store data synchronized from Sage 100 so that the data can be queried and displayed by the Dynamic Grid in Sage CRM.
Sage 100 → Data Synchronization → Synchronized SQL Table → Dynamic Grid
- Simplifies complex grid queries
- Allows data from multiple sources to be combined
- Supports calculated and derived fields
- Separates data processing from presentation
- Can improve grid performance for complex datasets
Using OPENQUERY
When Sage 100 is accessed through a SQL linked server, OPENQUERY can be used to retrieve data from the ERP system.
Conceptually: SQL Server → Linked Server → Sage 100
This is useful when information needs to be retrieved from multiple Sage 100 tables and processed before being presented in the CRM. GUMU supports linked-server connectivity for accessing Sage 100 information in real-time grids and charts.
Joining Sage 100 Tables
Business information is often stored across multiple Sage 100 tables. SQL joins allow related information to be combined into a single dataset.
For example: Sage 100 Table A + Sage 100 Table B → Combined Dataset
This allows the Dynamic Grid to present a complete business view without requiring the user to navigate between different sources.
SQL + Metadata = Advanced Dynamic Grid
SQL and Dynamic Grid metadata have different responsibilities.
SQL handles the data:
- Retrieval
- Joins
- Calculations
- Business logic
- Transformation
- Metadata handles the presentation:
- Field names
- Titles
- Formats
- Templates
Together, they transform raw Sage 100 information into a useful and interactive Sage CRM experience.
Advanced Dynamic Grids go beyond displaying simple database fields. By combining SQL, staging tables, OPENQUERY, joins, complex Sage 100 information can be prepared and presented effectively within Sage CRM.
The overall concept is: Extract → Transform → Prepare → Display
Once the data structure and SQL logic are understood, the next important step is keeping the staging data updated and synchronized with Sage 100.
In the next blog, we will explore Sync Data Configuration in Dynamic Grid Manager (Query-wise), including the purpose of Sync Data, the difference between Show Grid and Sync Data, creating Sync Data queries, truncate/delete and import queries, scheduling synchronization, and best practices for keeping CRM data updated.
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.