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) / For views relation fie...
Microsoft Dynamics AX (Archived)

For views relation fields in query can not come from base or derived tables.

(0) ShareShare
ReportReport
Posted on by Annette Theissen 1,653

Hello,


does anybody know what this means: "For views relation fields in query can not come from base or derived tables."?


I'm creating a query: InventTable -> outer joining VendTable -> inner joining DirPartyTable.

Relations are through a new vendField on inventTable and the standard relation with Party = RecId for joining DirParty to Vend.

Now I want to use this as the query for a view, but receive the error mentioned above.

Any tipps and hints are appreciated!

Thanks and have a nice weekend!

Annette

*This post is locked for comments

I have the same question (0)
  • Verified answer
    Community Member Profile Picture
    Community Member on at

    Annette

    this query works with another relation between InventTable and VendTable, so the krux seems to be there.

    Which fields with what properties are you joining exatly?

    Hans-Peter

  • Annette Theissen Profile Picture
    Annette Theissen 1,653 on at

    Hi Hans-Peter,

    thank you - you're absolutely right, it was a totally different issue on the query. Had introduced the problem in a previous step, but not noticed.

    I needed your push to step back a bit and analyze in more detail :-).

    The minimal scenario to cause the error is:

    • Create a query
    • Add EcoResProductTranslation as datasource
    • Inner join CompanyInfo to EcoResProductTranslation
    • Add relation EcoResProductTranslation.LanguageId = CompanyInfo.LanguageID
    • Create a view with this query
    • Compile view
    • See error :-)

    I still do not understand why this is a bad thing to do. But at least I now see something that I haven't seen work somewhere else in AX!

    BTW: My goal was to create a view that automatically shows the item name in the company's language. There is a standard AX view (EcoResProductTranslations) that tries something similar, but goes back to table systemParameters. But that doesn't help in my scenario - and most probably not in any other multi-language environment.

    Anyhow, thanks for getting me out of the wrong branch of the query to find the problem!!! I'll take a workaround now ...

    Kind regards,

    Annette

  • Community Member Profile Picture
    Community Member on at

    Tschau Annette

    The source of the error message comes from the point 1):

    1) The field "LanguageId" is not located in the "CompanyInfo" table but in the derived table "DirPartyTable".

    (CompanyInfo extends "OMInternalOrganization" which extends "DirOrganizationBase" which extends "DirPartyTable")

    2) The result of the above mentioned query would be unuseful because there is an n:n relation over the "LanguageId" fields.

    Have a nice week

    Hans-Peter

  • Annette Theissen Profile Picture
    Annette Theissen 1,653 on at

    Hi Hans-Peter,

    hm, yes, it comes from CompanyInfo, that's what I wrote ... but why would it be an n:n relation? Maybe my example was too small ...

    The second field in the relation constraint was supposed to be

    InventTable.dataAreaId == CompanyInfo.DataArea

    I would be grateful if you could explain what you mean.

    And even more grateful for tipps on how to phrase this in a query ;-). I mean, after all we KNOW the current company (inventTable.dataAreaId), there is only one current company ..

    Thanks!!!

    Annette

  • Verified answer
    Community Member Profile Picture
    Community Member on at

    Hello

    I tried to say the following:

    1) "CompanyInfo" is not a real table in the SQL database, but a logical construct in AX; the "physical" table in which the field "LanguageId" resides is "DirPartyTable".

    Because views will be written back into the SQL database when compiled, you cannot use joins with fields in derived tables. (the SQL database does not know the table "CompanyInfo".)

    2) Because the values of the field "LanguageId" will occure more than once in both tables, this is a n:n relation.

    I did not understand exactly, what your intention is.

    Hans-Peter

  • Annette Theissen Profile Picture
    Annette Theissen 1,653 on at

    Ah ok, so the emphasis is not on _fields_ but on _derived tables_. Didn't get that.

    (still wonder why it says base tables in the error message, but well).

    I just want a simple table (view) listing itemId and itemName in the company's language. That's why I wanted to join companyInfo ...

    So, what might work (from a logical point) then is adding not only the constraint (relation) on dataAreaId, but also InstanceRelationType to limit to companyInfo records.

    I guess I'll stick with my workaround and take this as another example why I don't like how "derived tables" are implemented :-)

    Thanks a million for your help!!!

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