web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Substring seach in ER.
Finance | Project Operations, Human Resources, ...
Suggested Answer

Substring seach in ER.

(3) ShareShare
ReportReport
Posted on by Lexington83 56

Hi,

I’ve had a request to enhance a customer remittance import configuration I built in ER so that it can handle substring matching. Sometimes customers send remittance files where the invoice number provided is 12345, but the original invoice number in the system might be something like INV12345.

I can work around some example by using text functions like LEFT, RIGHT, and MID, or reconstruct the expected invoice number using concatenation. However, I wanted to check whether anyone is aware of a substring, fuzzy‑match, or approximate‑match function available in ER formulas, or whether there is a native X++ method that ER can call to support this.

Any ideas or recommended approaches would be appreciated.

Thanks,

Lex

Categories:
Dynamics 365 Finance
I have the same question (0)
  • Suggested answer
    CA Neeraj Kumar Profile Picture
    CA Neeraj Kumar 5,099 Super User 2026 Season 1 on at
    Hi Lex,
     
    Can you please elaborate a little bit about the place where you are trying to write some ER logic (either in Import format mapping/ or in Destination)? 

    To check whether the string contains a particular text, we have CONTAINS function. https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/analytics/er-functions-logical-contains

    It gives true or false value.
     
    There is another function of REPLACE, which handles all kind of text conversions using REGEX (regular expression). If you can elaborate more about the requirement, i can exactly tell the formula to convert or identify.
     
    Regards,
    CA Neeraj Kumar
    Author of the Book - Electronic Reporting - The Hidden Gem
  • Suggested answer
    Alireza Eshaghzadeh Profile Picture
    Alireza Eshaghzadeh 14,839 Super User 2026 Season 1 on at
    Hi @Lexington83,
    In general, the recommended approach is that customers pay their invoices using the same payment ID and invoice number that they receive.
    If the invoice number in the bank file does not match the invoice number in D365FO, you will need to build a formula on the matching side so that the matching can occur in multiple steps. For example, you can:
    -Attempt exact match first, and if no result is found,
    -Apply approximate matching using functions such as replace or split to normalize the invoice number before comparison.
    In addition, consider using debtor information (customer account) as the first matching criterion, and then match the open invoices using the approximate matching logic described above.

     
  • Lexington83 Profile Picture
    Lexington83 56 on at

    Thanks, Neeraj Kumar. When mapping to the model, I need to validate the invoice number — checking whether it’s valid, I am already doiing a few checks like whether it has already been allocated to a payment, and whether there is any remaining open amount. One of the scenarios I’m handling is where the provided invoice number is only a partial string of the real invoice number.

    For example, if the full invoice number is normally 11 characters (e.g., INV00012345) but the data only contains 12345, I need to search for that partial value within the full list of invoices. What I was considering is creating a rule set for the most common patterns, reconstructing the likely full invoice number, and then searching based on that. A contains check works for string construction, but I’m not sure how best to apply it when searching across a list of strings.

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

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Congratulations to the February Top 10 Community Leaders

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 510 Super User 2026 Season 1

#2
Giorgio Bonacorsi Profile Picture

Giorgio Bonacorsi 399

#3
Adis Profile Picture

Adis 251 Super User 2026 Season 1

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans