[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8296
  • Last Modified:

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".
0
Damian_Gardner
Asked:
Damian_Gardner
  • 11
  • 9
1 Solution
 
quale-kaizenCommented:
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.
0
 
Damian_GardnerAuthor Commented:
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
0
 
Damian_GardnerAuthor Commented:
also forgot to ask - if I want to remove carriage returns, how is that specified?  

Thanks
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
quale-kaizenCommented:
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

0
 
Damian_GardnerAuthor Commented:
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
0
 
Damian_GardnerAuthor Commented:
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
0
 
quale-kaizenCommented:
Well, you can't. What you can do is create a dispaly method and use it on your forms or reports.
0
 
Damian_GardnerAuthor Commented:
Ok - so - this won't edit the value that comes up in the view field when I select records from it then?  
0
 
quale-kaizenCommented:
It would only display the corrected value for the field.

In AX you can't use views to update or create new records.
0
 
Damian_GardnerAuthor Commented:
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
0
 
quale-kaizenCommented:
Sure.
As a second step you can avoid inserting records with '\n' to the DB.
0
 
Damian_GardnerAuthor Commented:
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
0
 
quale-kaizenCommented:
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
0
 
quale-kaizenCommented:
This is how you would update the 'Street' field on the 'CustTable' table.
To run this code you need to create a new job and paste this code:
static void fixAddress(Args _args)
{
    CustTable custTable;
    ;
 
    While Select ForUpdate custTable
    {
        ttsBegin;
        custTable.Street = strRem(custTable.Street, '\n');
        custTable.doUpdate();
        ttsCommit;
    }
    
    info('done');
}

Open in new window

0
 
Damian_GardnerAuthor Commented:
Ok - thanks.  Let me try and let you know.

0
 
Damian_GardnerAuthor Commented:
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?
0
 
quale-kaizenCommented:
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
0
 
Damian_GardnerAuthor Commented:
Well - maybe I'll leave that alone then.
0
 
Damian_GardnerAuthor Commented:
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?
0
 
quale-kaizenCommented:
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.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 11
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now