Finance | Project Operations, Human Resources, ...

Adding range on multiple fields of datasource

(0) Share

Report

Posted on by Community member

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.

All responses (21)

Answers (0)

Bharani Preetham Pe... 3,609 Super User 2024 Season 1 on at

Like (0)

Report

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 230,370 Most Valuable Professional on at

Like (1)

Report

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 52 on at

Like (0)

Report

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 230,370 Most Valuable Professional on at

Like (1)

Report

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 52 on at

Like (0)

Report

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 230,370 Most Valuable Professional on at

Like (1)

Report

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 52 on at

Like (0)

Report
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

RecId 5637176840, SalesId SO-000797, ProFormaInvoice , ParmJobStatus Executed

RecId 5637176838, SalesId SO-000796, ProFormaInvoice , ParmJobStatus Executed

RecId 5637176157, SalesId SO-000795, ProFormaInvoice , ParmJobStatus Executed

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 230,370 Most Valuable Professional on at

Like (1)

Report
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 230,370 Most Valuable Professional on at

Like (1)

Report

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 52 on at

Like (0)

Report
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.