Share
ReportHI Team.
Does enable custom query works for a data entity whose data source is a View ? if Yes please provide me an example.
thanks in advance.
You’re offline. This is a read only version of the page.
50
HI Team.
Does enable custom query works for a data entity whose data source is a View ? if Yes please provide me an example.
thanks in advance.
Hi vini_a,
For BYOD I recommend splitting this entity to simpler entities to avoid performance issues. And view can be created as SQL view in Azure SQL DB.
Hi vini_a,
Does your view work properly? Because you join independently CUSTINVOICETRANS and PROJINVOICEITEM which can have multiple rows in each per one CUSTTRANS and it will not work correctly in this case.
How are you planning to use it? (integration or BYOD).
View is joined by outer join here is snippet for sql query for View
FROM CUSTTABLE T1 CROSS JOIN CUSTTRANS T2
LEFT OUTER JOIN PROJINVOICEJOUR T3 ON(((( T2.VOUCHER = T3.LEDGERVOUCHER) AND ( T2.DATAAREAID = T3.DATAAREAID)) AND ( T2.PARTITION = T3.PARTITION))
AND ((( T2.INVOICE = T3.PROJINVOICEID) AND ( T2.DATAAREAID = T3.DATAAREAID)) AND ( T2.PARTITION = T3.PARTITION))) LEFT OUTER JOIN LEDGERJOURNALTRANS T4
ON((( T2.VOUCHER = T4.VOUCHER) AND ( T2.DATAAREAID = T4.DATAAREAID)) AND ( T2.PARTITION = T4.PARTITION)) LEFT OUTER JOIN CUSTINVOICEJOUR T5
ON(((( T2.INVOICE = T5.INVOICEID) AND ( T2.DATAAREAID = T5.DATAAREAID)) AND ( T2.PARTITION = T5.PARTITION)) AND ((( T2.VOUCHER = T5.LEDGERVOUCHER)
AND ( T2.DATAAREAID = T5.DATAAREAID)) AND ( T2.PARTITION = T5.PARTITION))) LEFT OUTER JOIN PROJINVOICEITEM T6 ON(((( T3.PROJINVOICEID = T6.PROJINVOICEID)
AND ( T3.DATAAREAID = T6.DATAAREAID)) AND ( T3.PARTITION = T6.PARTITION)) AND ((( T3.INVOICEDATE = T6.INVOICEDATE) AND ( T3.DATAAREAID = T6.DATAAREAID))
AND ( T3.PARTITION = T6.PARTITION))) LEFT OUTER JOIN PROJINVOICEITEMDETAIL T7 ON((( T6.RECID = T7.INVOICEREFRECID) AND ( T6.DATAAREAID = T7.DATAAREAID))
AND ( T6.PARTITION = T7.PARTITION)) LEFT OUTER JOIN CUSTINVOICETRANS T8 ON(((((( T5.SALESID = T8.SALESID) AND ( T5.DATAAREAID = T8.DATAAREAID))
AND ( T5.PARTITION = T8.PARTITION)) AND ((( T5.INVOICEID = T8.INVOICEID) AND ( T5.DATAAREAID = T8.DATAAREAID)) AND ( T5.PARTITION = T8.PARTITION)))
AND ((( T5.INVOICEDATE = T8.INVOICEDATE) AND ( T5.DATAAREAID = T8.DATAAREAID)) AND ( T5.PARTITION = T8.PARTITION)))
AND ((( T5.NUMBERSEQUENCEGROUP = T8.NUMBERSEQUENCEGROUP) AND ( T5.DATAAREAID = T8.DATAAREAID)) AND ( T5.PARTITION = T8.PARTITION)))
LEFT OUTER JOIN SALESTABLE T9 ON((( T8.SALESID = T9.SALESID) AND ( T8.DATAAREAID = T9.DATAAREAID)) AND ( T8.PARTITION = T9.PARTITION))
Hi vini_a,
Is it union view or could you explain how you join source between each others?
Following is error i get when i try copy direct query and try sync DB. if try same query in View and Build and sync they sync good.
VIew based on CustTable/CustTrans/ProjInvoiceJour/CustInvoiceJour.
Microsoft.Dynamics.AX.Framework.Database.Synchronize.DataEntitySyncException: Microsoft.Dynamics.AX.Framework.Database.Synchronize.DataEntitySyncException: Failed to parse data entity view queries for following data entities: CustInvoiceLinesEntityV2.
at Microsoft.Dynamics.AX.Framework.Database.Tools.DataEntityTriggerSync.RunSync()
at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.DataEntityTriggerSync()
at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.FullSync()
at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.RunSync()
at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.Run(String metadataDirectory, String sqlConnectionString, SyncOptions options)
Process: syncengine.exe exited with code -1.
Hi
Can you share exact error? What is the structure of your view?
Hi vini_a,
CT can be activated only for table sources. If you using a view as a source for data entity consider copying of view structure to entity instead of using view directly.
Hi ,
Basically we trying make our entity incremental push, but data source for entity is a View. so i made following changes . but i see error says
"Incremental push is not supported for entity CustInvoiceLinesEntityV2, as change tracking query is not enabled on it."
Do you see any issue with Following code.
FYI: I checked Custtrans table which has change tracking enabled.
public static Query defaultCTQuery()
{
Query q;
q = new Query();
QueryBuildDataSource qbd = q.addDataSource(tablename2id('CustInvoiceLinesViewV2'));
qbd = qbd.addDataSource(tablename2id('CustTrans'));
qbd.relations(false);
//qbd = qbd.addDataSource(tablename2id('ProjInvoiceJour'));
//qbd.relations(false);
//qbd = qbd.addDataSource(tableName2Id('LedgerJournalTrans'));
//qbd.relations(false);
qbd.addLink(fieldName2Id(tableName2Id('CustTrans'),'Invoice'),fieldName2Id(tableName2Id('CustInvoiceLinesViewV2'),'Invoice'));
return q;
}
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156
All responses (
Answers (