Hi experts,
I need to join SMAServiceOrderTable or SMAServiceOrderLine Table with ProjInvoiceJour Table. I found out that there is no direct relation between these tables but I found that I can join SMAServiceOrderTable with ProjTable and then join ProjTable with ProjInvoiceTable and then finally with ProjInvoiceJour but I am getting multiple records. I need to get one unique record against that.
Basically my requirement is to add few fields of SMAServiceOrderTable in ProjInvoiceJournal form which contains ProjInvoiceJour table as a datasource.
Here is the SQL Query:
Select * from SMAServiceOrderTable as s1
join ProjTable as p1
on s1.ProjId = s1.ProjId
join ProjInvoiceTable as p2
on p1.PROJID = p2.PROJINVOICEPROJID
join PROJINVOICEJOUR as p3
on p2.PROJINVOICEPROJID = p3.PROJINVOICEPROJID
where p3.PROJINVOICEID = ''
Your response would be highly appreciated.
Hi Mohit,
Thank you for your response.
Yes, I have gone through that link. I also tried that in Insert event of ProjInvoiceJourTable but it's not working.
select ProjInvoiceId, TransId, Qty from invoiceLinesUnion where invoiceLinesUnion.ProjInvoiceId == projInvoiceJour.ProjInvoiceId;
if(invoiceLinesUnion)
{
select ProjTransId,ServiceOrderId from serviceOrderLine where serviceOrderLine.ProjTransId == invoiceLinesUnion.TransId;
if(serviceOrderLine)
{
projInvoiceJour.SMAServiceOrderId = serviceOrderLine.ServiceOrderId;
Info(strFmt("ServiceOrderId: %1", serviceOrderLine.ServiceOrderId));
}
}
Can you tell me on which event should I call this code?
Regards,
Arif Hussain
Hi, This has been discussed before.
Have you checked this thread.
community.dynamics.com/.../x-relation-from-service-order-header-link-to-project-invoice-journal
André Arnaud de Cal...
291,965
Super User 2025 Season 1
Martin Dráb
230,817
Most Valuable Professional
nmaenpaa
101,156