Finance | Project Operations, Human Resources, ...

Adding range on multiple fields of datasource

(1) 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.

I have the same question (0)

All responses (21)

Answers (0)

Suggested answer

Martin Dráb 237,801 Most Valuable Professional on at

Like (1)

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

Was this reply helpful? Yes No
Suggested answer

Waed Ayyad 9,039 Super User 2025 Season 2 on at

Like (1)

Report
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

Was this reply helpful? Yes No
Community member 54 on at

Like (0)

Report

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?

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

Like (2)

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

Was this reply helpful? Yes No
Community member 54 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Waed Ayyad 9,039 Super User 2025 Season 2 on at

Like (1)

Report
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

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

Like (1)

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

Was this reply helpful? Yes No
Community member 54 on at

Like (0)

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

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

Like (1)

Report

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").

Was this reply helpful? Yes No
Community member 54 on at

Like (0)

Report

("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?

Was this reply helpful? Yes No