asked on

How to use StrReplace function in Dynamics AX

Hello - I have a field in an AX view that sometimes will contain an unwanted carriage return in the data. I need to figure out a way to strip any carriage returns out of each record for this field. I know how to do this in SQL, but AX can blow up if you simply apply the changes to the SQL table, so this needs to be done at the AX level. does anyone have experience with X++ who might be able to suggest how best to do this? So, to clarify - if I have a view named "ASNView", with a field called "Address", with record 1 containing "123 Baker St {CR}", I want to strip that {CR} off of it, and return just "123 Baker St".

quale-kaizen

Damian,

The function to remove given characters from a string is strRem().
You can take a look at the msdn article for this function here:

http://msdn.microsoft.com/en-us/library/aa629339.aspx

BTW the Address field is composed of the Street, City, and ZipCode fields.

Damian Gardner

ASKER

quale-kaizen,

Thanks for responding. I see the syntax for the function, which helps, but I'm not sure how to structure the X++ code in the Method section of the view, which is where it needs to go, correct? Do you know how to do that? Things like passing and returning the field in question, and returning a value back successfully, is another part of what I need help on. Let me know.

Thanks,
Damian

Damian Gardner

ASKER

also forgot to ask - if I want to remove carriage returns, how is that specified?

Thanks

quale-kaizen

Damian,

You can create a 'display method' in the view
and than use it as a 'datamethod' on your forms and reports.
This is how the display method should look like:

display str displayStreetFixed()
{
    ;
    return strRem(this.Street, '\n');
}

Open in new window

Damian Gardner

ASKER

Ok - one more question now - and thanks for your help on this. How would I employ that on the field of the view in question? Right now, the field is simply pointing to a field from the main table. How do I perform the function on the data, exactly?

Thanks much

Damian Gardner

ASKER

I don't think a Display method is the right one to use, if this is a field that is being used by an electonic order system, with no user intervention. This field is used on an electronic data file that gets transmitted to another company, relating to an order they placed with us electronically. Having said this, is there another type I should use?

Thanks

quale-kaizen

Well, you can't. What you can do is create a dispaly method and use it on your forms or reports.

Damian Gardner

ASKER

Ok - so - this won't edit the value that comes up in the view field when I select records from it then?

quale-kaizen

It would only display the corrected value for the field.

In AX you can't use views to update or create new records.

Damian Gardner

ASKER

Ok - so - what about correcting at the source table then? Maybe I'm focusing on the wrong thing here. Could it be done at the table level?

Thanks

quale-kaizen

Sure.
As a second step you can avoid inserting records with '\n' to the DB.

Damian Gardner

ASKER

I agree on your second step there. We just can't be sure the customer service people will always comply with that. So - what type of method would I use on the underlying table field then? And is there a way to test it before implementing, or just put in place and try?

Thanks

quale-kaizen

You can duplicate your company account on your production server (this may take some time if the db is big)
Or you can use the 'DAT' company account for tests.

Go to Administration -> Company accounts and check your current setup

ASKER CERTIFIED SOLUTION

quale-kaizen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Damian Gardner

ASKER

Ok - thanks. Let me try and let you know.

Damian Gardner

ASKER

By the way - what's your experience with applying functions to the SQL-level fields? What if I simply added the StrReplace function in SQL instead? Would that blow up AX?

quale-kaizen

Currently we are using standard SQL views for reporting purposes.

After you edit an AX view from SQL, you can no longer guarantee proper working of the view in AX.

I never felt the need to do such a thing myself, but you can certainly give it a try ;)
Having the db backed up beforehand is good idea thoe

Damian Gardner

ASKER

Well - maybe I'll leave that alone then.

Damian Gardner

ASKER

Well - I've figured out a way to do it, but it's at the SQL view level, which is kind of cheating. The problem is that when AX re-syncs the view with SQL, I lose the function to remove the CR. Do you know if there's a way to exclude a table/view from being synchronized?

quale-kaizen

There is no standard way to do this.
However, one option would be to create a stored procedure, that adds the function to remove CR to the view, and than call the sp from AX after data dictionary is synchronized.