Hello everyone!
NOTE: I'm very unexperienced with Dynamics AX, please tell me anything that you think might help as I probably am just missing the correct approach to this problem.
When I type "crossCompany" I don't necessarily mean the keyword that is used in "select" statements.
It's kind of hard to explain my problem, but I'll do my best - might get a bit longer though.
Okay so what I'm trying to do is have Tables with specific data that are supposed to be stored once only for ALL companys in one Dynamics AX installation.
I kind of did find a solution for storing them, looking into them, etc by doing the following:
I created a company that is supposed to hold all "global" data, like Postal/ZIP codes and their corresponding city names for example. I called it "meta" and it should NOT contain anything else but the global data.
If you create ListPages/DetailsFormMaster forms its kind of easy to create a query that will search crossCompany so every company can look at them - once you double click the data in a ListPage or go to edit the data Dynamics AX will automatically change the company to "meta" for the details form, meaning its easy to manage that the data will not be stored in a different company by accident.
The big problem I'm having is this:
Let's assume the following, to make things a little more clear, as examples are easier to understand I think;
We are currently in the company "dat"
We have two tables: tableCitys and tableCustomers
tableCitys contains the Postal/ZIP Code as the primary key
tableCustomers uses the ZIP Code as a foreign key to relate to tableCitys
tableCitys ONLY contains data inside the company "meta"
tableCustomers ONLY contains data inside other companys than "meta" (like "dat")
If a user is requesting the customer list I still want my form to display the city, even though only the Postal/ZIP code is stored in the customer table.
To acheive this I tried to create a query in the AOT.
Now if I join the tables by adding the tableCustomers as the query datasource and add tableCitys as a table datasource for tableCustomers it does not work as I want it to (Query has AllowCrossCompany="Yes" and CrossCompanyAutoQuery="Yes" for the tableCitys_DataSource in the Form [I tried setting tableCustomers to CrossCompanyAutoQuery="Yes" too, but logically that isn't what I want and it doesn't work either])
What does the query give me as results?
If I use the setup I described above zero results are fetched.
However, I kind of got an idea of whats going on as I started addings customers to the "meta" company (for testing purposes only, this is not supposed to happen in a productive system).
It will actually display the customer-city joins that are inside the "meta" company (It will show these no matter what company I use currently)
So I think the crossCompany will actually force the select to look at all tables, but the join itself won't. So it only finds relations that are inside the company it's currently looking at. (I'm assuming, that the "select crossCompany" statement will kind of execute the select for each company and combine the results, instead of just leaving the "where" clause away which makes sure you only get results of the current company)
I managed to get the results I want by using X++ code.
I'll show you a very quick example of how I did it, then continue explaining.
while select * from tableCustomers
{
while select firstOnly crossCompany * from tableCitys
where tableCustomers.zipCode == tableCitys.zipCode
{
// Process here
info(strFmt(...));
}
}
I'm guessing this is not very well optimized, but its the easiest code to understand what I'm trying to do.
So instead of joining the tables, I did the 2nd select inside the first one, kind of like a loop inside a loop.
Now you might ask "well you got your desired results, what else do you want?".
Since I'm very unexperienced I don't know how I could possibly use this to implement it as a "DataSource" for Queries or Forms.
I'm looking for a method that results in what my code does, but implemented as a DataSource so it goes along with the standards of Dynamics AX and is easy to use.
I'm very confused to why I couldn't find anything about this topic, but I might just searched for the wrong keywords and I'm sorry if this has been asked already.
Thank you very very much in advance and have a nice day.
