You’re offline. This is a read only version of the page.
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
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!!!
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
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
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
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:
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
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
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,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (