Dynamics Community Forum Thread Details

Electronic Reporting (ER) formula language for MT940 line86 split string with "?" or ":" without taking in next line information

Hi everyone,

Good day to all of you, i have a requirement to split string in line86, my current formula is taking in information from line61 as there is only 1 subtag in some lines in the file example:

:86:X02?20ABCD
:61:2345436436754,00CE02//CHQ

Result:

ABCD :61:2345436436754,00CE02//CHQ | | |

There will be some line with 4 Subtags with result below with my formula:
:86:R08?20CHH?21From: PETER & CO PTE LTD?22PayNow?23OTHER COMPANY NAME
:62F:242341234124,24 :64:2341412412423-}

Result:
CHH | From: PETER & CO PTE LTD | PayNow | OTHER COMPANY NAME :62F:242341234124,24 :64:2341412412423-}

How can I fix to accommodate for any number of subtags in line86 without taking information from other lines. Thank you so much

My formula

IF(CONTAINS(@.Line86,"?20"),FIRST(SPLIT(INDEX(SPLIT(@.Line86,"?20"),2).Value,

IF(CONTAINS(INDEX(SPLIT(@.Line86,"?20"),2).Value,""), "?",":"))).Value,"")

& " | " &

IF(CONTAINS(@.Line86,"?21"),FIRST(SPLIT(INDEX(SPLIT(@.Line86,"?21"),2).Value,

IF(CONTAINS(INDEX(SPLIT(@.Line86,"?21"),2).Value,""), "?",":"))).Value,"")

& " | " &

IF(CONTAINS(@.Line86,"?22"),FIRST(SPLIT(INDEX(SPLIT(@.Line86,"?22"),2).Value,

IF(CONTAINS(INDEX(SPLIT(@.Line86,"?22"),2).Value,""), "?",":"))).Value,"")

& " | " &

IF(CONTAINS(@.Line86,"?23"),FIRST(SPLIT(INDEX(SPLIT(@.Line86,"?23"),2).Value,

IF(CONTAINS(INDEX(SPLIT(@.Line86,"?23"),2).Value,""), "?",":"))).Value,"")

Hi Benjamin,

Because your formula is already too complex I would go step by step.

1 - Take out lines after the 86, with something like:

FIRST(
    SPLIT(
        Replace(
        @.Line86,
        ":[0-9]",
        "{[(NEWLINE)]}",
        true),
    "{[(NEWLINE)]})"
)

Another way to do it is to modify Microsoft own formula, and make sure it only takes the first line that starts by 86, I can't test it but it may look something like:

1 - Split by new line

SPLIT(
    REPLACE(
        format.StatementFile.Statement.BankStmtLine.VALUE
        , "(\r\n|\r|\n):"
        , "{[(NEWLINE)]}:", true),
    "{[(NEWLINE)]}"
)

And then just take the first (or the last like MS with reverse) line that starts by 86:

FIRST(REVERSE(WHERE(@.PreviousStep,LEFT(@.PreviousStep.Value,4)=":86:")))

I'm sure I've made a lot of mistakes, but I hope you get the idea. Solution 1 is faster, 2 is cleaner. And of course after the transformation you have to point to this new node with your current formula.

Quick Links

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