Fun with REPLACE ER function in D365FO

-



Please visit http://regexstorm.net/Tester before reading the blog.


REPLACE ER function


REPLACE FUNCTION

The REPLACE function returns the specified text string as a String value after all or part of it has been replaced with another string.

Syntax

REPLACE (text, pattern, replacement, regular expression flag)

Arguments

text: String

The valid path of a data source of the String type.

pattern: String

If the regular expression flag argument is TRUE, this function returns the specified string after it has been changed by applying the regular expression that is specified by the pattern argument. The regular expression is used to find the characters that must be replaced.

Example: REPLACE ("+1 923 456 4971", "[^0-9]", "", true) applies a regular expression that removes all non-numeric symbols, and it returns "19234564971".

If the regular expression flag argument is FALSE, this function returns the specified string after the set of characters that are defined in the pattern argument have been replaced by characters of the replacement argument.

Example: REPLACE ("abcdef", "cd", "GH", false) replaces the pattern "cd" with the string "GH" and returns "abGHef".

Let's see different scenarios where REPLACE function is useful. All the given scenarios can be tested either in REGEX TESTER or in the Electronic reporting formula designer.

#001::Extract a word just after a particular word.

For instance, the colour name is to be extracted in the text “Size : XL Colour : Blue Style : Uppercut”. Here expected output is “blue”. Similarly, size = XL and Style = uppercut.

Input text

 

Size : XL Colour : Blue Style : Uppercut

Pattern

.+Colour\W+(\w+).+

Use in FUNCTION

REPLACE(CONCATENATE(“ “,“Input_text”,” “), “.+Colour\W+(\w+).+”,”$1”,TRUE)

Explanation

Here we have added space characters in the beginning and at the end; so as to allow the above pattern “.+” to search even for “Size” and “Style”.

Pattern

Description

.+

Matches any one or more characters

\W+

Matches the one or more word characters

(w+)

Capture the first word after the matched character “colour” as the first group

$1

Picks the first group as a replacement

Output

Blue



#002::Remove line breaks from the postal address

In D365, addresses are kept with line breaks e.g.

“H-25, Sector 12 Noida,

Uttar Pradesh,

201301,

IND”

This needs to be converted to “H-25, Sector 12 Noida, Uttar Pradesh, 201301, IND”

Input text

H-25, Sector 12 Noida,

Uttar Pradesh,

201301,

IND

Pattern

\n

Use in FUNCTION

REPLACE(“Input_text”, “\n”,” ”,TRUE)

Explanation

“\n” pattern represents the line breaks, we have replaced all line breaks with a space character.

Pattern

Description

\n

Matches all line breaks

Output

H-25, Sector 12 Noida, Uttar Pradesh, 201301, IND


#003::Remove numeric characters from a string

In a PAN card number, you want to remove the numeric characters and print only Alphabetical characters.

From “DXEPK8447E” to “DXEPKE”

 

Input text

DXEPK8447E

Pattern

[0-9]

Use in FUNCTION

REPLACE(“Input_text”, “[0-9]”,””,TRUE)

Explanation

Here we used 0 to 9, any other combination will also work like 1-5 means all numbers between 1 to 5.

Here, if you want to ignore certain numbers from the range (assume 5 and 7), then this pattern should be “[0-9-[5,7]]”

Pattern

Description

[0-9]

It represents all numeric characters from 0 to 9.

[0-9-[5,7]]

Here “-[ ]“ represents the exception block

Output

DXEPKE


#004::Remove non-numeric characters from a string

In a PAN card number, you want to remove the non-numeric characters and print only numeric characters.

From “DXEPK8447E” to “8447”

 

Input text

DXEPK8447E

Pattern

[^0-9]

Use in FUNCTION

REPLACE(“Input_text”, “[^0-9]”,””,TRUE)

Explanation

Here we used “^” to create a negative group of characters 0 to 9, any other combination will also work like ^1-5 means all characters other than 1 to 5.

Pattern

Description

[^0-9]

It represents all characters except the numbers from 0 to 9.

Output

8447


I will keep adding new patterns and scenarios in this blog post. Keep visiting the page.

That's it for this blog. See you in the next blog. Please comment if you have any suggestions about more use/scenarios of REPLACE function.

Happy Learning! 
By Neeraj Kumar


Comments

Post a Comment

You might love these

Run ER reports using Electronic Messaging Functionality in D365

-
Image
 What's the Problem: After you've created your ER format, mapped it to data sources, and completed it, you can run it from the  Electronic reporting  workspace. After the report is generated, you can save it locally.  Due to this, we need to give access to the Electronic reporting Workspace to Business Users. To control the following aspects of the reporting process, set up electronic message processing: Log information about who generated the report. Log information about when the report was generated. Save the reports that were generated for previous periods. Solution: Create an electronic message processing in the system to run the required ER reports. In the Electronic messaging functionality, we can create a sequence of steps for users when trying to generate the EM processing. The following example shows how you can set up electronic messaging to generate a report that is based on an exporting ER format for Microsoft Excel. If you want to follow this example, the exportin
Read more

Bulk Posting of Journals in Dynamics 365 Finance and Operations

-
Image
  Bulk Posting of Journals  Have you got bored with posting journals on one-to-one basis? Okay, let’s explore something to make it interesting. There is a functionality of posting journals by selecting multiple journals with required criteria. After selection of desired journals, post all the journals in a single click ( OK ). All the processing will be same as of normal posting from the Journal form. Navigation: General Journal >  Journal Entries >  Post Journals Description of highlighted numbers: 1.     Select – Option for Select the desired Journals based on different criteria. 2. Late Selection This Boolean can be used for excluding the particular transaction or including it. If this Boolean is selected as “ No ”, then all the lines with Boolean “Yes” in the column “Late selection” will be get deselected and remaining lines will be available for posting. And vice versa. Note : By default, Column for “ Late Selection ” is not visible on the form but can be adde
Read more

Calculate Preposted tax document on Purchases order confirmation in D365FO using Electronic reporting

-
Image
What's the Problem : Some of the customers demand printing the tax amount in the Purchases order print at line level. As we know, there is no table in the standard D365 to save the line-level tax amount in any posted tables. To get the tax amount at line level, the tax document needs to be calculated at run-time with available information on the Purchases Order line. Presently we use the customized class to calculate the tax amount at line level using the below code : static void GSTTotal (Args _args) { vendPurchOrderJour vendPurchOrderJour; ITaxDocument taxDocument; ITaxDocumentComponentLineEnumerator componentLineEnumerator; ITaxDocumentComponentLine componentLineObject; ITaxDocumentMeasureEnumerator measureEnumerator; TaxAmount taxAmount,taxValue; TaxComponent_IN taxComponent; ; vendPurchOrderJour = vendPurchOrderJour::findRecId( 5637291565 );      /Need to pass Confirmation journal recid taxDocument = TaxBusinessService::getTaxDocument
Read more