Skip to main content

Notifications

Announcements

No record found.

Supply Chain Management forum
Answered

Query extended range failure: x is not a valid datasource.field pair near pos

Posted on by 288

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

    }

  • Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,083 on at
    RE: Query extended range failure: x is not a valid datasource.field pair near pos

    Hi Nastaran,

    In SalesTableListPage it worked because SalesTable is already dataSource on the form and it has the name "SalesTable" (same as a table name). When you add a data source in x++ at runtime, the data source name is postfixed with an incrementing number (SalesTable_1)

  • Nastaran Profile Picture
    Nastaran 288 on at
    RE: Query extended range failure: x is not a valid datasource.field pair near pos

    Hi Sergei,

    As you said I replace this code and it works perfectly many thanks.

       queryFilterPool.value(strFmt('((%1 == "%2") || (%3.%4 == ""))',
                                    fieldStr(serMappingSalesPool, UserId),
                                    SysQuery::value(curUserId()),
    								this.query().dataSourceTable(tableNum(SalesTable)).name(),
                                    fieldStr(SalesTable, SalesPoolId)));

    I don't understand why the previous code for filtering sales order list page was working I mean second block of the code in my question. In that scenario so have I used tableStr

  • Verified answer
    Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,083 on at
    RE: Query extended range failure: x is not a valid datasource.field pair near pos

    Hi Nastaran,

    Try to get it from query 

    query.dataSourceTable(tableNum(SalesTable)).name()

    or you can move qbdsSalesTable variable from init scope to class declaration and use qbdsSalesTable.name().

  • Nastaran Profile Picture
    Nastaran 288 on at
    RE: Query extended range failure: x is not a valid datasource.field pair near pos

    Hi Sergei,

    yes I've seen. This is the query that causes error.

    SELECT FIRSTFAST FORUPDATE * FROM CustInvoiceJour(CustInvoiceJour) 
    WHERE ((((UserId == "test") || (SalesTable.SalesPoolId == "")))) 
    EXISTS JOIN FORUPDATE 'x' FROM CustInvoiceTrans(CustInvoiceTrans_1)
     WHERE CustInvoiceJour.SalesId = CustInvoiceTrans.SalesId AND ((Qty>0)) 
     JOIN FORUPDATE FROM SalesTable(SalesTable_1) ON CustInvoiceJour.SalesId = SalesTable.SalesId 
     OUTER JOIN FORUPDATE FROM UserMappingSalesPool(UserMappingSalesPool_1) 
     ON SalesTable.SalesPoolId = UserMappingSalesPool.SalesPoolId

    My condition in query filter is something like : userId ='x' or salestable.salespool =''

    If I cannot use tablestr how can I say salestable.salespool?

  • Sergei Minozhenko Profile Picture
    Sergei Minozhenko 23,083 on at
    RE: Query extended range failure: x is not a valid datasource.field pair near pos

    Hi Nastaran,

    Do you see the whole query after applying the extended range?

    Also, you should not use tableStr() in extended ranges, but you should use QueryBuildDataSource name as it's used as alias in query.

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,835 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,526 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans