Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance forum / Adding range on multip...
Finance forum
Suggested answer

Adding range on multiple fields of datasource

(0) ShareShare
ReportReport
Posted on by Community member 52
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.
Categories:
Development / Customization / SDK
  • Bharani Preetham Peraka Profile Picture
    Bharani Preetham Pe... 3,572 Moderator on at
    Adding range on multiple fields of datasource
    Is the query is showing right records when you check the same in SQL? Also is the method called actually?
  • Martin Dráb Profile Picture
    Martin Dráb 228,651 Most Valuable Professional on at
    Adding range on multiple fields of datasource
    Now you see how important is to test individual pieces to find out which is responsible for the problem. If you test many things at once, you don't know which one is to blame and mere guessing is often misleading.
  • Community member Profile Picture
    Community member 52 on at
    Adding range on multiple fields of datasource
    Okay, so it turns out that when the form is open, the salesparmtable on saleseditlines creates new records (without xxProFormaInvoice) that it uses, which is why when I range i have an empty query, I need to redo my entire approach. Thank you again, Martin.
  • Martin Dráb Profile Picture
    Martin Dráb 228,651 Most Valuable Professional on at
    Adding range on multiple fields of datasource
    I find information from you very confusing. Please show us explicitly:
     
    1. The piece of code you're trying to get working, not some other code.
    2. Result of getSQLStatement() of the query.
    3. A screenshot showing that returned data contains both empty and non-empty values of xxProFormaInvoice field.
  • Community member Profile Picture
    Community member 52 on at
    Adding range on multiple fields of datasource
    I am aware of this, I changed the query for demonstration, now if i use your it still returns the same result, hiding all waiting records regardless of whether the ProFormaInvoice column is empty or not.
  • Martin Dráb Profile Picture
    Martin Dráb 228,651 Most Valuable Professional on at
    Adding range on multiple fields of datasource
    You're getting the opposite of what you want because you changed my code by using the opposite conditions. Now your query returns only those records that you wanted to hide. Fix it by reverting to my code.
  • Community member Profile Picture
    Community member 52 on at
    Adding range on multiple fields of datasource
    Hello Martin, I appreciate your time and assistance.
    Please allow me to elaborate on my business requirements.
    After the user tries to choose certain records from a query, I have to hide those records, and I will show you some screenshots.
     
    I get your point about the job and why I need to place the code before the query execution.
    (I already changed that.)
     
    when i ran your job, i got this 
     
    1. RecId 5637176840, SalesId SO-000797, ProFormaInvoice , ParmJobStatus Executed
    2. RecId 5637176838, SalesId SO-000796, ProFormaInvoice , ParmJobStatus Executed
    3. RecId 5637176157, SalesId SO-000795, ProFormaInvoice , ParmJobStatus Executed
    4.  
    and 500 more. this is not what i want to keep in the datasource.
    I made this job to display the records that I wish to hide or even delete if it's easier.
     
    Query query = new Query();
        QueryBuildDataSource qbds = query.addDataSource(tableNum(SalesParmTable));

        qbds.addRange(fieldNum(SalesParmTable, xxProFormaInvoice)).value(SysQuery::valueNotEmptyString());
        qbds.addRange(fieldNum(SalesParmTable, ParmJobStatus)).value(SysQuery::value(ParmJobStatus::Waiting));
        

        QueryRun qr = new QueryRun(query);
        while (qr.next())
        {
            SalesParmTable parmTable = qr.get(tableNum(SalesParmTable));
            info(strFmt("RecId %1, SalesId %2, ProFormaInvoice %3, ParmJobStatus %4", parmTable.RecId, parmTable.SalesId, parmTable.xxProFormaInvoice, parmTable.ParmJobStatus));
        }
    and this is the result 
     
    RecId 5637182079, SalesId SO-000783, ProFormaInvoice xx-00004, ParmJobStatus Waiting
    RecId 5637180581, SalesId SO-000783, ProFormaInvoice xx-00003, ParmJobStatus Waiting
    RecId 5637179083, SalesId SO-000783, ProFormaInvoice xx-00002, ParmJobStatus Waiting
    RecId 5637191844, SalesId SO-000770, ProFormaInvoice xx-00011, ParmJobStatus Waiting
    RecId 5637190367, SalesId SO-000770, ProFormaInvoice xx-00007, ParmJobStatus Waiting
     
    i need to hide (or delete from datasource) this records 
     
    i need to hide the records that have "ProFormaInvoice not empty and ParmJobStatus is waiting" at the same time on the same record 
     
    when the user select a query from here and the result show one those records.
    For example, I have to hide these record.
    Once again, Martin, I appreciate your time and assistance. I hope my response did not cause further confusion.
  • Martin Dráb Profile Picture
    Martin Dráb 228,651 Most Valuable Professional on at
    Adding range on multiple fields of datasource
    Maybe you didn't run the query in isolation because you don't know how. If so, this example will help you: 
    Query query = new Query();
QueryBuildDataSource qbds = query.addDataSource(tableNum(SalesParmTable));

qbds.addRange(fieldNum(SalesParmTable, xxProFormaInvoice)).value(SysQuery::valueEmptyString());
qbds.addRange(fieldNum(SalesParmTable, ParmJobStatus)).value(SysQuery::valueNot(ParmJobStatus::Waiting));
// You didn't mention this condition by it was in the query string.
// Remove or change it as needed.
qbds.addRange(fieldNum(SalesParmTable, ParmId)).value(queryValue('ICPS-007221'));

QueryRun qr = new QueryRun(query);
while (qr.next())
{
    SalesParmTable parmTable = qr.get(tableNum(SalesParmTable));
    info(strFmt("RecId %1", parmTable.RecId));    
}
  • Martin Dráb Profile Picture
    Martin Dráb 228,651 Most Valuable Professional on at
    Adding range on multiple fields of datasource
    I've just noticed another bug of yours - you're changing the form query after it's executed, which is obviously wrong. Your form uses a different query than what you set in code and therefore your testing is pretty much useless.
     
    This is one of reasons why you should test individual components separately, because now you have no idea which part failed. If you want to test a query, you shouldn't test a form, if you want to rule out such problems.
     
    Anyway, at least fix the bug (= change the query before executing it) and try it again.
     
    When you know that the query returns no records, it means that there is no record meeting all the conditions. You need either different conditions or different data.
     
    If you're sure that the data is correct, it must mean that you want different conditions. But we can't tell you what you want; it depends on your business requirements.
     
    Let me repeat your technical requirement: "hide sales orders that have the field xxProFormaInvoice not empty and ParmJobStatus is Waiting". Let's assumes that it means SalesParmTable, not sales orders.Then the query should be (in pseudo code): select SalesParmTable where xxProFormaInvoice is not empty and ParmJobStatus is not Waiting. That's what the two conditions do, in my opinion. Maybe your technical requirement isn't what you actually need to fulfill the business requirement.
  • Community member Profile Picture
    Community member 52 on at
    Adding range on multiple fields of datasource
    the screenshot shows the result of this code, that's why i said  it shows no records  
    qbds_SalesParmTable.addRange(fieldNum(SalesParmTable, xxProFormaInvoice))
    .value(SysQuery::valueEmptyString());
qbds_SalesParmTable.addRange(fieldNum(SalesParmTable, ParmJobStatus))
    .value(SysQuery::valueNot(ParmJobStatus::Waiting));
    while in the first code that i posted here gives me all the records.
    i checked my data and the records that i want to hide do exist and get displayed in the form.
    i think i will change my approch if there is no other solution.
     

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

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

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

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,564 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,651 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans