topic Re: Unit Conversion in Archived Groups

Unit Conversion

Tue, 22 Jul 2025 17:59:49 GMT

Dear Community Team,

I have a problem when I want convert unit for the items.

I have a table for unit conversion and is contains a following

ItemID,FromUnit,ToUnit,Factor

item001,Case,Unit,40

item001,Case,Outer,20

item001,MSU,Kilo,7

item002,Case,Unit,15

item004,Case,Unit,29

and I have table for sales as example

SalesID,ItemId,UnitID,Qty

S001,item001,Unit,250

S001,item002,Case,50

I have a control contain all of units conversion and when I click to any unit I want must reflect

all quantities in the charts.

Re: Unit Conversion

MK_QSL — Wed, 01 Apr 2015 09:57:26 GMT

How you want to Associate your data model ?

UnitID of Sales Table equal to Which Unit of Item table... i.e. UnitID = FromUnit or UnitID = ToUnit?

To create association, you need to create a common key..

ItemiD & UnitID as Key1

in Sales Table

Same in Item Table

Re: Unit Conversion

Wed, 01 Apr 2015 10:18:21 GMT

Thanks Manish

Please can you give example if I want to create an expression in the chart for sum quantities.

and how can I benefit from the key1?

Re: Unit Conversion

MK_QSL — Wed, 01 Apr 2015 10:21:36 GMT

First can you tell me ... UnitID = FromUnit or UnitID = ToUnit?

Re: Unit Conversion

Wed, 01 Apr 2015 10:28:33 GMT

I use UnitId = FromUnit but if I can use UnitId = ToUnit may be that more than better.

Re: Unit Conversion

Wed, 01 Apr 2015 10:35:45 GMT

I have an expression as

sum(if(FromUnit = GetFieldSelections(UC_TOUNIT), QTYORDERED))

sum(if(ToUnit = GetFieldSelections(UC_TOUNIT),QTYORDERED*FACTOR,0))

Note: UC_TOUNIT is a list box controls to filtering as a parameter.

but this take a big time to resolve issues because the factor

in this line no problem is very fast:

sum(if(FromUnit = GetFieldSelections(UC_TOUNIT), QTYORDERED))

but in this line is very long time, some times as 30 minutes,

sum(if(ToUnit = GetFieldSelections(UC_TOUNIT),QTYORDERED*FACTOR,0))

Re: Unit Conversion

MK_QSL — Wed, 01 Apr 2015 10:53:56 GMT

ItemTemp:

Load * Inline

[

ItemID, FromUnit, ToUnit, Factor

item001,Case, Unit, 40

item001,Case, Outer, 20

item001,MSU, Kilo, 7

item002,Case, Unit, 15

item004,Case, Unit, 29

];

Item:

Load ItemID, ItemID & FromUnit as Key1, FromUnit as UnitID, 'FromUnit' as Flag, Factor Resident ItemTemp;

Load ItemID, ItemID & ToUnit as Key1, ToUnit as UnitID, 'ToUnit' as Flag, Factor Resident ItemTemp;

Drop Table ItemTemp;

Sales:

Load SalesID, ItemId & UnitID as Key1, Qty Inline

[

SalesID,ItemId, UnitID,Qty

S001, item001,Unit,250

S001, item002,Case,50

];

Now use below expression

=SUM({<Flag = {'FromUnit'}>}Qty) + SUM({<Flag = {'ToUnit'}>}Qty*Factor)

Re: Unit Conversion

Wed, 01 Apr 2015 11:38:53 GMT

Thanks Manish,

I want get a value for ToUnit if user click on unit conversion list box as parameter and it is name is UC_TOUNIT.

this controls to convert all of quantities according UC_TOUNIT