Dynamics Query
We use classes to write the Dynamics Queries, dynamic query that can be used to retrieve data from the database. So, in simple terms, a Dynamics Query is like a recipe that tells the system what data you want, where to find it, how to filter it, and how to organize it. When you "cook" the query, you get back the data you need to work with.
X++ code:
The total count shows how many records with the customer name of customer table are in the sales table,
totalCounter counts the total number of records that are entered on US-001,counter counts the number of records that have given US-001.
class RetieveDataJob
{
static void main(Args _args)
{
Query query;
QueryBuildDataSource datasource;
QueryBuildRange range;
QueryFilter filter;
QueryRun queryRun;
int counter = 0, totalCounter = 0;
query = new Query();
datasource = query.addDataSource(tableNum(CustTable));
datasource = datasource.addDataSource(tableNum(SalesTable));
datasource.joinMode(JoinMode::InnerJoin); datasource.relations(true);
datasource.addLink(fieldNum(CustTable, AccountNum),
fieldNum(SalesTable, CustAccount));
range = datasource.addRange(fieldNum(SalesTable, CustAccount));
range.value(SysQuery::value('us-001'));
queryRun = new QueryRun(query);
while (queryRun.next())
{
totalCounter++;
if (queryRun.changed(tableNum(CustTable)))
counter++;
}
info(strFmt("Customer Counter: %1", counter));
info(strFmt("Total result Counter: %1", totalCounter));
}
}
Visual studio
output:
SSMS__SQL Query:
SELECT SalesTable.DATAAREAID, * FROM CustTable CustTable
JOIN SalesTable SalesTable ON CustTable.AccountNum = SalesTable.CustAccount
AND CustTable.AccountNum = SalesTable.CustAccount
AND CustAccount = 'us-001'
AND CustTable.DATAAREAID = 'usmf'
AND SALESTABLE.SALESGROUP = 01
SSMS_Output:
Hope this info is useful to you....🙂
Thanks for the Visit....👍
No comments:
Post a Comment