web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Unitofmeasureconversio...
Microsoft Dynamics AX (Archived)

Unitofmeasureconversion table not giving real numbers

(0) ShareShare
ReportReport
Posted on by Lionel07 495

Hello experts,

your urgent help please. when I show the record for the table unitofmeasureconversion table I don't get real values. for example: when I run a report the query:

SELECT TOP 1000   [InventTable].ITEMID
,[FROMUNITOFMEASURE]
      ,[TOUNITOFMEASURE]
      ,inventTable.[PRODUCT]
      ,[FACTOR]
      ,[NUMERATOR]
      ,[DENOMINATOR]
      ,[INNEROFFSET]
      ,[OUTEROFFSET]
      ,[ROUNDING]
      ,[UNITOFMEASURECONVERSION].[RECID]
  FROM [DBNAME].[dbo].[UNITOFMEASURECONVERSION], [DBNAME].[dbo].[InventTable]
  WHERE [DBNAME].[dbo].[UNITOFMEASURECONVERSION].PRODUCT = [DBNAME].[dbo].InventTable.PRODUCT

it gives me results as follow:

Denominator Factor FromUnitOfMeasure Product Rounding ToUnitOfMeasure modifiedDateTime recVersion Partition RecId
1 1 5637144591 5637144848 To nearest 5637144594 22/11/2012 11:30 1 5637144576 5637145401

as you can see the FromUnitOFMeasure and ToUnitOfMeasure is not showing the real values. 

Please any help would be greatly appreciated

*This post is locked for comments

I have the same question (0)
  • Verified answer
    Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at

    Hi,

    You should fetch those values from table UnitOfMeasure(field: Symbol has the value) based on following relations

    UnitOfMeasureConversion.FromUnitOfMeasure == UnitOfMeasure.RecId

    UnitOfMeasureConversion.ToUnitOfMeasure == UnitOfMeasure.RecId.

    So, your query would like like

    use [DBNAME}
    SELECT TOP 1000   Invent.ITEMID
,Conver.[FROMUNITOFMEASURE]
      ,Conver.[TOUNITOFMEASURE]
      ,Conver.[PRODUCT]
      ,Conver.[FACTOR]
      ,Conver.[NUMERATOR]
      ,Conver.[DENOMINATOR]
      ,Conver.[INNEROFFSET]
      ,Conver.[OUTEROFFSET]
      ,Conver.[ROUNDING]
      ,Conver.[RECID]
	  ,FromUnitOfMeasure.SYMBOL as [FromUnit]
	  ,ToUnitOfMeasure.SYMBOL as [ToUnit]
  FROM [dbo].[UNITOFMEASURECONVERSION] as Conver
  join [dbo].[InventTable] as Invent
  on Conver.PRODUCT = Invent.PRODUCT
  join UnitOfMeasure as FromUnitOfMeasure
  on Conver.FromUnitOfMeasure = FromUnitOfMeasure.RecId
  join UnitOfMeasure as ToUnitOfMeasure
  on Conver.TOUNITOFMEASURE = ToUnitOfMeasure.RecId
  • Lionel07 Profile Picture
    Lionel07 495 on at

    Thanks a mil Chaitanya.

    When I run your query I got some error so am customising it to see if it will work.

    I am not familiar with JOIN operation so can I use  WHERE instead? Aslo, is it posible to write out the entire table name and not to do the shortcut. am trying the query directly in my DB. Please help me.

  • Lionel07 Profile Picture
    Lionel07 495 on at

    Ok  think I got it now. you save me and possibly my job.... thanks a million

  • Verified answer
    Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at

    Hi,

    Without joins we cannot fetch values from other tables. Yes, you can use full tablename instead of shortcut, just remove table synonyms from the query. I haven't encountered any error, can you post the error you are facing.

  • Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at

    Hi,

    If you feel your issue is resolved, could you mark the helpful answer(s) as verified.

  • Lionel07 Profile Picture
    Lionel07 495 on at

    Thanks a mil again Chaitanya.

    question please... is it posible for me to get only the intra-class conversion values only? thank much for your help.

  • Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at

    Hi,

    Can you provide me details about intra-class conversion values, as I am not aware of it.

  • Lionel07 Profile Picture
    Lionel07 495 on at

    The thing is with the UnitofMeasureConversion table it captures 3 different category of conversions: Standard, Intra-class and Inter-Class

    what I would like to get the intra-class conversion

    intra-class conversions says the units has to be from the same class/group eg: pcs and KG I think these are from the same class/group called quantity.

    Inter-Class now an be done when they are in different groups eg: when converting from liters to kg these are in both volume liquid and quantity

    PS. with the intra an inter class an item number must be selected.

    thanks again

  • Verified answer
    Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at

    Hi,

    Following query fetches the details of the products with intra-class conversion values

    use [DBNAME]
    SELECT [dbo].[InventTable].ITEMID
    ,[dbo].[UNITOFMEASURECONVERSION].[PRODUCT]
    ,FromUnitOfMeasure.SYMBOL as [FromUnit]
    ,ToUnitOfMeasure.SYMBOL as [ToUnit]
    ,[dbo].[UNITOFMEASURECONVERSION].[FACTOR]
    ,[dbo].[UNITOFMEASURECONVERSION].[NUMERATOR]
    ,[dbo].[UNITOFMEASURECONVERSION].[DENOMINATOR]
    ,[dbo].[UNITOFMEASURECONVERSION].[INNEROFFSET]
    ,[dbo].[UNITOFMEASURECONVERSION].[OUTEROFFSET]
    ,[dbo].[UNITOFMEASURECONVERSION].[ROUNDING]
    ,[dbo].[UNITOFMEASURECONVERSION].[RECID]
    FROM [dbo].[UNITOFMEASURECONVERSION]
    join [dbo].[InventTable]
    on [dbo].[UNITOFMEASURECONVERSION].PRODUCT = [dbo].[InventTable].PRODUCT
    join UnitOfMeasure as FromUnitOfMeasure
    on [dbo].[UNITOFMEASURECONVERSION].FromUnitOfMeasure = FromUnitOfMeasure.RecId
    join UnitOfMeasure as ToUnitOfMeasure
    on [dbo].[UNITOFMEASURECONVERSION].TOUNITOFMEASURE = ToUnitOfMeasure.RecId
    and FromUnitOfMeasure.UNITOFMEASURECLASS = ToUnitOfMeasure.UNITOFMEASURECLASS

  • Verified answer
    Lionel07 Profile Picture
    Lionel07 495 on at

    Chaitanya Golla saved me grateful for your help

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Meet the Microsoft Dynamics 365 Contact Center Champions

We are thrilled to have these Champions in our Community!

Congratulations to the March Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
CP04-islander Profile Picture

CP04-islander 16

#2
GiacomoRovai Profile Picture

GiacomoRovai 4

#3
Douglas Noel Profile Picture

Douglas Noel 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans