Dynamics Community Forum Thread Details

I have been using insert_recordset approach but sometimes because of the query and mapping I face this issue.

Not sure how to handle this, previously when I faced this issue I just change the sequences of the datasources in my query and is got resolved but that query was having multiple child datasources in a single paren but now this is not the case.

Below is the source code im using.

final protected void buildFieldMap(Query _reportQuery, Map _fieldMap)
{
QueryBuildDataSource taxAuthorityAddressDS = _reportQuery.dataSourceTable(tableNum(TaxAuthorityAddress));
QueryBuildDataSource taxTransDS = _reportQuery.dataSourceTable(tableNum(TaxTrans));
QueryBuildDataSource taxTableDS = _reportQuery.dataSourceTable(tableNum(TaxTable));

_reportQuery.clearAllFields();

// Tax Authority Address fields
int taxAuthorityAddressUniqueId = taxAuthorityAddressDS.uniqueId();
QueryBuildFieldList taxAuthorityAddressFL = taxAuthorityAddressDS.fields();

taxAuthorityAddressFL.addField(fieldNum(TaxAuthorityAddress, TaxAuthority));
_fieldMap.insert(fieldStr(KGPSalesTaxCollectedSummaryTmp, TaxAuthority), [taxAuthorityAddressUniqueId, fieldStr(TaxAuthorityAddress, TaxAuthority)]);

taxAuthorityAddressFL.addField(fieldNum(TaxAuthorityAddress, Name));
_fieldMap.insert(fieldStr(KGPSalesTaxCollectedSummaryTmp, TaxAuthorityName), [taxAuthorityAddressUniqueId, fieldStr(TaxAuthorityAddress, Name)]);

// Tax Trans fields
int taxTransUniqueId = taxTransDS.uniqueId();
QueryBuildFieldList taxTransFL = taxTransDS.fields();

taxTransFL.addField(fieldNum(TaxTrans, ExemptTax));
_fieldMap.insert(fieldStr(KGPSalesTaxCollectedSummaryTmp, ExemptTax), [taxTransUniqueId, fieldStr(TaxTrans, ExemptTax)]);

taxTransFL.addField(fieldNum(TaxTrans, TaxAmountCur));
_fieldMap.insert(fieldStr(KGPSalesTaxCollectedSummaryTmp, TaxAmountCur), [taxTransUniqueId, fieldStr(TaxTrans, TaxAmountCur)]);

taxTransFL.addField(fieldNum(TaxTrans, TaxBaseAmountCur));
_fieldMap.insert(fieldStr(KGPSalesTaxCollectedSummaryTmp, TaxBaseAmountCur), [taxTransUniqueId, fieldStr(TaxTrans, TaxBaseAmountCur)]);

taxTransFL.addField(fieldNum(TaxTrans, TaxDirection));
_fieldMap.insert(fieldStr(KGPSalesTaxCollectedSummaryTmp, TaxDirection), [taxTransUniqueId, fieldStr(TaxTrans, TaxDirection)]);

int taxTableUniqueId = taxTableDS.uniqueId();
QueryBuildFieldList taxTableFL = taxTableDS.fields();

taxTableFL.addField(fieldNum(TaxTable, TaxPackagingSort));
_fieldMap.insert(fieldStr(KGPSalesTaxCollectedSummaryTmp, TaxPackagingSort), [taxTransUniqueId, fieldStr(TaxTable, TaxPackagingSort)]);



Query::insert_recordset(salesTaxCollectedSummaryTmp_taxAuth, _fieldMap, _reportQuery);

}

below is the query and the map I'm getting.

SELECT TaxAuthority, Name FROM TaxAuthorityAddress(TaxAuthorityAddress) 
JOIN  FROM TaxPeriodHead(TaxPeriodHead) ON TaxAuthorityAddress.TaxAuthority = TaxPeriodHead.TaxAuthority 
JOIN ExemptTax, TaxAmountCur, TaxBaseAmountCur, TaxDirection 
FROM TaxTrans(TaxTrans) ON TaxPeriodHead.TaxPeriod = TaxTrans.TaxPeriod 
AND ((TransDate>={ts '2021-01-01 00:00:00.000'} AND TransDate<={ts '2021-09-30 00:00:00.000'})) 
JOIN TaxPackagingSort FROM TaxTable(TaxTable) ON TaxTrans.TaxCode = TaxTable.TaxCode

{["ExemptTax"-><2>,"TaxAmountCur"-><2>,"TaxAuthority"-><2>,"TaxAuthorityName"-><2>,"TaxBaseAmountCur"-><2>,"TaxDirection"-><2>,"TaxPackagingSort"-><2>}

As you see above the sequences are not matching.
Please help me resolve this issue. Thanks in advance.