Item main account

(0) Share

Report

Posted on by Community Member

How can I connect the main account in the main account table to an item with the main account set in the Released Products / Manage Cost / Posting. I can't seem to see that in Personalize and can't make sense of the IDs that I need to follow.

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (1)

Community Member on at

Like (0)

Report

RE: Item main account

Thanks again. I see the relationship now between the InventItemGroupItem table and the InventPosting table. I think I'm missing the leap between InventPosting and MainAccount. I've tried comparing the Recversion, the LedgerChartofAccountants and the RecID to the LedgerDimension, Recversion and RecID. None seem to link up. Is there other combinations using itemcode, custvendCode or one of those fields? I think, primarily as far as I can tell 99% of our items are using ALL as the item code. Bet it'd be useful if one was able to create a view for this stuff and simplify it. But I guess there isn't much use besides having to get it like I am.

Was this reply helpful? Yes No
Verified answer

André Arnaud de Cal... 297,200 Super User 2025 Season 2 on at

Like (0)

Report

RE: Item main account

Hi Brian,

I understand the requirement. It will not be a very straight forward sql statement.

The main account reference is stored in the table InventPosting which has some flexible setup possible. Like shown in the last three record here below.

The main account is presented as code, but the table contains a record reference to the table MainAccount. This table has the main account itself.

The item//item group relation is stored in the table InventItemGroupItem.

Logic in AX is first Table, then Group and All is the last search.

Table means there is a specific account setup for an Item itself.

Group is a relation to the item group linked to the product

All is the escape.

Hope this information will help you furter.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Item main account

I need to have it at the line item level. I need to rebuild it in sql, unfortunately. This is just part of a much larger project. It's more organizationally driven than AX capability driven.

So I need a "select" statement to get it out of there so I can bring that data into the project as well. Thank you for answering. I've been trying to craft it without success. I appreciate the help.

Was this reply helpful? Yes No
André Arnaud de Cal... 297,200 Super User 2025 Season 2 on at

Like (0)

Report

RE: Item main account

Hi Brian,

It is possible to set one main account for all items or based on an item group. Can you indicate where you need to use the sql statement? The logic to retrieve a main account is within standard AX. Do you need to rebuild the login in sql?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Item main account

Andre,

Right. Sorry let me clarify my question. I have the main account set up but I need to find the table that contains the data and how it connects to a table that contains the item number. I need to write a sql statement to return the main account whilst taking in the item number.

Was this reply helpful? Yes No
André Arnaud de Cal... 297,200 Super User 2025 Season 2 on at

Like (0)

Report

RE: Item main account

Hi Brian,

You can setup the main account for your inventory items in menu Inventory and Warehouse management, Setup. Posting, Posting.

There are several types where you need to setup this main account, depending on the features you are using.

e.g.

Sales order: packing slip (might also be filled with a temporary balance account) and Issue

Purchase order: Product receipt (might also be filled with a temporary balance account) and Purchase, inventory receipt

Inventory: Issue and Receipt

Was this reply helpful? Yes No