web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Invalid source query s...
Finance | Project Operations, Human Resources, ...
Answered

Invalid source query specified for insert_Recordset.

(1) ShareShare
ReportReport
Posted on by AbdullahAhmed_ 513
I use this query and I get this error: 'Invalid source query specified for insert_Recordset.'
 
        XDSSalesTable               xDSSalesTable;
        Query                       q;
        QueryBuildDataSource        qbds;
        QueryBuildDataSource        qbds_Joined;
        QueryBuildFieldList         qbfl;
        Map                         fieldMapping;

        q               = new Query();
        qbds            = q.addDataSource(tableNum(SalesTable));
        qbds.addSelectionField(fieldNum(SalesTable,SalesId));
        qbds.addRange(fieldNum(SalesTable,SalesStatus)).value('3');
        qbfl            = qbds.fields();
        qbfl.dynamic(QueryFieldListDynamic::No);
       

        qbds_Joined = q.dataSourceTable(tableNum(SalesTable)).addDataSource(tableNum(XDSCustTable));
        qbds_Joined.addLink(fieldNum(SalesTable,InvoiceAccount),fieldNum(XDSCustTable,CustAccount));
        qbds_Joined.addRange(fieldNum(XDSCustTable,UserId)).value('TestUser');

        fieldMapping    = new Map(Types::String,Types::Container);

        ttsBegin;
        fieldMapping.insert(fieldStr(XDSSalesTable,SalesId)      ,[qbds.uniqueId(),fieldStr(SalesTable,SalesId)]);
        Query::insert_recordset(xDSSalesTable,fieldMapping,q);
                    
        update_recordset xDSSalesTable
            setting UserId = 'TestUser'
            where xDSSalesTable.UserId == '';
        ttscommit;
 
and this is the value of the query variable q :
{Query  object c3143e70: 
SELECT SalesId FROM SalesTable(SalesTable_1) 
    WHERE (((SalesStatus == "3")) 
JOIN CustAccount FROM XDSCustTable(XDSCustTable_1) 
   ON SalesTable.InvoiceAccount = XDSCustTable.CustAccount 
   AND ((UserId = N'TestUser'))}
which returns result when written in sql server!
I have the same question (1)
  • Martin Dráb Profile Picture
    Martin Dráb 238,828 Most Valuable Professional on at
    It's a pity that you ignore the code improvements I gave you in your previous thread and you keep using your all code with all the useless statement (e.g. clearing ranges on a newly added data sources), cryptic variable names and so on. You make your code difficult to understand for everyone, including (and most importantly) yourself. But I'm not going to fix it again if you aren't interested.
     
    The first thing I would fix is removing the duplicate call of addLink(). Maybe it's not the cause of your current problem, but it's wrong for sure, therefore you should address it in either case.
     
    Another problem I see is that you're comparing SalesStatus with string values, but SalesStatus is an enum (= integer in database).
  • Martin Dráb Profile Picture
    Martin Dráb 238,828 Most Valuable Professional on at
    Isn't it wrong that your query select fields from XDSCustTableRegular? Should you use an exists join?
  • AbdullahAhmed_ Profile Picture
    AbdullahAhmed_ 513 on at
    - The classes and tables are required to have DDV as a prefix so, that makes the names weird.
    - If I removed addLink(), how would the 2 datasources join... I mean what would be the join condition if I didn't specify it?
    - SalesStatus is stored as numbers in the database and I checked the query and it turns the required result.
     
    - What I'm trying to acheive is: I have a security policy that has CustTable as a primary table and SalesTable as constrained.
    In my setup form there's a condition that filters CustTable and another condition that filters SalesTable and I have like a Tmp table (XDSSalesTable) that I'm trying to insert the records into...
     
    Instead of inserting SalesId into (XDSSalesTable) depending on the condition that filters SalesTable:
    ((SalesStatus == "1") ||(SalesStatus == "3"))
    which returns most of the SalesId in the system (+700k records)... when I join SalesTable with another Tmp that has CustAccount of the desired customers (XDSCustTable)  Then I only get (60 records)
  • Martin Dráb Profile Picture
    Martin Dráb 238,828 Most Valuable Professional on at
    I didn't say that you should remove addLink() completely. I'm saying that calling addLink() twice with exactly the same parameters is useless. You should throw away one of these calls.

    And by the way, you should define the relation on a table and then merely call relations(true), rather than resorting to addLink().
  • AbdullahAhmed_ Profile Picture
    AbdullahAhmed_ 513 on at
    Sorry I didn't notice the duplication.. I updated the code to be more readable.
     
    I still don't know why I'm facing this error... when I use a single datasource the Query::insert_recordset works fine, but when I join another datasource it's throwing the error!
  • Martin Dráb Profile Picture
    Martin Dráb 238,828 Most Valuable Professional on at
    Let me repeat: isn't it wrong that you return fields from XDSCustTable? Have you tried my suggestion to avoid that by using an exists join instead of the inner join?

    For example:
    Query q = new Query();

QueryBuildDataSource salesTableDs = q.addDataSource(tableNum(SalesTable));
qbds.fields().dynamic(QueryFieldListDynamic::No);
qbds.addSelectionField(fieldNum(SalesTable, SalesId));
qbds.addRange(fieldNum(SalesTable, SalesStatus)).value(queryValue(SalesStatus::Delivered));

QueryBuildDataSource xdsCustTableDs = salesTableDs.addDataSource(tableNum(XDSCustTable));
xdsCustTableDs.joinMode(JoinMode::ExistJoin);
xdsCustTableDs.relations(true);
xdsCustTableDs.addRange(fieldNum(XDSCustTable, UserId)).value(queryValue('TestUser'));
    Please notice also the correct way of setting an enum range value.
  • AbdullahAhmed_ Profile Picture
    AbdullahAhmed_ 513 on at
    Yes I don't want fields from XDSCustTable.. I just want to apply a range on SalesId coming from SalesTable.
     
    The addRelation(true) doesn't specify the relation between SalesTable and  XDSCustTable 
    and the query is like this:
    SELECT SalesId FROM SalesTable(SalesTable_1) 
    WHERE (((SalesStatus == "3")) 
EXISTS JOIN *FROM XDSCustTable(XDSCustTable_1) 
   where((UserId = N'TestUser'))}
    and still the same error!
  • Martin Dráb Profile Picture
    Martin Dráb 238,828 Most Valuable Professional on at
    I said that you should define the relation on a table and then merely call relations(true). If relations(true) doesn't add a join condition, it likely means that you forgot to add the relation (on XDSCustTable).
  • AbdullahAhmed_ Profile Picture
    AbdullahAhmed_ 513 on at
    Thank you, that makes sense.. I don't prefer to use relations(true) or the use relations property on the datasource level because there might be more than 1 relation between 2 tables and the system might not pick the right one that fit my condition or in this case when I didn't specify a relation on XDSCustTable table level and the query didn't get affected...
    That's why I prefer to use addLink() or specify the relation fields on the table level instead of setting use relation property to true.
     
    Anyway, I want to understand the error.. why it's happening? 
    'Invalid source query specified for insert_Recordset.' why the source is invalid? It works well if it's only 1 datasource and when I join another datasource it's now invalid??
  • Suggested answer
    Saif Ali Sabri Profile Picture
    Saif Ali Sabri 2,354 Moderator on at

    The error 'Invalid source query specified for insert_Recordset' occurs because insert_recordset does not support complex queries with joined tables or linked data sources. The insert_recordset command expects a query with a single data source, so adding a joined data source (qbds_Joined) in this case is invalid.

    1. Use a simple select statement instead of insert_recordset for inserting records with joined conditions.
    2. Create a temporary table to store the query results before inserting them into the XDSSalesTable.

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

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Congratulations to the February Top 10 Community Leaders

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 517 Super User 2026 Season 1

#2
Giorgio Bonacorsi Profile Picture

Giorgio Bonacorsi 440

#3
Adis Profile Picture

Adis 266 Super User 2026 Season 1

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans