Cross company table referencing a per company company table

(0) Share

Report

Posted on by Pete Alberts

3,542

Hi folks

Referencing a cross company table from a per company table is standard, used everywhere and straight forward.

However referencing a per company table from a cross company table is difficult and creates a lot of issues. I have a vague suspicion that the concept is logically flawed, but can't put my finger on it. I am looking for an answer or opinion on this - is it logically incorrect or okay? Another less important question is - why is it such a headache to work cross company? (I am not asking how, I am asking why.)

Here is an example:

If we create a custom mobile app, the table containing mobile user info (which the app references) should be cross company. Probably debatable, but let's assume that to be correct. Then if a mobile user needs site defaulting - we will need to create a DefaultSite field on the table, which is per company. So we will reference InventSite (per company) from XYZMobileUser (cross company).

Accomplishing this includes a lot of frustration. But the end result will either be: 1) Two fields on XYZMobileUser - InventSiteDataAreaId & InventSiteId or 2) InventSiteRecId. And then we also need a cross company lookup - which is an enormous crisis, but doable.

Thanks for your attention.

I have the same question (0)

All responses (9)

Answers (2)

Verified answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

You should have another company specific table for defining company specific settings for your mobile user. No need for special lookups, instead that data is set in the company where it's relevant.

Was this reply helpful? Yes No
Pete Alberts 3,542 on at

Like (0)

Report

Thanks for your reply Nikolaos

That is only an example. I did that over a year ago. I want to know if it the concept is ok in general? I have not found any implementation of that sort in the standard application.

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

Product master (EcoResProduct) is global and Released product (InventTable) is company specific. So there's one example.

Another different example is Worker (HcmWorker) and Employment (HcmEmployment). The Employment table is also global, but includes information about the company of the employment. In the user interface you will see the employment of the current company of your session.

And yes, this is the concept and that's how it's done. You need to have one record for each company, if you have company specific data. So you need a separate table for handling the company specific data.

Was this reply helpful? Yes No
Pete Alberts 3,542 on at

Like (0)

Report

InventTable and EcoResproduct is a company table referencing a cross company table.

HcmEmployment - The table's functionality can be reproduced by a per company HcmEmploymentV2 table (removing the legal entity reference). Meaning it is still a company table referencing a cross company table.

I said that the concept is used everywhere in the standard application. There are many more such examples. I'm asking about the other way around. A cross company table referencing a per company table.

Thank you for your patience with me.

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

It's a 1:n relationship and you can only define the reference from the company specific table to the cross company table. Just like you can't have a reference from CustTable to SalesTable.

So, your mobile users are global. But everything they handle (for example orders) is company specific. So, if you need some default values, you always take them from the company that is related to the transaction / process that they are working on.

Was this reply helpful? Yes No
Verified answer

André Arnaud de Cal... 300,896 Super User 2025 Season 2 on at

Like (0)

Report

Not sure what exactly you need to accomplish, but as long you have the InventSiteDataAreaId field as part of the relation, it could be a viable solution.

An example in the standard is InventModelGroupItem. This is a global table using local values from the InventModelGroup.

Was this reply helpful? Yes No
Pete Alberts 3,542 on at

Like (0)

Report

Hi André

I merely mentioned an example. I am asking about the concept. Thank you for providing an example in the standard application. I do not know anything about the functional details about those tables. But it is good proof of a fact I mentioned - implementing the concept is a headache (from a technical point of view). Open the table browser and try to supply a ModelGroupId value. You specify a value from the lookup and then it complains about the value not being valid. That is a side story - I am aware of the reasons behind this.

What I am asking, which I have mentioned a few times :) is about the functional logic behind this - is it flawed? I am questioning whether it makes sense.

I have another example - a colleague of mine reminded me of PRs. PR and lines are cross company, but references per company tables, such as VendTable. Once again - it is a headache. The application constantly notifies you that it is switching between companies when you are on the form. My colleague, with decades of experience, mentioned that he has not come across a single implementation where the cross company behaviour is actually used. On the contrary - customers often ask for a new form which behaves per company. And with my lack of knowledge on SCM, I still feel confident enough to say that the concept is per company, not cross company. The table is flawed. I mean, if I want to buy a stapler, that purchase request is relevant to the company I am working in. It has nothing to do with the other companies my employer owns.

So then I feel we are still without an example - because I feel PRs were incorrectly implemented and I do not know anything about InventModelGroup (I suspect that it will be that same sort of story - the CC table should've been per company).

My hypothesis is that the concept is indeed incorrect. If you need to reference a per company table from a cross company table, then you should rethink your table structure. So back to my own example of Mobile Users referencing an InventSite. The table should've been per company, or I should've added a buffer table in between. But referencing a site from a cross company table does not make sense.

Was this reply helpful? Yes No
Pete Alberts 3,542 on at

Like (0)

Report
I am busy with cash flow analysis. My custom functionality includes the option to select main accounts from different companies. I was struggling a lot with the MainAccount concept (I have quite a lot of accounting background - but it is so long ago I basically know nothing at the moment). MainAccounts are cross company but assigned to a company and some other stuff which confuses me. I got around this by simply referencing LedgerLiquidity (which references a MainAccount and MainAccount is the key). LedgerLiquidity is per company.

So I decided to implement this with a table containing, among others, user and LedgerLiquidity references. There are three ways to do this:

Per Company table XYZUserLiquidityAccount - UserId and LedgerLiquidityRecId fields.

Cross company table XYZUserLiquidityAccount - UserId, CompanyDataAreaId and MainAccountRecId (references LedgerLiquidity.MainAccount) fields

Cross company table XYZUserLiquidityAccount - UserId and LedgerLiquidityRecId (LedgerLiquidty.RecId is unique cross company, so no DataArea reference is needed)

II am not asking for help with this - I have already managed to do this. It just brought me back to the question I've had for a while - does it make sense to reference a per company table from a cross company table?

In this case it does not really matter - the user never really sees the table or directly interacts with it (I created a tree on a dialog where the user can select). I just use the table in the background to filter values.

Option 3 is ideal, because it is the easiest to work with - no need for company switching as the root table in the query is cross company. But I have a perfectionistic crisis characteristic in my personality - so I have changed my implentation between the 3 options about 6000 times in the past week. This problem will be sorted out if I can get some confirmation that it is logically incorrect to reference a per company table from a cross company table, or that it is okay.

Was this reply helpful? Yes No
Pete Alberts 3,542 on at

Like (1)

Report

Hi Nikolaos and André

Thank you very much for your help to me (in general) on this forum. I would definitely have been worse off without you.

I have given this much thought.

I realise now that you are correct. It is a 1:N relationship. Referencing the table with RecId is a nifty workaround, but produces a ton of issues. And Nikolaos your first response was the answer - one needs to add a table "in between". It just took me a few months to realise it :)

Was this reply helpful? Yes No

Join the conversation

Community site session details

Cross company table referencing a per company company table

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

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

Featured topics

Product updates

Community site session details

Cross company table referencing a per company company table

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

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

Featured topics

Product updates