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.