Unit convertion in Computed Column to use in Query

(0) Share

Report

Posted on by Community Member

Good day everyone,

I am creating a form, which will be used for matching purchase and sales line.

This form needs to present the list of matching purchase candidates for a SalesLine comparing quantity with purchase lines.
From sales order form we can select the sales line and then click menu action button to open my form show matchable candidates.

This is a form with a grid and behind it is a query with a PurchLine as a main datasource.

The matching is done using quantity, when SalesLine.SalesQty == PurchLine.PurchQty we need to show this line, but, of course, lines can have different units and we need to implement unit conversion there.

For sales line with 100 * UnitId kilograms the form should show all PurchLines candidates which have 100 kg or even 0.1 ton or even 100 000 grams.

And that is a problem I do not know how to add the convertion to my form.

Unfortunatelly we need performance and we cannot use some Tmp table which we would pre-fill upon openning with converted values.

Is there any option how I can tell SQL to do the magic for me using Computed Columns or join some View which will convert all quantities to the UnitId I specify?
Or simply: Can I do the unit convertion using Computed column?

I have the same question (0)

All responses (3)

Answers (0)

Blue Wang on at

Like (0)

Report
Hi Volodymyr2127,

For convertion you can check UnitOfMeasureConverter_Product class.

UnitOfMeasureConverter_Product unitConverter = UnitOfMeasureConverter_Product::construct(); unitConverter.parmProduct(InventTable::find("Test",true).Product); unitConverter.parmFromUnitOfMeasure(UnitOfMeasure::unitOfMeasureIdBySymbol("g")); unitConverter.parmToUnitOfMeasure(UnitOfMeasure::unitOfMeasureIdBySymbol("kg"));

A computed column method it returns a piece of T-SQL code for the calculation. The method is executed on database synchronization and the piece of T-SQL code is inserted to the view definition.

If you want to use computed columns, your need to prepare SQL code that you want to generate.

Obviously can't use X classes (such as UnitOfMeasureConverter) in SQL.

Was this reply helpful? Yes No
Martin Dráb 237,976 Most Valuable Professional on at

Like (0)

Report

The logic for conversions is pretty complicated; rewriting it to T-SQL (and then writing X++ code generating this T-SQL code) wouldn't be a huge task. I don't think it's worth it.

By the way, which version of AX are you talking about?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Dynamics 365 F&O 10.0.12

Was this reply helpful? Yes No