Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Supply chain | Supply Chain Management, Commerce / Query extended range f...
Supply chain | Supply Chain Management, Commerce
Answered

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

(0) ShareShare
ReportReport
Posted on by Nastaran 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,089 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,089 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,089 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.

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans