Finance | Project Operations, Human Resources, ...

How to convert null value to (" ") blank sting in a view that has been used an outer join using SysComputedColumn in d365fo?

(0) Share

Report

Posted on by Community Member

Hi Folks.

I had a requirement for a custom lookup of ItemId on sales Line for External ItemId to be included. I have duplicated the standard Query and the View for the same and have added the CustVendExternalItem table with Outer Join.

 [FormControlEventHandler(formControlStr(SalesTable, SalesLine_ItemId), FormControlEventType::Lookup)]
    public static void SalesLine_ItemId_OnLookup(FormControl sender, FormControlEventArgs e)
    {
        SysTableLookup       sysTableLookup  = SysTableLookup::newParameters(tableNum(ALE_InventItemIdLookupSimpleView), sender);
        Query                query           = new Query();
        QueryBuildDataSource qbds;
        //CustAccount          custAccount = sender.formRun().design().controlName(formControlStr(SalesTable, CustAccount)).valueStr();
        FormControlCancelableSuperEventArgs ce = e as FormControlCancelableSuperEventArgs;
        
        qbds            = query.addDataSource(tableNum(ALE_InventItemIdLookupSimpleView));
        //qbds.addRange(fieldNum(ALE_InventItemIdLookupSimpleView , CustVendRelation)).value(strFmt('%1',SysQuery::valueNotEmptyString()));
        // Add fields
        sysTableLookup.addLookupfield(fieldNum(ALE_InventItemIdLookupSimpleView, ItemId));
        sysTableLookup.addLookupfield(fieldNum(ALE_InventItemIdLookupSimpleView, Product));
        sysTableLookup.addLookupfield(fieldNum(ALE_InventItemIdLookupSimpleView, ProductName));
        sysTableLookup.addLookupfield(fieldNum(ALE_InventItemIdLookupSimpleView, NameAlias));
        sysTableLookup.addLookupfield(fieldNum(ALE_InventItemIdLookupSimpleView, ExternalItemId));
        sysTableLookup.addLookupfield(fieldNum(ALE_InventItemIdLookupSimpleView, ItemGroupId));
        sysTableLookup.addLookupfield(fieldNum(ALE_InventItemIdLookupSimpleView, ItemType));

        // Run lookup
        sysTableLookup.parmQuery(query);
        sysTableLookup.performFormLookup();               

        //cancel super() to prevent error.
        ce.CancelSuperCall();
    }

In the View on the table browser "" was not working and when i have checked in the DB so it has shown in Null value
to over come I have used in DB ISNULL(FieldName,"") and it worked. I want to do the same in D365 fo. I have gone through many of the blogs and found SysComputedColumn class. I am not aware how to use it.

SysComputedColumn::if(SysComputedColumn::isNullExpression(fieldName), SysComputedColumn::returnLiteral(""), fieldName);

https://robscode.onl/d365-outer-join-in-view-with-null-enum-column/

Kindly help me out on this issue.

would be appreciated.

Cheers.

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

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

Like (0)

Report
I would simply use ISNULL(), as you mentioned. Using if() and isNullExpression() looks overcomplicated to me in this case.

For example:

return strFmt('ISNULL(%1, \'\')', fieldName);

Just note that you didn't show us how you've populated fieldName variable, therefore we can't review this part.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
return strFmt('ISNULL(%1, \'\')', fieldName);

Hi Martin,

Thanks for the reply.

Could you please advise as you have mentioned above code?

As per you query for field name I have picked it from on of your replied Link below
https://community.dynamics.com/ax/f/microsoft-dynamics-ax-forum/161366/how-to-handle-null-value-string-column-of-a-view-in-x

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

Like (0)

Report

I'm sorry, but it's not clear to me what kind of advise you want about my code. What problem do you have with it?

Regaring fieldName, the thread you've linked doesn't seem to contain its definition - neither from me nor anybody else. Couldn't you please simply show your actual code? Just a hint - you should get the value by something like SysComputedColumn::returnField().

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I have no idea of the syntax or using the SysComputedColumn Class in x++/view.

I am simply asking to help me achieve to replace null value like below

To this as below with null value be replaced by " " blank

Or you can share anything that can help me to get rid of this null value to blank.

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

Like (0)

Report

If you have no idea about how to create computed columns, your should fix that by reading an article on this topic. For example: Computed View Columns in AX 2012.

Then you should be able to utilize what I gave you above.

Was this reply helpful? Yes No