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

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / D365 - View with table...
Microsoft Dynamics AX (Archived)

D365 - View with tables "save by Company" = No and Yes (DirPartyTable + CustTable)

(0) ShareShare
ReportReport
Posted on by Sebastian Aurand 1,650

Hello,

I need to join two tables in a view. One of them is company specific (CustTable) and one is not (DirPartyTable). As the result I want to have a "company only" - view, where the displayed records belong to the current (log in) company = CustTable.DataAreaId.

How could I achieve this behaviour?

"Simply" joining (by DirPartyTable.RecId = CustTable.Party) the two tables in a view results in multiple entries if the DirPartyTable is linked to more CustTable in different companies:

Join.jpg

5756.VIEW.jpg

Thanks in advance

Sebastian

*This post is locked for comments

I have the same question (0)
  • Verified answer
    Sohaib Cheema Profile Picture
    Sohaib Cheema 49,438 User Group Leader on at

    Craete a query and keep the CustTable at top and then drag the Party Table under it, Inner Join will make sure to get only related records from child table. this seems to be one way to get only company specific records.

    Once you are done with query, you can use it to build a view

  • Sebastian Aurand Profile Picture
    Sebastian Aurand 1,650 on at

    It's the table browser

  • Sohaib Cheema Profile Picture
    Sohaib Cheema 49,438 User Group Leader on at

    what you want? you want to restrict data on table browser or in a view? what is table name that you are browsing?

  • Sebastian Aurand Profile Picture
    Sebastian Aurand 1,650 on at

    I was browsing my view "test" with the table browser. The view contains 4 fields: CustTable.AccountNum, CustTable.DataAreaId, DirPartyTable.PartyNumber and DirPartyTable.Name.

    I tried your solution and it worked for me. I guessed that there has to be something like a property in the view to make it company sensible or not. Nevertheless it's working now.

    Thanks for your fast help.

  • Douglas Noel Profile Picture
    Douglas Noel 3,905 on at

    Hi Sebastian,

    you wanna have kernel automismn on filtering DA on regular tabkes in your case?

    Do it in the only possible way. You have to have a regular table on top to have the where DA condition in ALL cases without adding filters programmatically.

    This is not the case in your example and you can't or don't want to restructure the view?

    But, I love it, let's storm the brain.....

    Build another view with

    any local table with exactly one row (potential candidates xxxxxxParameters) reliable.

    attach your exiting view (will give an innerjoin) to the reliable one record per company master, relation da master, da cust)

    Only use fields from existing view.

    Disadvantage: Each DirParty has technicalky the same recid (the one from master)

    Maybe this?

    regards Douglas

    Remember three times 10 is 42 (101010)

  • Sohaib Cheema Profile Picture
    Sohaib Cheema 49,438 User Group Leader on at

    @Sebastian Aurand,

    when views are build/synchronized the definition is updated in SQL and you can watch what query goes to SQL side.

    You can experience this by switching the positions of the tables in query.

  • santhosh kumar 1212 Profile Picture
    santhosh kumar 1212 215 on at

    You can check the standard view :DimAttributeCustTable  (it use CustTable as primary table and DirPartyTable is joinded to CustTable(BackingEntity).

    Just duplicate and remove unnecessary fields and methods. 

    Add required field from CustTable & DirPartyTable datasources to Fields.

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans