InventDimID - Help I'm going crazy

Jonathan Smyth

unread,

Dec 21, 2009, 11:00:01 AM12/21/09

to

Hi all,

I'd be grateful of any help you might be able to offer on this one . . . . .

I'm trying to track down the on hand and available stock by item dimension
and am drawing a blank. Basically I'm going from the inventdimension
combination to the inventsum table, using the inventdim ID and the itemID.
The strange thing is that there are inventdimID's that exist in the inventsum
table but do not cross reference to the inventdimcombination. Does anyone
know if there is an intermediary table in between please??

Cheers

Jon

Glibs

unread,

Dec 21, 2009, 4:52:20 PM12/21/09

to

It is strongly recommended to use standard classes and standard inquiry
building approach to obtain inventory onhand data.

InventSum has aggregates for both item and stock dimensions.
InventDimCombination has only item dimensions (stock dimensions are
refined). So, if item is tracked by color and warehouse, in
InventDimCombination you will have only color and InventSum will keep onhand
data by color and warehouse. And InventDimId will be different in these
cases.

Jonathan Smyth

unread,

Dec 22, 2009, 4:29:02 AM12/22/09

to

Hi Glibs,

The main reason we're trying to figure out the relationship is that we're
pulling out the data using SQL, and posting it using the business connector
and the various related classes. Extracting the data using the business
connector was significantly slower than using a SQL query, hence me trying to
establish which InventDimId the InventSum is referencing?? Is there any
relationship to other tables when stocking within locations, etc?

Regards

Jon

"Glibs" wrote:

> .
>

Klaas Deforche

unread,

Dec 22, 2009, 8:59:01 AM12/22/09

to

Hi Jonathan,

The reason that there are inventDimId's in inventsum that aren't in
inventDimCombination is because in inventsum, there are dimensions that are
not present in InventDimCombination.

InventDimCombination only has Config/Size/Color (= item dimensions), while
inventSum also has storage dimensions (like site, warhouse, etc).

You have to know that the InventDimId represents *one combination* of
inventory dimensions.

Eg:
Color 01 + Size M (+ Config is blank)-> InventDimId 000001
Color 01 + Size M + Config 02 -> InventDimId 000002
Color 01 + Size M + Config 02 + Site 01 + Warehouse 08 -> InventDimId 000003

You see that you can't just join on inventDimId, even though Color + Size
are the same.
You'll have to join on the InventDim table (= the intermediary table you are
looking for), only on the dimensions that are important to you (and
inventdimid ofcourse).

Hope this helps.

Kind regards,
Klaas.
----------------
http://www.artofcreation.be

alice

unread,

Dec 26, 2009, 7:54:12 PM12/26/09

to

"Jonathan Smyth" <Jonath...@discussions.microsoft.com> escreveu na
mensagem news:85B00EC7-30FB-4F93...@microsoft.com...

jaestevan

unread,

Jan 19, 2010, 2:51:01 AM1/19/10

to

May be useless but not commented yet. You always have to ensure the relation
of the DATAAREAID field if using SQL Server tables directly, this is, the
company that is associated to each record in the tables.