web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / SysComputedColumn meth...
Finance | Project Operations, Human Resources, ...
Answered

SysComputedColumn methods - how to join 2 tables to a view and return a value

(0) ShareShare
ReportReport
Posted on by Lakshmi Karambakkam 1,307
Hi,
 
To write a view method, I have to join 2 tables to a view. I cannot create another view for 2 tables as the two tables are inventtrans and inventtransorigin.
 
I want to write methods to avoid performance issues.
 
How to write a SysComputedColumn method for this view?
 
Lakshmi
I have the same question (0)
  • Martin Dráb Profile Picture
    Martin Dráb 238,828 Most Valuable Professional on at
    Please tell us more about your requirement and the problem you're struggling with. Also, your statement that the tables are InventTrans and InventTransOrigin doesn't explain why you can't create a view.
  • Lakshmi Karambakkam Profile Picture
    Lakshmi Karambakkam 1,307 on at
    if another view is created using inventtrans tables and attached to the first view, the form is opening after 30 minutes. inventtrans is such a huge table, so there is a performance issue.
    To remove performance issue, I wanted to write view methods.
     
    What is the syntax for joining two tables using syscomputed columns?
  • Martin Dráb Profile Picture
    Martin Dráb 238,828 Most Valuable Professional on at
    You seem to be saying that you need InventTrans, that using InventTrans is too slow you solve the problem by using a computed column. But that seems to solve nothing; you'll still need InventTrans there.
     
    If the query is the same, it's irrelevant whether you run it in a view or in a computed column. And if you want to use a different query, then you can do it in a view as well. But in fact, doing it in a computed column would be worse for performance, because you'd end up with a subquery for each row instead of a simple join.
     
    Joining InventTrans and InventTransOrigin doesn't normally takes 30 minutes. Instead of just giving up, find the bug you have there. First of all, make sure that the relation is correct.
     
    If you aren't aware of it, note that you can open your view definition in SQL Server Management Studio and check whether SQL code there is what you want. You can also run the code and investigate the execution plan and you try making changes to the query and test them there.
  • Bharani Preetham Peraka Profile Picture
    Bharani Preetham Pe... 3,634 Moderator on at
    Your requirement is not very clear. If the form is having performance issues you can give some range before the form opens. But you missed giving us the complete requirement. InvenTrans and InventTransOrigin tables joining with a view doesn't give us full requirement. 
     
    As Martin suggested there can be some other reason like let's say some display methods or some other code in execute query or some database transaction related code. So please give us full requirement here.
  • Lakshmi Karambakkam Profile Picture
    Lakshmi Karambakkam 1,307 on at
    Martin,
     
    Could you guide me the steps of how to open view definition in SQLserver management studio and tips that will help me to find errors and fix these relations?
     
    Lakshmi
  • Layan Jwei Profile Picture
    Layan Jwei 8,165 Super User 2026 Season 1 on at
    Hi Lakshmi,
     
    Open SQL server, expand AxDB node, expand views node, look for dbo.YourView. When you find it, right click on it and click view designer.
    Then take the SQL statement run it. Maybe also share it with us here to help you
     
    Thanks,
    Layan Jweihan
  • Lakshmi Karambakkam Profile Picture
    Lakshmi Karambakkam 1,307 on at
    Hi,

    My primary table is SalesPackingSlipTrackingInformation which has packingslip, trackingNumber and salesid.
     
    PackingSlipId TrackingNumber SalesID
    Pack1 Track1 SalesID1
    Pack2 Track1 SalesID1
    Pack3 Track2 SalesID1
    Pack4 Track3 SalesID1
    Pack5 Track4 SalesID2
    Pack6 Track5 SalesID2

    I created a  view to add InvoiceID and InvoiceDate fields for every record in SalesPackingSlipTrackingInformation table. I also have to add some fields from CustPackingSlipJour related toPackingSlipid.

    To add these fields, I created a view as sql query below.

    select SALESPACKINGSLIPTRACKINGINFORMATION.recid, SALESPACKINGSLIPTRACKINGINFORMATION.trackingnumber, inventtrans.packingslipid
    from SALESPACKINGSLIPTRACKINGINFORMATION 
     join custpackingslipjour 
    on SALESPACKINGSLIPTRACKINGINFORMATION.salesID = custpackingslipjour.SALESID
    and SALESPACKINGSLIPTRACKINGINFORMATION.PACKINGSLIPID = custpackingslipjour.PACKINGSLIPID
    and SALESPACKINGSLIPTRACKINGINFORMATION.DELIVERYDATE = custpackingslipjour.DELIVERYDATE
    left outer join inventtrans on SALESPACKINGSLIPTRACKINGINFORMATION.packingslipid = inventtrans.packingslipid
    left outer join RDSdeliveryTrackingtable on SALESPACKINGSLIPTRACKINGINFORMATION.TRACKINGNUMBER = RDSdeliveryTrackingtable.TRACKINGNUMBER
    and SALESPACKINGSLIPTRACKINGINFORMATION.RDSCARRIERCODE = RDSdeliveryTrackingtable.carriercode
    group by SALESPACKINGSLIPTRACKINGINFORMATION.recid, SALESPACKINGSLIPTRACKINGINFORMATION.trackingnumber, inventtrans.packingslipid

    The above query runs faster. But when I try to add other fields like InvoiceID and InvoiceDate, fields related to CustPackingSlipJour to View that are not part of group by fields, the view is throwing error that they are not part of group by fields.

    How to add non group by fields without performance issues?
     
     
    Lakshmi
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 238,828 Most Valuable Professional on at
    If you use group by, the only fields that will have a value defined are the fields you group by and those you aggregate (such as using count() function). Trying to use other fields is wrong.
     
    I guess you want to group by these fields too.
     

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

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Congratulations to the February Top 10 Community Leaders

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 517 Super User 2026 Season 1

#2
Giorgio Bonacorsi Profile Picture

Giorgio Bonacorsi 440

#3
Adis Profile Picture

Adis 266 Super User 2026 Season 1

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans