Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Electronic Reporting (...
Finance | Project Operations, Human Resources, ...
Suggested answer

Electronic Reporting (ER): Find substrings, show characters right from index of substrings

(1) ShareShare
ReportReport
Posted on by Honk 8
Dear community!
 
I should have posted this question long before trying to solve it by myself. Meanwhile I even doubt it is possible in ER, but maybe the swarm intelligence of this community proves me wrong. I really hope it does..
So here goes:
 
I need to alter the content of a field containing a string of characters, for example:
Producer (Hardware) = Company Contoso Products-123123SPEC = BMQ3321,ASSR G4545 SPECCUST = ABP234234-43
What is now required is to find only the values for the attributes SPEC and SPECCUST, omitting all characters before, and also the attributes and the equal-symbols themselves...
It should look like:
BMQ3321,ASSR G4545  ABP234234-43
Is there any way in ER to do this?
 
Before I involve the same developer who concatenated this abomination in the first place (and then it would lead to lots of questions, documentation, more time and a system update) I was really hoping there might be a way with using a formula. 
 
P.S.
I had come up with 
REPLACE(REPLACE(STRINGVALUE,"SPECCUST = ","",false),"SPEC = "," ",false)
which solved the initial requirement before it was specified to this now... any help is appreciated!
 
Thanks in advance!
 
edit: redundant wording
Categories:
Dynamics 365 Finance
  • Honk Profile Picture
    Honk 8 on at
    Electronic Reporting (ER): Find substrings, show characters right from index of substrings
    Hi Saif,
     
    thank you for the quick reply and your explanation! However, your syntax would not work with the ER formula language.
    There is no SEARCH() function and variables cannot be initialized as well.. while I appreciate your insight very much, I'm afraid this won't help me.
     
     
  • Suggested answer
    Saif Ali Sabri Profile Picture
    Saif Ali Sabri 370 on at
    Electronic Reporting (ER): Find substrings, show characters right from index of substrings

    The ER formula language doesn’t directly support complex string parsing.  However, with a combination of SEARCH, RIGHT, LEFT, and REPLACE functions, you can get close to the desired output. Here’s a method to extract the values for "SPEC" and "SPECCUST" without the attributes and equal signs.

    Given your input:

     
    java
    Producer (Hardware) = Company Contoso Products-123123SPEC = BMQ3321,ASSR G4545 SPECCUST = ABP234234-43

    Approach

    1. Find the Position of "SPEC":
      Use SEARCH("SPEC = ", STRINGVALUE, 1) to locate where "SPEC" starts.

    2. Extract Right from "SPEC":
      After finding "SPEC", use RIGHT to extract everything after "SPEC = ".

    3. Remove Extra Text After "SPEC" Value:
      Find where "SPECCUST" starts, so you can remove any extra characters after the "SPEC" value and before "SPECCUST".

    4. Apply Similar Steps for "SPECCUST":
      Repeat steps 1–3 to get the value after "SPECCUST = ".

    5. Concatenate and Trim:
      Combine the extracted values for "SPEC" and "SPECCUST" and ensure there are no extra spaces.

    Example Formula

     
    plaintext
    SPEC_Value := LEFT(
    RIGHT(STRINGVALUE, LEN(STRINGVALUE) - SEARCH("SPEC = ", STRINGVALUE) - 6),
    SEARCH(" ", RIGHT(STRINGVALUE, LEN(STRINGVALUE) - SEARCH("SPEC = ", STRINGVALUE) - 6)) - 1
    );

    SPECCUST_Value := RIGHT(
    RIGHT(STRINGVALUE, LEN(STRINGVALUE) - SEARCH("SPECCUST = ", STRINGVALUE) - 9),
    SEARCH(" ", RIGHT(STRINGVALUE, LEN(STRINGVALUE) - SEARCH("SPECCUST = ", STRINGVALUE) - 9)) - 1
    );

    Concatenated_Value := TRIM(CONCATENATE(SPEC_Value, " ", SPECCUST_Value))

    Explanation

    • SPEC_Value: Finds the substring after "SPEC = " and captures only the characters up to the next space.
    • SPECCUST_Value: Locates "SPECCUST = ", then extracts everything after it up to the next space.
    • Concatenated_Value: Combines these values with a space between them.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

photo of Muhammad Affan

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

thumbnail image of a pie chart section

Top 10 leaders for November!

Congratulations to our November super stars!

thumbnail image of the word Tip

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,391 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,445 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans