MSDyn365FO. Electronic Reporting. Parse a text file with different record types.

In the previous blog post series we learned how to import simple CSV file. However, CSV and other text files may contain records of different types that should be imported to different tables or process differently. In this post we will enhance format and mapping created. Let’s say in our example first column represents records type (a, b or c):

CSVImportTestFile

In this case we need to change format and add new “CASE” element:

FormatCase

And 3 record sequences instead of one:

FormatCaseRecords

Note that each sequence has a predefined value for the String field, that’s how we tell ER that if record has “a” in the first column it should be parsed with RecordA sequence. Also, we changed Multiplicity to “One many” for the sequences, to tell ER that there is at least 1 record for each type. It could be set to “Zero many” if a record is optional or “Exactly one” if we expect it only once in a file.

Now we need to change mapping. Each sequence has system field “isMatched” that is populated if file record is matched to sequence pattern. We will use it to bind 3 record types to same model field, but in a real life examples different records may go to different tables, like header and lines.

MapModelToFormatCase

Expression used is pretty simple, it takes value from the first record if it is mapped, if not, it checks second record and if it is not mapped as well it takes value from the third.

IF(@.Types.RecordA.IsMatched, @.Types.RecordA.Data.FieldInt, IF(@.Types.RecordB.IsMatched, @.Types.RecordB.Data.FieldInt, @.Types.RecordC.Data.FieldInt))

Also using IsMatched you can provide default values when it is missing in a file.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s