I am filtering the data in form Find sales order which is accessible from return order forms. In this form, only invoices which sales pool is related to the current user or even if the sales pool is empty must be shown.
I have a table which maps a user to the related sales pools. By augmenting Custinvoicejour data source class I add my logic. However when I open the form the error raised that "Query extended range failure: SalesTable.SalesPoolId is not a valid datasource.field pair near pos 55."
I should mention that I have filtered the sales order list page successfully and the logic was the same. The only difference is that in sales order main data source is SalesTable and need only one join to the mapping user table but CustInvoicejour is the main data source of the find sales order and needed firstly to join SalesTable.
I can't figure out why in this situation the query filter is not working. I could apply filter by writing more complex joins (using multiple not exist join) .
This is the code for filtering Find sales order form which causes the error
[Extensionof(formDataSourceStr(SalesCopying, CustInvoiceJour))]
final class SalesCopying_CustInvoiceJourDS_Extension
{
private QueryFilter queryFilterPool;
public void init()
{
next init();
QueryBuildDataSource qbdsSalesTable, qbdsUserMapping;
if(!Global::isSystemAdministrator()
&& SalesParameters::find().FilterSalesByPool )
{
qbdsSalesTable = this.queryBuildDataSource().addDataSource(tableNum(SalesTable));
qbdsSalesTable.joinMode(JoinMode::InnerJoin);
qbdsSalesTable.fetchMode(QueryFetchMode::One2One);
qbdsSalesTable.addLink(fieldNum(CustInvoiceJour, SalesId), fieldNum(SalesTable, SalesId), this.queryBuildDatasource().name());
qbdsUserMapping = qbdsSalesTable.addDataSource(tableNum(UserMappingSalesPool));
qbdsUserMapping.joinMode(JoinMode::OuterJoin);
qbdsUserMapping.fetchMode(QueryFetchMode::One2One);
qbdsUserMapping.addLink(fieldNum(SalesTable, SalesPoolId), fieldNum(UserMappingSalesPool, SalesPoolId), qbdsSalesTable.name());
queryFilterPool = this.query().addQueryFilter(qbdsUserMapping ,fieldstr(UserMappingSalesPool, UserId));
queryFilterPool.status(RangeStatus::Hidden);
}
}
public void executeQuery()
{
if(!Global::isSystemAdministrator()
&& SalesParameters::find().FilterSalesByPool)
{
queryFilterPool.value(strFmt('((%1 == "%2") || (%3.%4 == ""))',
fieldStr(UserMappingSalesPool, UserId),
SysQuery::value(curUserId()),
tablestr(SalesTable),
fieldStr(SalesTable, SalesPoolId)));
}
}
}
This is the code for filtering all sales order form which works perfectly.
[Extensionof(formDataSourceStr(SalesTableListPage, SalesTable))]
final class SalesTableListPage_SalesTableDS_Extension
{
private QueryFilter queryFilterPool;
public void init()
{
next init();
QueryBuildDataSource qbds;
if(!Global::isSystemAdministrator() && SalesParameters::find().FilterSalesByPool)
{
qbds = this.queryBuildDataSource().addDataSource(tableNum(UserMappingSalesPool));
qbds.joinMode(JoinMode::OuterJoin);
qbds.fetchMode(QueryFetchMode::One2One);
qbds.addLink(fieldNum(SalesTable, SalesPoolId), fieldNum(UserMappingSalesPool, SalesPoolId), this.name());
queryFilterPool = this.query().addQueryFilter(qbds ,fieldstr(UserMappingSalesPool, UserId));
queryFilterPool.status(RangeStatus::Hidden);
}
}
public void executeQuery()
{
if(!Global::isSystemAdministrator() && SalesParameters::find().FilterSalesByPool)
{
queryFilterPool.value(strFmt('((%1 == "%2") || (%3.%4 == ""))',
fieldStr(UserMappingSalesPool, UserId),
SysQuery::value(curUserId()),
tableStr(SalesTable),
fieldStr(SalesTable, SalesPoolId)));
}
next executeQuery();
}
