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 Microsoft Employee

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.

All responses (5)

Answers (0)

Martin Dráb 230,285 Most Valuable Professional on at

Like (0)

Report

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

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.
Community Member Microsoft Employee on at

Like (0)

Report

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

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.
Martin Dráb 230,285 Most Valuable Professional on at

Like (0)

Report

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

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().
Community Member Microsoft Employee on at

Like (0)

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

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
Suggested answer

Martin Dráb 230,285 Most Valuable Professional on at

Like (0)

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

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.