Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / How do I add ranges on...
Finance | Project Operations, Human Resources, ...
Suggested answer

How do I add ranges on two different fields from the same data source using query?

(0) ShareShare
ReportReport
Posted on by Pavel Ioana 102

Hey! I have to display for the Sales Responsible and for the Sales Taker only the sales that belong to them. If I write the code as written below, I have to complete both fields in order for the Sales ID lookup to be displayed correctly, but there may be a situation when the Sales Responsible and the Sales Taker are two different people. How can I display this? 

    public void lookupSalesId(FormStringControl _control)
{   
 
    Query query= new Query();
    Query query1= new Query();
    QueryBuildDataSource    qbds1,qbds2,qbds3,qbds4;
    QueryBuildRange qbr1;
    
    SysTableLookup sysTablelookup;
    
    contract = this.dataContractObject();

    sysTablelookup =SysTableLookup::newParameters(tableNum(CustInvoiceJour),_control);
    sysTablelookup.addLookupfield(fieldNum(CustInvoiceJour,SalesId));

    qbds1=query.addDataSource(tableNum(CustInvoiceJour));
    qbds1.addRange(fieldNum(CustInvoiceJour, InvoiceAccount)).value(dialogInvoiceAccount.value());
    qbds2=qbds1.addDataSource(tableNum(SalesTable));
    
    //qbds2.joinMode(JoinMode::InnerJoin);
    qbds2.addLink(fieldNum(CustInvoiceJour, SalesId), fieldNum(SalesTable, SalesId));
    qbds2.addRange(fieldNum(SalesTable, WorkerSalesResponsible)).value(queryValue(dialogSalesResponsible.value()));
    qbds2.addRange(fieldNum(SalesTable, WorkerSalesTaker)).value(queryValue(dialogSalesTaker.value()));
    
    sysTablelookup.parmQuery(query);
    sysTablelookup.performFormLookup();
    
    
    }

pastedimage1665486676752v2.png

  • Martin Dráb Profile Picture
    Martin Dráb 231,837 Most Valuable Professional on at
    RE: How do I add ranges on two different fields from the same data source using query?

    Let me simplify your code, such as using relations() instead of addLink() and throwing away the unused variable:

    public void lookupSalesId(FormStringControl _control)
{
	SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(CustInvoiceJour), _control);

	Query query = new Query();	
	QueryBuildDataSource invoiceQbds = query.addDataSource(tableNum(CustInvoiceJour));
	QueryBuildDataSource salesQbds = invoiceQbds.addDataSource(tableNum(SalesTable));
	
	salesQbds.relations(true);
	invoiceQbds.addRange(fieldNum(CustInvoiceJour, InvoiceAccount)).value(queryValue(dialogInvoiceAccount.value()));

	if (dialogSalesResponsible.value() != 0)
	{
		salesQbds.addRange(fieldNum(SalesTable, WorkerSalesResponsible)).value(queryValue(dialogSalesResponsible.value()));

	}
	
	if (dialogSalesTaker.value() != 0)
	{
		salesQbds.addRange(fieldNum(SalesTable, WorkerSalesTaker)).value(queryValue(dialogSalesTaker.value()));
	}	
	
	sysTableLookup.addLookupField(fieldNum(CustInvoiceJour, SalesId));	
	sysTableLookup.parmQuery(query);
	sysTableLookup.performFormLookup();
}

    So you merely wanted to conditionally add ranges on two fields.

  • Pavel Ioana Profile Picture
    Pavel Ioana 102 on at
    RE: How do I add ranges on two different fields from the same data source using query?
    I solved the problem.
    This is the code I used.
    public void lookupSalesId(FormStringControl _control)
{
Query query = new Query();
QueryBuildDataSource invoiceQbds = query.addDataSource(tableNum(CustInvoiceJour));
QueryBuildDataSource salesQbds = invoiceQbds.addDataSource(tableNum(SalesTable));
QueryBuildRange range = salesQbds.addRange(fieldNum(SalesTable, WorkerSalesResponsible));
SysTableLookup sysTablelookup;

contract = this.dataContractObject();

sysTablelookup =SysTableLookup::newParameters(tableNum(CustInvoiceJour),_control);
sysTablelookup.addLookupfield(fieldNum(CustInvoiceJour,SalesId));
invoiceQbds.addRange(fieldNum(CustInvoiceJour, InvoiceAccount)).value(dialogInvoiceAccount.value());
salesQbds.addLink(fieldNum(CustInvoiceJour, SalesId), fieldNum(SalesTable, SalesId));

if(dialogSalesResponsible.value() != 0)
{
    range.value(queryValue(dialogSalesResponsible.value()));

}
if(dialogSalesTaker.value() != 0)
{

    salesQbds.addRange(fieldNum(SalesTable, WorkerSalesTaker)).value(queryValue(dialogSalesTaker.value()));

}
  sysTablelookup.parmQuery(query);
  sysTablelookup.performFormLookup();

  // info(invoiceQbds.toString());


}
  • GirishS Profile Picture
    GirishS 27,823 Moderator on at
    RE: How do I add ranges on two different fields from the same data source using query?

    If the above code doesn't work properly, then you need to get sales id for sales responsible and sales taker in separate query and insert into the list. After that iterate the list and add the range to query in the lookup method.

    Below method get Sales id against sales responsible as a list.

    public static List getSalesIdSalesResponsible(HcmWorkerRecId _workerRecId)
{
    Query query = new Query();
    List salesResponsible = new List(Types::String);
    CustInvoiceJour invoiceJour;
    QueryBuildDataSource invoiceQbds = query.addDataSource(tableNum(CustInvoiceJour));
    QueryBuildDataSource salesQbds = invoiceQbds.addDataSource(tableNum(SalesTable));
    QueryBuildRange range = salesQbds.addRange(fieldNum(SalesTable, WorkerSalesResponsible));
    salesQbds.addLink(fieldNum(CustInvoiceJour, SalesId), fieldNum(SalesTable, SalesId));
    range.value(queryvalue(_workerRecId));
    QueryRun qRUn = new QueryRun(query);
    while(qRun.next())
    {
        invoiceJour - qRun.get(tablenum(CustInvoiceJour));
        salesResponsible.addEnd(invoiceJour.SalesId)
    }
    return salesResponsible;
}

    Below method get Sales id against sales taker as a list.

    public static List getSalesIdSalesTaker(HcmWorkerRecId _workerRecId)
{
    Query query = new Query();
    List salesTaker = new List(Types::String);
    CustInvoiceJour invoiceJour;
    QueryBuildDataSource invoiceQbds = query.addDataSource(tableNum(CustInvoiceJour));
    QueryBuildDataSource salesQbds = invoiceQbds.addDataSource(tableNum(SalesTable));
    QueryBuildRange range = salesQbds.addRange(fieldNum(SalesTable, WorkerSalesTaker));
    salesQbds.addLink(fieldNum(CustInvoiceJour, SalesId), fieldNum(SalesTable, SalesId));
    range.value(queryvalue(_workerRecId));
    QueryRun qRUn = new QueryRun(query);
    while(qRun.next())
    {
        invoiceJour - qRun.get(tablenum(CustInvoiceJour));
        salesTaker.addEnd(invoiceJour.SalesId)
    }
    return salesResponsible;
}

    Now your lookup method will look like below.

    Query query = new Query();
QueryRun qRun;
CustInvoiceJour invoiceJour;
List salesResponsible,salesTaker;
ListEnumerator salesResponsibleEnumerator,salesTakerEnumerator;
salesResponsible = YourClassName::getSalesIdSalesResposible();
salesTaker = YourClassName::getSalesIdSalesTaker();
QueryBuildDataSource invoiceQbds = query.addDataSource(tableNum(CustInvoiceJour));
QueryBuildDataSource salesQbds = invoiceQbds.addDataSource(tableNum(SalesTable));
QueryBuildRange range = salesQbds.addRange(fieldNum(CustInvoiceJour, SalesId));
SysTableLookup sysTablelookup;
contract = this.dataContractObject();
sysTablelookup =SysTableLookup::newParameters(tableNum(CustInvoiceJour),_control);
sysTablelookup.addLookupfield(fieldNum(CustInvoiceJour,SalesId));  
salesQbds.addLink(fieldNum(CustInvoiceJour, SalesId), fieldNum(SalesTable, SalesId));
if(salesResponsible)
{
    salesResponsibleEnumerator = salesResponsible.getEnumerator();
    while(salesResponsibleEnumerator.moveNext())
    {
        range.value(queryRangeConcat(range.value(),salesResponsibleEnumerator.current()));
    }
}
if(salesTaker)
{
    salesTakerEnumerator = salesTaker.getEnumerator();
    while(salesTakerEnumerator.moveNext())
    {
        range.value(queryRangeConcat(range.value(),salesTakerEnumerator.current()));
    }
}
sysTablelookup.parmQuery(query);
sysTablelookup.performFormLookup();

    Thanks,

    Girish S.

  • Pavel Ioana Profile Picture
    Pavel Ioana 102 on at
    RE: How do I add ranges on two different fields from the same data source using query?

    I tried the code posted by you and the code suggested by Martin and unfortunately the problem was not solved

  • GirishS Profile Picture
    GirishS 27,823 Moderator on at
    RE: How do I add ranges on two different fields from the same data source using query?

    Have you tried the code which i posted above?

    Still not working?

    Thanks,

    Girish S.

  • Pavel Ioana Profile Picture
    Pavel Ioana 102 on at
    RE: How do I add ranges on two different fields from the same data source using query?

    Hi! Girish,

    Unfortunately, the problem was not solved.

  • GirishS Profile Picture
    GirishS 27,823 Moderator on at
    RE: How do I add ranges on two different fields from the same data source using query?

    Hi Pavel,

    Is the issue resolved?

    Thanks,

    Girish S.

  • Martin Dráb Profile Picture
    Martin Dráb 231,837 Most Valuable Professional on at
    RE: How do I add ranges on two different fields from the same data source using query?

    All right, so the problem with AND instead of || is resolved.

    I can't comment on "my sales are displayed correctly only when both fields are filled", because it depends also on the data in your system and on your expectations, not just on the query.

    Consider running a SQL query to check that the expected data already exists. Then share it with us, so we can see what SQL query you want to generate by your X++ code.

  • GirishS Profile Picture
    GirishS 27,823 Moderator on at
    RE: How do I add ranges on two different fields from the same data source using query?

    Hi pavel,

    If the sales responsible and sales taker is given, then only you need to add range to the sales id lookup. Like below code you need to check the condition and then add the range.

    Query query = new Query();

QueryBuildDataSource invoiceQbds = query.addDataSource(tableNum(CustInvoiceJour));

QueryBuildDataSource salesQbds = invoiceQbds.addDataSource(tableNum(SalesTable));

QueryBuildRange range = salesQbds.addRange(fieldNum(SalesTable, WorkerSalesResponsible));

SysTableLookup sysTablelookup;

contract = this.dataContractObject();

sysTablelookup =SysTableLookup::newParameters(tableNum(CustInvoiceJour),_control);

sysTablelookup.addLookupfield(fieldNum(CustInvoiceJour,SalesId));  

salesQbds.addLink(fieldNum(CustInvoiceJour, SalesId), fieldNum(SalesTable, SalesId));
if(dialogSalesResponsible.value() != 0 || dialogSalesTaker.value() != 0)
{
    range.value(strFmt('((%1.%2 == %3) || (%1.%4 == %5))',

    salesQbds.name(),

    fieldStr(SalesTable, WorkerSalesResponsible),

    dialogSalesResponsible.value(),

    fieldStr(SalesTable, WorkerSalesTaker),

    dialogSalesTaker.value()));
}

  sysTablelookup.parmQuery(query);

  sysTablelookup.performFormLookup();

   //info(invoiceQbds.toString());

    So the above code will add range to the sales id if one of the fields has value not equal to 0 or empty string.

    Thanks,

    Girish S.

  • Pavel Ioana Profile Picture
    Pavel Ioana 102 on at
    RE: How do I add ranges on two different fields from the same data source using query?

    I ran the following code

    Query query = new Query();

QueryBuildDataSource invoiceQbds = query.addDataSource(tableNum(CustInvoiceJour));

QueryBuildDataSource salesQbds = invoiceQbds.addDataSource(tableNum(SalesTable));

QueryBuildRange range = salesQbds.addRange(fieldNum(SalesTable, WorkerSalesResponsible));

SysTableLookup sysTablelookup;

contract = this.dataContractObject();

sysTablelookup =SysTableLookup::newParameters(tableNum(CustInvoiceJour),_control);

sysTablelookup.addLookupfield(fieldNum(CustInvoiceJour,SalesId));  

salesQbds.addLink(fieldNum(CustInvoiceJour, SalesId), fieldNum(SalesTable, SalesId));

range.value(strFmt('((%1.%2 == %3) || (%1.%4 == %5))',

salesQbds.name(),

fieldStr(SalesTable, WorkerSalesResponsible),

dialogSalesResponsible.value(),

fieldStr(SalesTable, WorkerSalesTaker),

dialogSalesTaker.value()));

  sysTablelookup.parmQuery(query);

  sysTablelookup.performFormLookup();

   //info(invoiceQbds.toString());

    The query is of the form:

    SELECT SalesId, SalesId, InvoiceAccount, InvoiceId FROM CustInvoiceJour(CustInvoiceJour_1) JOIN * FROM SalesTable(SalesTable_1) ON CustInvoiceJour.SalesId = SalesTable.SalesId AND ((((SalesTable_1.WorkerSalesResponsible == 0) || (SalesTable_1.WorkerSalesTaker == 22565421014))))

     The problem still exists because my sales are displayed correctly only when both fields are filled

    pastedimage1665558507708v1.png

    pastedimage1665558523450v2.png

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

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 293,001 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,837 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans