Finance | Project Operations, Human Resources, ...

Unanswered

String data, right truncation

(1) Share

Report

Posted on by JeanB

206

Hello everyone, hope you are going well.

In a custom form, I put a grid showing view's data in which I have a method giving a real computed column.

The data display correctly, but when I want to filter the column with a decimal value, I have this error. This not happen when I filter with a integer.

session 1214793 (jbboucly)

SELECT T1.ITEMID,T1.X01REVISIONSPEC,T1.X02REVISIONDATE,T1.X03AUTHOR,T1.X04DELIVERYSTATE,T1.X05NORME,T1.X06WIDTHMM,[...],T1.X48CERTIFICATEOFCONFORMITYPURCHASES,T1.PARTITION,T1.RECID FROM MET_ATTRIBUTECONVIVIALVIEW T1 WHERE (((PARTITION=5637144576) AND (DATAAREAID=N'msas')) AND (X12THICKNESSMM=?)) ORDER BY T1.RECID OPTION(FAST 129)

[Microsoft][ODBC Driver 17 for SQL Server]String data, right truncation

Object Server Azure:
Cannot select a record in MET_AttributeConvivialView (MET_AttributeConvivialView). The SQL database has issued an error.

Here is the method used. It seemes that only the real columns don't work, the text, date and integer ones are ok.

public static str _12ThicknessMM()
{
tableName viewName = tableStr(MET_AttributeConvivialView);

str attrName = SysComputedColumn::comparisonField(
viewName,
identifierStr(MET_AttributeView),
fieldStr(MET_AttributeView, Name));

str ValeurFloat = SysComputedColumn::comparisonField(
viewName,
identifierStr(MET_AttributeView),
fieldStr(MET_AttributeView, FloatValue));

str targetAttrName = SysComputedColumn::returnLiteral("12_Thickness (mm)");

str empty = SysComputedColumn::cast(
SysComputedColumn::returnLiteral("0.0"),
"NUMERIC(32,16)");

str caseExpr = SysComputedColumn::if(
SysComputedColumn::compareExpressions(attrName, "=", targetAttrName),
ValeurFloat,
empty
);

str maxExpr = SysComputedColumn::cast(SysComputedColumn::max(caseExpr),
"NUMERIC(32,16)");

return maxExpr;
}

Do you guys know how to correct this problem ?

Thanks !

Categories:

Dynamics 365 Finance

I have the same question (1)

All responses (11)

Answers (0)

André Arnaud de Cal... 301,194 Super User 2025 Season 2 on at

Like (0)

Report

Hi Jean,

Have you tried using a comma as decimal separator? You can also try using the search option Matches. Please let us know if this works for you.

Was this reply helpful? Yes No
JeanB 206 on at

Like (0)

Report

Hello André, thanks for your quick reply !

I tried with a point or a coma, it's the same, the error massage appears. Also with a matche filter, that don't work.

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

Like (1)

Report

Hi Jean,

This is odd, I thought I replied earlier on this question, but can't see the reply anymore.

Anyway, try to use the comma as decimal separator and/or use the filter option Matches. Let us know if this makes a difference or not.

Was this reply helpful? Yes No
JeanB 206 on at

Like (0)

Report

Hello André, thanks for your quick reply! (Same for me, I already replied to your response)

I tried using both a comma and a point, but neither worked. I also tested it with a match filter, and I’m still getting the same error message.

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

Like (1)

Report

Could you please give us the actual SQL code? Maybe we'll spot something wrong there. You can use SQL Server Management Studio to see the definition of the view, including the computed column.

By the way, you can remove the calls of cast(). When you look at the SQL code, you'll see that the system always wraps your code in a cast corresponding to the chosen type of the computed column.

Was this reply helpful? Yes No

JeanB

206 on at

Like (0)

Report

Hello Martin, thanks for your response.

Ok for the Cast().

Here is the design :

SELECT        MAX(T1.ITEMBUYERGROUPID) AS ITEMBUYERGROUPID, MAX(T1.PRODUCTLIFECYCLESTATEID) AS PRODUCTLIFECYCLESTATEID, T1.ITEMID, T1.DATAAREAID, T1.PARTITION, 1010 AS RECID, MAX(T2.NAME) AS NAME, 
                         T2.PARTITION AS PARTITION#2, CAST(MAX(CASE WHEN T1.NAME = N'01_Revision Spec' THEN T1.TEXTVALUE ELSE '' END) AS NVARCHAR(10)) AS X01REVISIONSPEC, 
                         CAST(MAX(CASE WHEN T1.NAME = N'02_Revision date' THEN T1.DATETIMEVALUE ELSE '' END) AS DATETIME) AS X02REVISIONDATE, CAST(MAX(CASE WHEN T1.NAME = N'03_Author' THEN T1.TEXTVALUE ELSE '' END) 
                         AS NVARCHAR(40)) AS X03AUTHOR, CAST(MAX(CASE WHEN T1.NAME = N'04_Delivery state' THEN T1.TEXTVALUE ELSE '' END) AS NVARCHAR(40)) AS X04DELIVERYSTATE, 
                         CAST(MAX(CASE WHEN T1.NAME = N'05_Norme' THEN T1.TEXTVALUE ELSE '' END) AS NVARCHAR(40)) AS X05NORME, CAST(MAX(CASE WHEN T1.NAME = N'06_Width (mm)' THEN T1.FLOATVALUE ELSE N'0' END) 
                         AS NUMERIC(32, 16)) AS X06WIDTHMM, CAST(MAX(CASE WHEN T1.NAME = N'07_Width (in)' THEN T1.FLOATVALUE ELSE N'0' END) AS NUMERIC(32, 16)) AS X07WIDTHIN, 
                         CAST(MAX(CASE WHEN T1.NAME = N'08_Width tolerances' THEN T1.TEXTVALUE ELSE '' END) AS NVARCHAR(40)) AS X08WIDTHTOLERANCES, 
                         CAST(MAX(CASE WHEN T1.NAME = N'09_Length (mm)' THEN T1.FLOATVALUE ELSE N'0' END) AS NUMERIC(32, 16)) AS X09LENGTHMM, 
                         CAST(MAX(CASE WHEN T1.NAME = N'10_Length (in)' THEN T1.FLOATVALUE ELSE N'0' END) AS NUMERIC(32, 16)) AS X10LENGTHIN, 
                         CAST(MAX(CASE WHEN T1.NAME = N'11_Length tolerances' THEN T1.TEXTVALUE ELSE '' END) AS NVARCHAR(40)) AS X11LENGTHTOLERANCES, 
                         CAST(CAST(MAX(CASE WHEN T1.NAME = N'12_Thickness (mm)' THEN T1.FLOATVALUE ELSE CAST(N'0.0' AS NUMERIC(32, 16)) END) AS NUMERIC(32, 16)) AS NUMERIC(32, 16)) AS X12THICKNESSMM, 
                         CAST(MAX(CASE WHEN T1.NAME = N'13_Thickness (in)' THEN T1.FLOATVALUE ELSE N'0' END) AS NUMERIC(32, 16)) AS X13THICKNESSIN, 
                         CAST(MAX(CASE WHEN T1.NAME = N'14a_Thickness tolerances (-) mm' THEN T1.FLOATVALUE ELSE N'0' END) AS NUMERIC(32, 16)) AS X14ATHICKNESSTOLERANCESINFMM, 
                         CAST(MAX(CASE WHEN T1.NAME = N'14b_Thickness tolerances (+) mm' THEN T1.FLOATVALUE ELSE N'0' END) AS NUMERIC(32, 16)) AS X14BTHICKNESSTOLERANCESSUPMM, 
                         CAST(MAX(CASE WHEN T1.NAME = N'14c_Thickness tolerances (-) Inche' THEN T1.FLOATVALUE ELSE N'0' END) AS NUMERIC(32, 16)) AS X14CTHICKNESSTOLERANCESINFIN, 
                         CAST(MAX(CASE WHEN T1.NAME = N'14d_Thickness tolerances (+) Inche' THEN T1.FLOATVALUE ELSE N'0' END) AS NUMERIC(32, 16)) AS X14DTHICKNESSTOLERANCESSUPIN, 
                         CAST(MAX(CASE WHEN T1.NAME = N'15_Diameter (mm)' THEN T1.FLOATVALUE ELSE N'0' END) AS NUMERIC(32, 16)) AS X15DIAMETERMM, 
                         CAST(MAX(CASE WHEN T1.NAME = N'16_Diameter (in)' THEN T1.FLOATVALUE ELSE N'0' END) AS NUMERIC(32, 16)) AS X16DIAMETERIN, 
                         CAST(MAX(CASE WHEN T1.NAME = N'17_Diameter tolerances' THEN T1.TEXTVALUE ELSE '' END) AS NVARCHAR(40)) AS X17DIAMETERTOLERANCES, 
                         CAST(MAX(CASE WHEN T1.NAME = N'18_Edge state' THEN T1.TEXTVALUE ELSE '' END) AS NVARCHAR(40)) AS X18EDGESTATE, 
                         CAST(MAX(CASE WHEN T1.NAME = N'20_Roll int. Diameter (mm)' THEN T1.TEXTVALUE ELSE '' END) AS NVARCHAR(40)) AS X20ROLLINTDIAMETERMM, 
                         CAST(MAX(CASE WHEN T1.NAME = N'21_Roll ext. Diameter (mm)' THEN T1.TEXTVALUE ELSE '' END) AS NVARCHAR(40)) AS X21ROLLEXTDIAMETERMM, 
                         CAST(MAX(CASE WHEN T1.NAME = N'22_Roll ext. Diameter (in)' THEN T1.TEXTVALUE ELSE '' END) AS NVARCHAR(40)) AS X22ROLLEXTDIAMETERIN, 
                         CAST(MAX(CASE WHEN T1.NAME = N'23_Wound coil' THEN T1.TEXTVALUE ELSE '' END) AS NVARCHAR(40)) AS X23WOUNDCOIL, 
                         CAST(MAX(CASE WHEN T1.NAME = N'24_Mechanical strength inf. (N/mm²)' THEN T1.INTVALUE ELSE N'0' END) AS INT) AS X24MECHANICALSTRENGTHINFNMM, 
                         CAST(MAX(CASE WHEN T1.NAME = N'25_Mechanical strength inf. (ksi)' THEN T1.INTVALUE ELSE N'0' END) AS INT) AS X25MECHANICALSTRENGTHINFKSI, 
                         CAST(MAX(CASE WHEN T1.NAME = N'26_Mechanical strength sup. (N/mm²)' THEN T1.INTVALUE ELSE N'0' END) AS INT) AS X26MECHANICALSTRENGTHSUPNMM, 
                         CAST(MAX(CASE WHEN T1.NAME = N'27_Mechanical strength sup. (ksi)' THEN T1.INTVALUE ELSE N'0' END) AS INT) AS X27MECHANICALSTRENGTHSUPKSI, 
                         CAST(MAX(CASE WHEN T1.NAME = N'28_Inf. Vickers hardness HV' THEN T1.INTVALUE ELSE N'0' END) AS INT) AS X28INFVICKERSHARDNESSHV, 
                         CAST(MAX(CASE WHEN T1.NAME = N'29_Sup. Vickers hardness HV' THEN T1.INTVALUE ELSE N'0' END) AS INT) AS X29SUPVICKERSHARDNESSHV, 
                         CAST(MAX(CASE WHEN T1.NAME = N'30_Mini elongation at 80% (mm)' THEN T1.TEXTVALUE ELSE '' END) AS NVARCHAR(40)) AS X30MINIELONGATIONATMM, 
                         CAST(MAX(CASE WHEN T1.NAME = N'32_Max. edgewise curvature (mm/M)' THEN T1.TEXTVALUE ELSE '' END) AS NVARCHAR(40)) AS X32MAXEDGEWISECURVATUREMMM, 
                         CAST(MAX(CASE WHEN T1.NAME = N'33_Max. edgewise curvature (in/3ft)' THEN T1.TEXTVALUE ELSE '' END) AS NVARCHAR(40)) AS X33MAXEDGEWISECURVATUREINFT, 
                         CAST(MAX(CASE WHEN T1.NAME = N'34_Max. crossbow (mm)' THEN T1.TEXTVALUE ELSE '' END) AS NVARCHAR(40)) AS X34MAXCROSSBOWMM, 
                         CAST(MAX(CASE WHEN T1.NAME = N'35_Max. crossbow (in)' THEN T1.TEXTVALUE ELSE '' END) AS NVARCHAR(40)) AS X35MAXCROSSBOWIN, 
                         CAST(MAX(CASE WHEN T1.NAME = N'36_Max. roughness Ra (µ)' THEN T1.TEXTVALUE ELSE '' END) AS NVARCHAR(40)) AS X36MAXROUGHNESSRA, 
                         CAST(MAX(CASE WHEN T1.NAME = N'37_Max. roughness Ra (µ in)' THEN T1.TEXTVALUE ELSE '' END) AS NVARCHAR(40)) AS X37MAXROUGHNESSRAIN, 
                         CAST(MAX(CASE WHEN T1.NAME = N'38_Plating' THEN T1.TEXTVALUE ELSE '' END) AS NVARCHAR(40)) AS X38PLATING, CAST(MAX(CASE WHEN T1.NAME = N'39_Plating thickness (µ)' THEN T1.TEXTVALUE ELSE '' END) 
                         AS NVARCHAR(40)) AS X39PLATINGTHICKNESS, CAST(MAX(CASE WHEN T1.NAME = N'40_Plating thickness (µ in)' THEN T1.TEXTVALUE ELSE '' END) AS NVARCHAR(40)) AS X40PLATINGTHICKNESSIN, 
                         CAST(MAX(CASE WHEN T1.NAME = N'41_Plating characteristic' THEN T1.TEXTVALUE ELSE '' END) AS NVARCHAR(40)) AS X41PLATINGCHARACTERISTIC, 
                         CAST(MAX(CASE WHEN T1.NAME = N'42_Auto quality' THEN T1.TEXTVALUE ELSE '' END) AS NVARCHAR(40)) AS X42AUTOQUALITY, 
                         CAST(MAX(CASE WHEN T1.NAME = N'43_According to specification' THEN T1.TEXTVALUE ELSE '' END) AS NVARCHAR(40)) AS X43ACCORDINGTOSPECIFICATION, 
                         CAST(MAX(CASE WHEN T1.NAME = N'44_According to our general purchasing conditions' THEN T1.TEXTVALUE ELSE '' END) AS NVARCHAR(40)) AS X44ACCORDINGTOOURGENERALPURCHASINGCONDITIONS, 
                         CAST(MAX(CASE WHEN T1.NAME = N'45_Grade / Family' THEN T1.TEXTVALUE ELSE '' END) AS NVARCHAR(40)) AS X45GRADEFAMILY, 
                         CAST(MAX(CASE WHEN T1.NAME = N'48_Certificate of conformity (Purchases)' THEN T1.TEXTVALUE ELSE '' END) AS NVARCHAR(40)) AS X48CERTIFICATEOFCONFORMITYPURCHASES

FROM            dbo.MET_ATTRIBUTEVIEW AS T1 LEFT OUTER JOIN
                         dbo.ECORESPRODUCTTRANSLATION AS T2 ON T1.PRODUCT = T2.PRODUCT AND T1.PARTITION = T2.PARTITION AND T2.LANGUAGEID = N'en-US'
GROUP BY T1.ITEMID, T1.DATAAREAID, T1.PARTITION, T2.PARTITION

Was this reply helpful? Yes No

Martin Dráb 237,987 Most Valuable Professional on at

Like (0)

Report
Okay, is this the computed column you mean?

CAST( CAST( MAX( CASE WHEN T1.NAME = N'12_Thickness (mm)' THEN T1.FLOATVALUE ELSE CAST(N'0.0' AS NUMERIC(32, 16)) END ) AS NUMERIC(32, 16) ) AS NUMERIC(32, 16) ) AS X12THICKNESSMM

And when we get rid of the extra casting, we get this:

CAST( MAX( CASE WHEN T1.NAME = N'12_Thickness (mm)' THEN T1.FLOATVALUE ELSE 0.0 END ) AS NUMERIC(32, 16) ) AS X12THICKNESSMM

What happens if you filter the view by X12THICKNESSMM directly in T-SQL (not through F&O)?

Was this reply helpful? Yes No
JeanB 206 on at

Like (0)

Report

Hello, yes it's this one, but it doesn't work for any column.
I can filter the data directly in SQL Server without any issue:

Was this reply helpful? Yes No
Layan Jwei 8,118 Super User 2025 Season 2 on at

Like (0)

Report

Hi Jean,

If you try to filter the full value in the form, would it work? Just copy the full value from SQL and try that.

Also if you remove the numeric(32,16), would the filter work?

Note: Make sure you sync your database

And please show us your new x++ code for the computed column, and show us the properties of the computed column in the view.

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

Like (0)

Report
I've managed to reproduce the problem.

The example you shared wasn't useful to me - it's very complex, while 95% is irrelevant to the problem, and it depends on some custom objects. I was forced to develop my own example to be able to test your scenario. (Next time, consider making it easier for others to help you.)

I created a view on PurchLine, grouped by ItemId and with a real computed column backed by this method:

private static str numSql() { return 'CHECKSUM(T1.ItemId) / 100.0'; }

Then I created a form with a grid bound to this view. Sorting works as expected, but filtering with 'is equal to' gives me the error.

I don't have more time at the moment, but I'll do more tests later. Anyway, this will likely lead to a support request (where you'll also need a simple example as mine).

Was this reply helpful? Yes No

Join the conversation

Community site session details

String data, right truncation

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

String data, right truncation

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates