Finance | Project Operations, Human Resources, ...

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

(0) Share

Report

Posted on by Pavel Ioana

106

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();
    
    
    }

I have the same question (0)

All responses (20)

Answers (0)

Suggested answer

GirishS 27,827 Moderator on at

Like (1)

Report
Hi Pavel,

Try the below code. You can make use of strfmt to add ranged to the query like below.

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)); qbr1 = qbds2.addRange(fieldNum(SalesTable, WorkerSalesResponsible)); qbr.value(strFmt('(%1.%2 == %3) || (%1.%4 == %5)', qbds2.name(), fieldStr(SalesTable, WorkerSalesResponsible), dialogSalesResponsible.value(), fieldStr(SalesTable, WorkerSalesTaker), dialogSalesTaker.value())); qbr1.status(RangeStatus::Hidden); //qbds2.addRange(fieldNum(SalesTable, WorkerSalesResponsible)).value(queryValue(dialogSalesResponsible.value())); //qbds2.addRange(fieldNum(SalesTable, WorkerSalesTaker)).value(queryValue(dialogSalesTaker.value()));

Thanks,

Girish S.

Was this reply helpful? Yes No
Pavel Ioana 106 on at

Like (0)

Report

And this code does the same thing as my code. To display my sales correctly, I need to fill in both fields, but I don't need this

Was this reply helpful? Yes No
GirishS 27,827 Moderator on at

Like (0)

Report

Hi Pavel,

I am not able to get you.

Can you elaborate?

Thanks,

Girish S.

Was this reply helpful? Yes No
Martin Dráb 237,990 Most Valuable Professional on at

Like (0)

Report

That's a strange result. If the filters works, there should be an 'OR' condition. If it doesn't work, you should get an error or no records. Are you sure that you've successfully compiled the new code?

I believe that the whole expression should be in brackets, i.e. '((%1.%2 == %3) || (%1.%4 == %5))' instead of '(%1.%2 == %3) || (%1.%4 == %5)'. Give it a try.

If it doesn't help, please look at the resulting query in the debugger, or call qbds2.toString() in code and share the value with us.

Was this reply helpful? Yes No
Pavel Ioana 106 on at

Like (0)

Report

I compiled the code. This is what is displayed in the debugger

NAME:

qbds2

VALUE:

SELECT * FROM SalesTable(SalesTable_1) WHERE CustInvoiceJour.SalesId = SalesTable.SalesId AND ((WorkerSalesResponsible = 0)) AND ((WorkerSalesTaker = 22565421014))

Was this reply helpful? Yes No
GirishS 27,827 Moderator on at

Like (0)

Report

It should be OR condition not AND.

Have you tried adding range using strfmt as mentioned above?

Thanks,

Girish S.

Was this reply helpful? Yes No
Martin Dráb 237,990 Most Valuable Professional on at

Like (0)

Report

Please show us your current code for creating the query.

Was this reply helpful? Yes No
Pavel Ioana 106 on at

Like (0)

Report

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));

qbr1 = qbds2.addRange(fieldNum(SalesTable, WorkerSalesResponsible));

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

qbds2.name(),

fieldStr(SalesTable, WorkerSalesResponsible), dialogSalesResponsible.value(),

fieldStr(SalesTable, WorkerSalesTaker), dialogSalesTaker.value()));

qbr1.status(RangeStatus::Hidden);

sysTablelookup.parmQuery(query);

sysTablelookup.performFormLookup();

}

Was this reply helpful? Yes No
Martin Dráb 237,990 Most Valuable Professional on at

Like (0)

Report
That's not very readable code. Let me fix it a bit (throw unusused variables, use meaningful names), remove dependencies to your other code and post it again in the right way:

Query query = new Query(); 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(strFmt('((%1.%2 == %3) || (%1.%4 == %5))', salesQbds.name(), fieldStr(SalesTable, WorkerSalesResponsible), 11111, fieldStr(SalesTable, WorkerSalesTaker), 22222)); info(invoiceQbds.toString());

When I run it, I see a query with || operator, not AND, as you claimed:

SELECT * FROM CustInvoiceJour(CustInvoiceJour_1) JOIN * FROM SalesTable(SalesTable_1) ON CustInvoiceJour.SalesId = SalesTable.SalesId AND ((((SalesTable_1.WorkerSalesResponsible == 11111) || (SalesTable_1.WorkerSalesTaker == 22222))))

Please try it once more.

Was this reply helpful? Yes No
Martin Dráb 237,990 Most Valuable Professional on at

Like (0)

Report

By the way, do you realize that you join gives just a single sales order, but a single invoice may contain several others? The right approach is using CustInvoiceSalesLink table.

Was this reply helpful? Yes No

Join the conversation

Community site session details

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

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

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

Featured topics

Product updates

Community site session details

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

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

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

Featured topics

Product updates