Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Query::Insert_RecordSe...
Microsoft Dynamics AX (Archived)

Query::Insert_RecordSet field is not filled

(0) ShareShare
ReportReport
Posted on by XB 1,867

Hi all

I created next code and when i call it from a class is not fill ProdId field on temporal table.

Query q = new Query();
QueryBuildDataSource prodTableQbds;
QueryBuildDataSource prodBOMQbds;
QueryBuildFieldList prodTableFieldList;
QueryBuildFieldList prodBOMfieldList;

Map fieldMapping;

prodTableQbds = q.addDataSource(tableNum(ProdTable));
prodBOMQbds = prodTableQbds.addDataSource(tableNum(ProdBOM));
prodBOMQbds.joinMode(JoinMode::InnerJoin);
prodBOMQbds.relations(true);

prodTableFieldList = prodTableQbds.fields();
prodTableFieldList.addField(fieldNum(ProdTable, ProdId));
prodTableFieldList.addField(fieldNum(ProdTable, bomId));
prodTableFieldList.dynamic(QueryFieldListDynamic::No);

prodBOMfieldList = prodBOMQbds.fields();
prodBOMfieldList.addField(fieldNum(ProdBOM, BOMId));
prodBOMfieldList.addField(fieldNum(ProdBOM, ItemId));
prodBOMfieldList.dynamic(QueryFieldListDynamic::No);

prodTableQbds.addGroupByField(fieldNum(ProdTable, ProdId));
prodTableQbds.addGroupByField(fieldNum(ProdTable, bomId));
prodBOMQbds.addGroupByField(fieldNum(ProdBOM, BOMId));
prodBOMQbds.addGroupByField(fieldNum(ProdBOM, ItemId));

prodTableQbds.addRange(fieldNum(ProdTable, ProdStatus)) .value(queryValue(ProdStatus::StartedUp));
prodTableQbds.addRange(fieldNum(ProdTable, ProdPoolId)) .value(queryValue(prodPoolId));
prodBOMQbds.addRange(fieldNum(ProdBOM, ProdFlushingPrincip)).value(queryValue(ProdFlushingPrincipBOM::Manual));
prodBOMQbds.addRange(fieldNum(ProdBOM, EndConsump)) .value(queryValue(NoYes::No));

if (ProdId)
{
prodTableQbds.addRange(fieldNum(ProdTable, ProdId)).value(queryValue(ProdId));
}
if (filterSpice)
{
prodTableQbds.addRange(fieldNum(ProdTable, DlvDate)) .value(queryRange(fromDate, toDate));
prodTableQbds.addRange(fieldNum(ProdTable, ProductionLineId)).value(queryValue(ProductionLineId));
}
if (ItemId)
{
prodBOMQbds.addRange(fieldNum(ProdBOM, ItemId)).value(queryValue(ItemId));
}

fieldMapping = new Map(Types::String, Types::Container);
fieldMapping.insert(fieldStr(ProdBOMTmp, ProdId), [prodTableQbds.uniqueId(), fieldStr(ProdTable, ProdId)]);
fieldMapping.insert(fieldStr(ProdBOMTmp, BOMId), [prodTableQbds.uniqueId(), fieldStr(ProdTable, BOMId)]);
fieldMapping.insert(fieldStr(ProdBOMTmp, ProdTableBOMId), [prodBOMQbds.uniqueId(), fieldStr(ProdBOM, BOMId)]);
fieldMapping.insert(fieldStr(ProdBOMTmp, ItemId), [prodBOMQbds.uniqueId(), fieldStr(ProdBOM, ItemId)]);

query::insert_recordset(ProdBOMTmp, fieldMapping, q);

if (ItemId)
{
prodBOMQbds.clearRange(fieldNum(ProdBOM, ItemId));
prodBOMQbds.addRange(fieldNum(ProdBOM, bomId)).value(queryValue(ItemId));
}

query::insert_recordset(ProdBOMTmp, fieldMapping, q);

*This post is locked for comments

  • Suggested answer
    XB Profile Picture
    XB 1,867 on at
    RE: Query::Insert_RecordSet field is not filled

    Finally I solve the problem replacing ProdId for RecId.

    fieldList = qbds.fields();
    fieldList.addField(fieldNum(ProdBOMAllergenView, ProdId));
    fieldList.addField(fieldNum(ProdBOMAllergenView, ProdTableRecId));
    fieldList.addField(fieldNum(ProdBOMAllergenView, bomId));
    fieldList.addField(fieldNum(ProdBOMAllergenView, ProdTableBOMId));
    fieldList.addField(fieldNum(ProdBOMAllergenView, ItemId));
    fieldList.dynamic(QueryFieldListDynamic::No);

    qbds.addGroupByField(fieldNum(ProdBOMAllergenView, ProdId));
    qbds.addGroupByField(fieldNum(ProdBOMAllergenView, ProdTableRecId));
    qbds.addGroupByField(fieldNum(ProdBOMAllergenView, bomId));
    qbds.addGroupByField(fieldNum(ProdBOMAllergenView, ProdTableBOMId));
    qbds.addGroupByField(fieldNum(ProdBOMAllergenView, ItemId));

    ......

    fieldMapping = new Map(Types::String, Types::Container);
    fieldMapping.insert(fieldStr(ProdBOMTmp, ProdTableRecId), [qbds.uniqueId(), fieldStr(ProdBOMAllergenView, ProdTableRecId)]);
    fieldMapping.insert(fieldStr(ProdBOMTmp, ProdId), [qbds.uniqueId(), fieldStr(ProdBOMAllergenView, ProdId)]);
    fieldMapping.insert(fieldStr(ProdBOMTmp, BOMId), [qbds.uniqueId(), fieldStr(ProdBOMAllergenView, BOMiD)]);
    fieldMapping.insert(fieldStr(ProdBOMTmp, ProdTableBOMId), [qbds.uniqueId(), fieldStr(ProdBOMAllergenView, ProdTableBOMId)]);
    fieldMapping.insert(fieldStr(ProdBOMTmp, ItemId), [qbds.uniqueId(), fieldStr(ProdBOMAllergenView, ItemId)]);

    ...

    I have RecId but still not ProdId but works for me.

    Regards

  • XB Profile Picture
    XB 1,867 on at
    RE: Query::Insert_RecordSet field is not filled

    Thanks all for your answers.

    Before I create this post I tried to do some things to solve the problem.

    -I'm using a temporaly table and it's not the first time by the way I tried to use a regular table and I get the same result.

    -Query return data after this insert_recordset I loop temporaly table and three of four field are returned.

    -I tried to remove group by and I get the same result.

    If I take the sql comannd and do with a while select it return all fields but when I'll try on a insert_recordset prodId is not insert.

  • udaY-ch Profile Picture
    udaY-ch 4,624 on at
    RE: Query::Insert_RecordSet field is not filled

    You can try removing / commenting out the group by and give a try and If am correct, group by is not supported with Query::insert_recordset.

  • Martin Dráb Profile Picture
    Martin Dráb 230,235 Most Valuable Professional on at
    RE: Query::Insert_RecordSet field is not filled

    You must do some work on diagnostics, because you can't fix a problem unless you find it.

    First of all, does your query return any data?

    By the way, please use the </> button in the rich formatting view to paste source code. It preserves indentation, making code much easier to read.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Query::Insert_RecordSet field is not filled

    Are you using a temp type table to insert the records? It wont work if you do.

    Thanks,

    Praveen

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