Electronic Reporting (ER) : How to add Record counter in Model ?

(0) Share

Report

Posted on by RaghuD3FO

Hello All,

As part of a new custom ER configuration I am creating in D365 Finance and Operations, there is a need for the output text file to have a field/column which has the record count.

Header Record : the column will always be ‘000000001’.

Line Records: similar column should begin with value of ‘000000002’ and should increment by 1 for each record.

Example:

000000002

000000003

Trailer Record : the value should just be continuation of increment of the last value in Line Records.

Example:
000000004

Is there an ER function that can achieve this? COUNT function seems to only give the total count of records in data source. While INDEX seems to return the content in a list, if the position number is passed into the INDEX function?

Thank you,

Raghu

I have the same question (0)

All responses (11)

Answers (1)

Martin Dráb 237,965 Most Valuable Professional on at

Like (1)

Report

Have you considered ENUMERATE()?

Was this reply helpful? Yes No
Suggested answer

Laura Manache on at

Like (0)

Report

Dear Raghu,

Thank you for reaching out to Microsoft community.

To list the record counter, as Martion mentioned, we do recommend using the ENUMERATE ER function. Please kindly consider the below article for further guidance and and illustrative example.
docs.microsoft.com/.../er-functions-list-enumerate

Please let us know if this helps or if you need any other information, and we will be more than glad to assist further. Also, kindly mark as verified if the answer is helpful.

With kind thoughts,
Laura

Was this reply helpful? Yes No
RaghuD3FO 60 on at

Like (0)

Report

Martin and Laura, Thank you for the responses. I already considered using ENUMERATE but figured it cannot work out because ENUMERATE returns the entire record list (like the Vendor Info example in the MS Docs article).

I also considered using INDEX(ENUMERATE(<dataSource>).Number).Value.

Not sure if above will work, whether "ENUMERATE(<dataSource>).Number" will also return a record list, whose Value could be grabbed through INDEX command? But even if it does, how do I tell in the ER Format Model which value to be passed in index integer?

What I am looking for is a way to know which number record (of the datasource) is "cursor" on...like you can on a Form Datasource.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,965 Most Valuable Professional on at

Like (1)

Report

Correct me if I'm wrong, but it sounds to me that ENUMERATE() gives you exactly what you want (records and their indices), but you don't know how to work with lists.

What you should do is binding the list to a control that can display lists (such as XML sequence). Inside it, use controls for individual values. You'll refer to the index by @.Number. You don't need any extra functions or anything.

Your code suggests that you're trying to define a separate control for each row and enumerate the whole list again and again, which indeed doesn't make much sense.

Was this reply helpful? Yes No
Suggested answer

Arcadi 794 on at

Like (1)

Report

As Martin and Laura say you need ENUMERATE() and also COUNT() for the trailer record. You'll find the need of counting records in a lot of reports, so you'll need to master this:

Was this reply helpful? Yes No
RaghuD3FO 60 on at

Like (0)

Report

Thank you Martin and Arcadi. But I think where the disconnect is happening here is...that my output file is Text and not XML. Text is what this particular bank wants.

So, how do I add a control of type "XML Sequence" in this Text File format? I only see a control of type "Sequence" which is more like the ones I already have for Header, Line and Trailer. PLease see below screenshot :

Unless you are suggesting that I add this "Sequence" control and if yes, then I am wondering whether I could turn this "Excluded" radio button to YES. Does this "Excluded" option actually let me use the Control in my logic, but not actually have the control show up in the output file?

Was this reply helpful? Yes No
Arcadi 794 on at

Like (0)

Report

My example is a TXT too, you don't need any XML element

Was this reply helpful? Yes No
RaghuD3FO 60 on at

Like (0)

Report

Hi Arcadi,

I get it now on taking closer look at your screenshot. You are doing the Enumeration at the Data Model and NOT creating a Control in the Designer. This makes sense. I will try this and let you know how it goes. Thank you very much.

Was this reply helpful? Yes No
Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

I didn't say that you should use XML sequence. I said you need "a control that can display lists" and then I mentioned XML sequence as an example of such a control. If you're using a generic text form, use Sequence.

What do you mean by using the control in your logic but not showing it in the file? What is your business requirements if not adding the records to the output file?

Was this reply helpful? Yes No
RaghuD3FO 60 on at

Like (0)

Report

Martin and Arcadi, thank for your help. I am yet to test the overall solution more, but I was able to get this to the point that the model was recognizing the value and number within the Enumeration without errors/warnings.

Was this reply helpful? Yes No