Extract missing number sequence list using Electronic Reporting in D365FO

-


What's the Problem:



Customers having a huge number of transactions. The General Journal Transfer (GJT) numbers are used in all voucher entries posted in the system. These numbers are getting exhausted in a very short duration let assume in 2 months. Customer having doubt that many number sequences are missing for General Journal Transfer. They want to extract some reports which can provide the missing number sequences for GJT and get surety about missing number sequences.


There is an option "Check voucher series" under Ledger setup in the General Ledger module.


If we try to put the number sequence code here and extract the report, it comes blank (as this report is for voucher series, not support for other number sequences).


A temporary solution can be to use the Table browser, extract data from table GeneralJournalEntry for a particular date range into an Excel sheet. Put the expected number sequences, compare the data and identify the missing number sequences. Considering the huge transactions and limitation of the excel sheet to keep up to 10 lakhs rows, this solution is not a good one to use on regular basis.


Here is the solution:

Create a report of missing number sequences using Electronic Reporting functionality.

Logic for the report: 

  1. Identify the lowest and highest number sequence used in a given date range for GJT.
  2. Extract the number part from the number sequence. e.g if the number sequence is INMF-000012, then the number part is 000012.
  3. Create a parallel list with expected numbers between the range of Highest and Lowest number sequences identified in step 1.
  4. Then identify the records which are expected but not in the used number sequence. 
  5. This way, you will get the list of numbers that are missed in the given date range for GJT.
Let's apply this logic and create a report using Electronic Reporting. The purpose of this blog is not only to identify the missing number sequences but also for learning different formulas and their use in Electronic reporting.

I have prepared this configuration. A data model with model mapping and a CSV format.

Step 1: Click on the "Create configuration" option and select Root to prepare a data model from scratch. 






















Go to designer.


Step 2: Create the data model with a Root, Recordlist, and String.

Step 3: Click on "Map model to Datasource".






Step 4: Click on New, provide a description and click on the designer.










Step 5: Select "User Input Parameter" in Datasource type. Click on "add root".












Step 6: Select "Table records" in Datasource type. Click on "add root". Select the General Journal Entry table, also click on "ask for query" so that system will provide the "Records to Include" option for filtration purposes.































Step 7: Select "Calculated fields" in Datasource type. Click on "add root". Add the calculated fields as presented in the below table. Then click "Edit formula" and put the formula as suggested also in the below table.













 



The table containing the list of Calculated fields with formula and purpose.

Calculated Fields Formula Purpose
100_$SortedTable ORDERBY('015_GeneralJournalEntry', '015_GeneralJournalEntry'.JournalNumber) Use Order-by formula to sort the General Journal Entry table according to GJT (JournalNumber).
105_$ExtractedNumbers INTVALUE(VALUE(MID(@.JournalNumber, '005_StartFrom', '010_NumOfCharacters'))) Extract the numeric part from the number sequence using MID formula. For starting number and number of characters, user parameters are used that we defined in step 1. e.g extract "000012" from INMF-000012.
 
 Then Use VALUE formula to convert the string "000012" into real value "12.00000".
 
 Use INTVALUE to convert the real number into an integer from "12.00000" to 12.
200_$CountofRecords COUNT('100_$SortedTable') Get the count of records in General Journal entry table.
300_$MinExtractedNumber FIRSTORNULL('100_$SortedTable').'105_$ExtractedNumbers There is no formula of MIN or MAX, so we are using the sorting and reverse sorting option & Picking the first values using the FIRSTORNULL option.
To identify the minimum value of the number sequence, get the first value from Extracted numbers (already sorted A to Z above).
400_$MaxExtractedNumber FIRSTORNULL(REVERSE('100_$SortedTable')).'105_$ExtractedNumbers' To identify the maximum value of the number sequence, first, we will reverse the sorting from "A to Z" to "Z to A". Then pick the first value using the FIRSTORNULL option.
500_$CountOfExpectedRecords ('400_$MaxExtractedNumber'-'300_$MinExtractedNumber')+1 Get the count of expected records for number sequence assuming that no number sequence is missing. So, deduct a minimum value from the maximum value and add 1.
600_$ExpectedRecordList SPLIT(PADLEFT("#", '200_$CountofRecords', "#"), 1) Now we want to create a list where expected records will be there and from this list, we will compare and get the missing number sequences. To create a sequence of records equal to a number of expected records (we calculated in the previous step).
 
 First use PADLEFT formula, PADLEFT(string,length,padding_chars)
 
 Returns a string of a specified length in which the beginning of the current string is padded with specified characters.
 
 We took the string as "#" (any special characters you can use), length is equal to the number of expected records calculated in the previous step. Padding character is also taken as "#". It helps to create a value like "######################" here "#" will repeat up to the number of expected records.
 
 Then we will use the SPLIT function:
 SPLIT(string,numeric)
 
 Splits an input string into a list of substrings by length. A parameter specifies the maximum length of a substring.
 
 It creates a list with number of records equal to the number of expected records with value "#" in all records. We used numeric as 1, to split the above-mentioned character into the expected number of records.
700_$Sequence ENUMERATE('600_$ExpectedRecordList') Use ENUMERATE function to get the sequence from 1 to n.
705_$SequenceFromMinNumber (INTVALUE(@.Number)+'300_$MinExtractedNumber')-1 Then add one calculated field under the node "700_$Sequence". Here we are creating the number sequence starting from the Minimum extracted number sequence till Maximum extracted number sequence. Now we have a list where all the expected number sequences are there.
 
 To get the same, we added minimum extracted number to all values and deduct 1. then convert the value into an Integer using the INTVALUE function.
710_$Filter WHERE('100_$SortedTable', '100_$SortedTable'.'105_$ExtractedNumbers'=@.'705_$SequenceFromMinNumber') Now, first, filter the records in Expected Number sequences that match the Extracted number sequence ($SortedTable).
715_$CalculatedSequence IF(ISEMPTY(@.'710_$Filter'), 0, @.'710_$Filter'.'105_$ExtractedNumbers')+0 Add a calculated field under node "700_$Sequence" with logic "Put value as 0 where records are not available in $Sorted table in comparison to the expected number of records. For achieving the same, we used the IF and ISEMPTY function.
800_$Final WHERE('700_$Sequence', '700_$Sequence'.'715_$CalculatedSequence'=0) Get the final list where all missing number sequences are stored. Using the WHERE function with condition '715_$CalculatedSequence'=0.

After completing above steps, model mapping designer will look like the below image:




Step 8: Now bind the data model with $Final node












Step 9: Save the Model mapping and complete the configuration.




Step 10: Now create a format based on the above data model. I will be creating CSV format in this example (I will not be explaining in detail the format creation, it will be covered in a separate blog).

Step 11: Go to Designer. Prepare CSV format designer.

Step 12: Bind the Format designer to Data model.


Run the report and verify the record.

Fill in the User input parameters and select the date range.

Final Output:


That's it for this blog. See you in the next blog. 

Happy Learning! 
By Neeraj Kumar

Comments

Post a Comment

You might love these

Run ER reports using Electronic Messaging Functionality in D365

-
Image
 What's the Problem: After you've created your ER format, mapped it to data sources, and completed it, you can run it from the  Electronic reporting  workspace. After the report is generated, you can save it locally.  Due to this, we need to give access to the Electronic reporting Workspace to Business Users. To control the following aspects of the reporting process, set up electronic message processing: Log information about who generated the report. Log information about when the report was generated. Save the reports that were generated for previous periods. Solution: Create an electronic message processing in the system to run the required ER reports. In the Electronic messaging functionality, we can create a sequence of steps for users when trying to generate the EM processing. The following example shows how you can set up electronic messaging to generate a report that is based on an exporting ER format for Microsoft Excel. If you want to follow this example, the exportin
Read more

Bulk Posting of Journals in Dynamics 365 Finance and Operations

-
Image
  Bulk Posting of Journals  Have you got bored with posting journals on one-to-one basis? Okay, let’s explore something to make it interesting. There is a functionality of posting journals by selecting multiple journals with required criteria. After selection of desired journals, post all the journals in a single click ( OK ). All the processing will be same as of normal posting from the Journal form. Navigation: General Journal >  Journal Entries >  Post Journals Description of highlighted numbers: 1.     Select – Option for Select the desired Journals based on different criteria. 2. Late Selection This Boolean can be used for excluding the particular transaction or including it. If this Boolean is selected as “ No ”, then all the lines with Boolean “Yes” in the column “Late selection” will be get deselected and remaining lines will be available for posting. And vice versa. Note : By default, Column for “ Late Selection ” is not visible on the form but can be adde
Read more

Calculate Preposted tax document on Purchases order confirmation in D365FO using Electronic reporting

-
Image
What's the Problem : Some of the customers demand printing the tax amount in the Purchases order print at line level. As we know, there is no table in the standard D365 to save the line-level tax amount in any posted tables. To get the tax amount at line level, the tax document needs to be calculated at run-time with available information on the Purchases Order line. Presently we use the customized class to calculate the tax amount at line level using the below code : static void GSTTotal (Args _args) { vendPurchOrderJour vendPurchOrderJour; ITaxDocument taxDocument; ITaxDocumentComponentLineEnumerator componentLineEnumerator; ITaxDocumentComponentLine componentLineObject; ITaxDocumentMeasureEnumerator measureEnumerator; TaxAmount taxAmount,taxValue; TaxComponent_IN taxComponent; ; vendPurchOrderJour = vendPurchOrderJour::findRecId( 5637291565 );      /Need to pass Confirmation journal recid taxDocument = TaxBusinessService::getTaxDocument
Read more