web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Check out the Community Spotlight Video Series
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Adding range on multip...
Finance | Project Operations, Human Resources, ...
Suggested Answer

Adding range on multiple fields of datasource

(1) ShareShare
ReportReport
Posted on by CU19030852-0 63
Hi everyone, i'm trying to range the records of the SalesParmTable datasource after OnQueryExecuted on SalesEditLines, where i need to hide the sales orders that have the field xxProFormaInvoice not empty and ParmJobStatus is Waiting.
this is my code 
 QueryBuildRange     qbr_SalesParmTable1;                      if (custParameters.xxPendingSalesInvoice == NoYes::Yes )        {                        QueryBuildDataSource qbds_SalesParmTable = sender.query().dataSourceTable(tableNum(SalesParmTable));            qbr_SalesParmTable1 = qbds_SalesParmTable.addRange(fieldNum(SalesParmTable,xxProFormaInvoice));            qbr_SalesParmTable1.value(strFmt('((%1 == %2) && (%3 == %4))',                fieldStr(SalesParmTable, xxProFormaInvoice),                SysQuery::valueNotEmptyString(),                fieldStr(SalesParmTable, ParmJobStatus),                SysQuery::value(ParmJobStatus::Waiting)));            qbr_SalesParmTable1.status(RangeStatus::Hidden);                     }
but i'm getting all the records that exist in SalesParmTable which is not the expected result.
would appreciate if you could point to what i'm doing wrong.
thank you.
I have the same question (0)
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 240,278 Most Valuable Professional on at
    You skipped an important step in debugging. You wrote X++ code to generate a query and then you tested whether the query returns correct data, but you have no idea about what's wrong because you never verified that the query was what you wanted.
     
    When I look at your code
    QueryBuildRange     qbr_SalesParmTable1;

if (custParameters.xxPendingSalesInvoice == NoYes::Yes)
{
    QueryBuildDataSource qbds_SalesParmTable = sender.query().dataSourceTable(tableNum(SalesParmTable));
    qbr_SalesParmTable1 = qbds_SalesParmTable.addRange(fieldNum(SalesParmTable,xxProFormaInvoice));
    qbr_SalesParmTable1.value(strFmt('((%1 == %2) && (%3 == %4))',
        fieldStr(SalesParmTable, xxProFormaInvoice),
        SysQuery::valueNotEmptyString(),
        fieldStr(SalesParmTable, ParmJobStatus),
        SysQuery::value(ParmJobStatus::Waiting)));
    qbr_SalesParmTable1.status(RangeStatus::Hidden);
}
     
    the obvious problem I see is the usage of the textually query expression ((%1 == %2) && (%3 == %4)). It's difficult to write and debug, therefore you should use it only if there is no other choice. Fortunately, your requirement can be implemented in an easier and safer way:
    qbds_SalesParmTable.addRange(fieldNum(SalesParmTable, xxProFormaInvoice))
    .value(SysQuery::valueNotEmptyString());
qbds_SalesParmTable.addRange(fieldNum(SalesParmTable, ParmJobStatus))
    .value(queryValue(ParmJobStatus::Waiting));
     
  • Suggested answer
    Waed Ayyad Profile Picture
    Waed Ayyad 9,212 Super User 2026 Season 2 on at
    Hi Community User,
     
    You can try this code,
     
     QueryBuildRange     qbr_SalesParmTable1;               
 if (custParameters.xxPendingSalesInvoice == NoYes::Yes )     
 {                      
    QueryBuildDataSource qbds_SalesParmTable = sender.query().dataSourceTable(tableNum(SalesParmTable));
    qbr_SalesParmTable1 = qbds_SalesParmTable.addRange(fieldNum(SalesParmTable,xxProFormaInvoice));

    qbr_SalesParmTable1.value(strFmt('((%1 != "%2") && (%3 == %4))',    
      fieldStr(SalesParmTable, xxProFormaInvoice), 
      SysQuery::value(''),              
      fieldStr(SalesParmTable, ParmJobStatus),           
      any2Int(ParmJobStatus::Waiting)));   
      
      qbr_SalesParmTable1.status(RangeStatus::Hidden);  
}
    Thanks
    Waed Ayyad
    Please mark this answer as "Verified" if it solved your issue. In order to help others who will face a similar issue in the future
     
  • CU19030852-0 Profile Picture
    CU19030852-0 63 on at
    thank you Waed Ayyad and Martin Dráb for the quick answer.
     
    Waed Ayyad: i tried the code but in all cases it gaves back an empty result don't know why.
     
    Martin Dráb: when i range like in your code how i'm i supposed to hide the resulted records?
     
  • Martin Dráb Profile Picture
    Martin Dráb 240,278 Most Valuable Professional on at
    I don't understand your question.
     
    Your (and mine) code isn't about hiding; it defines which records should be returned from database. That's done by adding ranges, such as saying that you want only records where xxProFormaInvoice is not empty. That's what you can see in my code. Namely, the following piece of code says that you want to filter by xxProFormaInvoice and that only records with non-empty value should be returned.
    qbds.addRange(fieldNum(SalesParmTable, xxProFormaInvoice))
    .value(SysQuery::valueNotEmptyString());
     
  • CU19030852-0 Profile Picture
    CU19030852-0 63 on at
    Martin Dráb i do apologize for the confusion, what i want to do is to hide the records (that have the field xxProFormaInvoice not empty and ParmJobStatus field on Waiting) from what is being displayed on the datasource
    in order to do so i wanted to range the datasource and then hide the resulted record.
  • Suggested answer
    Waed Ayyad Profile Picture
    Waed Ayyad 9,212 Super User 2026 Season 2 on at
    Hi Community User,
     
    Try to revert the ranges:
    qbds_SalesParmTable.addRange(fieldNum(SalesParmTable, xxProFormaInvoice))
    .value(SysQuery::QueryValue(''));
qbds_SalesParmTable.addRange(fieldNum(SalesParmTable, ParmJobStatus))
    .value(SysQuery::ValueNot(ParmJobStatus::Waiting));
     
    Thanks
    Waed Ayyad
    Please mark this answer as "Verified" if it solved your issue. In order to help others who will face a similar issue in the future
  • Martin Dráb Profile Picture
    Martin Dráb 240,278 Most Valuable Professional on at
    Waed is right that you need to reverse the conditions, but his code isn't correct. This should work better: 
    qbds_SalesParmTable.addRange(fieldNum(SalesParmTable, xxProFormaInvoice))
    .value(SysQuery::valueEmptyString());
qbds_SalesParmTable.addRange(fieldNum(SalesParmTable, ParmJobStatus))
    .value(SysQuery::valueNot(ParmJobStatus::Waiting));
  • CU19030852-0 Profile Picture
    CU19030852-0 63 on at
    hey thank you both, but it's still not returning empty datasource whatever the case even the standard query isn't retuning records. tried to debug and this the value of qbds_SalesParmTable
    {SELECT FIRSTFAST FORUPDATE * FROM SalesParmTable(SalesParmTable) 
USING INDEX ParmTableRefIdx WHERE ((ParmId = N'ICPS-007221')) AND ((xxProFormaInvoice = '') OR
 (xxProFormaInvoice = '') OR (xxProFormaInvoice = '')) AND ((NOT (ParmJobStatus = 2)) OR 
(NOT (ParmJobStatus = 2)) OR (NOT (ParmJobStatus = 2))) OUTER JOIN FORUPDATE LineNum, RecId 
FROM BankLCExportLine(Ref_BankLCExportLine_BankLCExportLine) ON 
SalesParmTable.BankLCExportLine = BankLCExportLine.RecId OUTER JOIN FORUPDATE Location, 
Location FROM LogisticsPostalAddress(Ref_LogisticsPostalAddress_LadingPostalAddress_RU) ON 
SalesParmTable.LadingPostalAddress_RU = LogisticsPostalAddress.RecId OUTER JOIN 
FORUPDATE Description FROM LogisticsLocation(Ref_LogisticsLocation_LadingPostalAddress_RU_Location)
 ON LogisticsPostalAddress.Location = LogisticsLocation.RecId OUTER JOIN FORUPDATE Location, 
Location FROM LogisticsPostalAddress(Ref_LogisticsPostalAddress_UnladingPostalAddress_RU) ON 
SalesParmTable.UnladingPostalAddress_RU = LogisticsPostalAddress.RecId OUTER JOIN FORUPDATE 
Description FROM LogisticsLocation(Ref_LogisticsLocation_UnladingPostalAddress_RU_Location) 
ON LogisticsPostalAddress.Location = LogisticsLocation.RecId OUTER JOIN FORUPDATE 
RegistrationNumber FROM TaxRegistration(Ref_TaxRegistration_TaxRegistration) ON 
SalesParmTable.TaxId = TaxRegistration.RecId OUTER JOIN FORUPDATE ShippingBillNumber FROM 
CustomsShippingBillNumberTable_IN(Ref_CustomsShippingBillNumberTable_IN_CustomsShippingBillNumberTable_IN1) ON 
SalesParmTable.CustomsShippingBillNumberTable_IN = CustomsShippingBillNumberTable_IN.RecId 
OUTER JOIN FORUPDATE FiscalDocumentTypeId FROM FiscalDocumentType_BR(Ref_FiscalDocumentType_BR_FiscalDocumentType_BR) ON 
SalesParmTable.FiscalDocumentType_BR = FiscalDocumentType_BR.RecId 
OUTER JOIN FORUPDATE PortId FROM EximPorts_IN(Ref_EximPorts_IN_EximPorts_IN) ON SalesParmTable.EXIMPorts_IN = EximPorts_IN.RecId}
    and this my code  
     /// <summary>
    ///
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    [FormDataSourceEventHandler(formDataSourceStr(SalesEditLines, SalesParmTable), FormDataSourceEventType::QueryExecuted)]
    public static void SalesParmTable_OnQueryExecuted(FormDataSource sender, FormDataSourceEventArgs e)
    {
        CustParameters  custParameters = CustParameters::find();
       
        if (custParameters.HPSPendingSalesInvoice == NoYes::Yes )
            {
                QueryBuildDataSource qbds_SalesParmTable = sender.query().dataSourceTable(tableNum(SalesParmTable));

                qbds_SalesParmTable.addRange(fieldNum(SalesParmTable, xxProFormaInvoice)).value(SysQuery::valueEmptyString());
                qbds_SalesParmTable.addRange(fieldNum(SalesParmTable, ParmJobStatus)).value(SysQuery::valueNot(ParmJobStatus::Waiting));
      
                

            }
    }
     
  • Martin Dráb Profile Picture
    Martin Dráb 240,278 Most Valuable Professional on at
    Your query string reveals a bug in your code. The reason why you have duplicated conditions there (e.g. (xxProFormaInvoice = '') OR (xxProFormaInvoice = '') OR (xxProFormaInvoice = '')) is caused by the fact that you're adding ranges without taking into account that there already may be existing ranges. You should either remove ranges (clearRanges()) or try to find an existing range (SysQuery::findOrCreateRange()). You should have spotted this problem when verifying correctness of your query string.
     
    Are you saying that the range for ParmId = ICPS-007221 is ignored? (That's how I interpret your statement "i'm getting all the records that exist in SalesParmTable").
  • CU19030852-0 Profile Picture
    CU19030852-0 63 on at
    ("i'm getting all the records that exist in SalesParmTable") that's with when i used my initial code but when i used the code you gave i'm getting empty datasource
    any idea how i fix that? 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Check out the Community Spotlight Video Series

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Women in Power Builds Momentum

Expanding mentorship, skilling, and AI innovation

Congratulations to the July Top 10 Community Leaders

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 263 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 257 Super User 2026 Season 2

#3
Subra Profile Picture

Subra 235 Super User 2026 Season 2

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP
License usage reporting (in-app and PPAC) with Entra dynamic groups

Product updates

Dynamics 365 release plans