Unposted Journal Report from D365 F&O using Power Automate

Hello all,

It has been a while since I last wrote something. Truth is I haven’t been doing much with Dynamics 365 F&O and have been just focusing on the Power Platform as a whole. Any ways a few days back a requirement came through my desk where a controller wanted to see a list of all unposted Journal entries at the end of day in his/her email. I didn’t think there is a native report for this or a way to get this sent automatically to an email. This was a great example for a Power Platform solution so I decide to share with all.

1. The first step was to create a new flow and use the Dynamics 365 F&O connector. Use the “List items present in Table” action

The entity that will bring that data back is the “LedgerJournalHeaders”. We need to add a filter to the data so that it only show us the entries that have not been posted. The field in the entity that can provide this info is called “IsPosted”. Since this field is really not a text field but a Dynamics Enum data type the correct way to filter for “No” is as follows. “IsPosted eq Microsoft.Dynamics.DataEntities.NoYes’No'”

Make sure to select Cross Company as “Yes” to get all the legal entities unposted Journal entries.

The next step is to create a nice looking HTML table from the JSON outputs of the action above. The input for the create HTML table action is the value from the step above

The we can cherry pick the fields that we want to show in the report. To select them just use the item expression as below “item?[‘dataAreaId’]”

To format the numbers you can use this expression

“formatNumber(item()?[‘JournalTotalCredit’],’C2′)”

The final step is to send the email using the send to email action.

To make it as easy as possible for you to build this flow, I have put all of the actions into a scope action which you can copy into one of your own flows, so to implement this for yourself, follow these steps:

1. Create a new flow and add the manual trigger to test, after you can change it to run on a schedule trigger
2. Add a new action and then select from “My clipboard”

then paste the following code CTRL+V, this will be the code to create a new variable to style the HTML table

{"id":"df0051dd-5fa0-43bc-b7c8-f03a-64c403b4","brandColor":"#770BD6","connectionReferences":{"shared_office365_1":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_office365/connections/8d7c56c55da942b79593236a86dcede0"}},"shared_dynamicsax":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_dynamicsax/connections/shared-dynamicsax-4234cac7-b67a-437f-bda7-0f26-5bcf04f7"}},"shared_office365":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_office365/connections/7a6068d61ce14cec81c8e11a9ad6ffbb"}},"shared_office365users":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_office365users/connections/bd03cbfbebe04e84b7c6e20692827a6e"}}},"connectorDisplayName":"Variables","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzJweCIgaGVpZ2h0PSIzMnB4IiBlbmFibGUtYmFja2dyb3VuZD0ibmV3IDAgMCAzMiAzMiIgdmVyc2lvbj0iMS4xIiB2aWV3Qm94PSIwIDAgMzIgMzIiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyI+DQogPHJlY3Qgd2lkdGg9IjMyIiBoZWlnaHQ9IjMyIiBmaWxsPSIjNzcwQkQ2Ii8+DQogPGcgZmlsbD0iI2ZmZiI+DQogIDxwYXRoIGQ9Ik02Ljc2MywxMy42ODV2LTMuMjA4QzYuNzYzLDguNzQ4LDcuNzYyLDgsMTAsOHYxLjA3Yy0xLDAtMiwwLjMyNS0yLDEuNDA3djMuMTg4ICAgIEM4LDE0LjgzNiw2LjUxMiwxNiw1LjUxMiwxNkM2LjUxMiwxNiw4LDE3LjE2NCw4LDE4LjMzNVYyMS41YzAsMS4wODIsMSwxLjQyOSwyLDEuNDI5VjI0Yy0yLjIzOCwwLTMuMjM4LTAuNzcyLTMuMjM4LTIuNXYtMy4xNjUgICAgYzAtMS4xNDktMC44OTMtMS41MjktMS43NjMtMS41ODV2LTEuNUM1Ljg3LDE1LjE5NCw2Ljc2MywxNC44MzQsNi43NjMsMTMuNjg1eiIvPg0KICA8cGF0aCBkPSJtMjUuMjM4IDEzLjY4NXYtMy4yMDhjMC0xLjcyOS0xLTIuNDc3LTMuMjM4LTIuNDc3djEuMDdjMSAwIDIgMC4zMjUgMiAxLjQwN3YzLjE4OGMwIDEuMTcxIDEuNDg4IDIuMzM1IDIuNDg4IDIuMzM1LTEgMC0yLjQ4OCAxLjE2NC0yLjQ4OCAyLjMzNXYzLjE2NWMwIDEuMDgyLTEgMS40MjktMiAxLjQyOXYxLjA3MWMyLjIzOCAwIDMuMjM4LTAuNzcyIDMuMjM4LTIuNXYtMy4xNjVjMC0xLjE0OSAwLjg5My0xLjUyOSAxLjc2Mi0xLjU4NXYtMS41Yy0wLjg3LTAuMDU2LTEuNzYyLTAuNDE2LTEuNzYyLTEuNTY1eiIvPg0KICA8cGF0aCBkPSJtMTUuODE1IDE2LjUxMmwtMC4yNDItMC42NDFjLTAuMTc3LTAuNDUzLTAuMjczLTAuNjk4LTAuMjg5LTAuNzM0bC0wLjM3NS0wLjgzNmMtMC4yNjYtMC41OTktMC41MjEtMC44OTgtMC43NjYtMC44OTgtMC4zNyAwLTAuNjYyIDAuMzQ3LTAuODc1IDEuMDM5LTAuMTU2LTAuMDU3LTAuMjM0LTAuMTQxLTAuMjM0LTAuMjUgMC0wLjMyMyAwLjE4OC0wLjY5MiAwLjU2Mi0xLjEwOSAwLjM3NS0wLjQxNyAwLjcxLTAuNjI1IDEuMDA3LTAuNjI1IDAuNTgzIDAgMS4xODYgMC44MzkgMS44MTEgMi41MTZsMC4xNjEgMC40MTQgMC4xOC0wLjI4OWMxLjEwOC0xLjc2IDIuMDQ0LTIuNjQxIDIuODA0LTIuNjQxIDAuMTk4IDAgMC40MyAwLjA1OCAwLjY5NSAwLjE3MmwtMC45NDYgMC45OTJjLTAuMTI1LTAuMDM2LTAuMjE0LTAuMDU1LTAuMjY2LTAuMDU1LTAuNTczIDAtMS4yNTYgMC42NTktMi4wNDggMS45NzdsLTAuMjI3IDAuMzc5IDAuMTc5IDAuNDhjMC42ODQgMS44OTEgMS4yNDkgMi44MzYgMS42OTQgMi44MzYgMC40MDggMCAwLjcyLTAuMjkyIDAuOTM1LTAuODc1IDAuMTQ2IDAuMDk0IDAuMjE5IDAuMTkgMC4yMTkgMC4yODkgMCAwLjI2MS0wLjIwOCAwLjU3My0wLjYyNSAwLjkzOHMtMC43NzYgMC41NDctMS4wNzggMC41NDdjLTAuNjA0IDAtMS4yMjEtMC44NTItMS44NTEtMi41NTVsLTAuMjE5LTAuNTc4LTAuMjI3IDAuMzk4Yy0xLjA2MiAxLjgyMy0yLjA3OCAyLjczNC0zLjA0NyAyLjczNC0wLjM2NSAwLTAuNjc1LTAuMDkxLTAuOTMtMC4yNzFsMC45MDYtMC44ODVjMC4xNTYgMC4xNTYgMC4zMzggMC4yMzQgMC41NDcgMC4yMzQgMC41ODggMCAxLjI1LTAuNTk2IDEuOTg0LTEuNzg2bDAuNDA2LTAuNjU4IDAuMTU1LTAuMjU5eiIvPg0KICA8ZWxsaXBzZSB0cmFuc2Zvcm09Im1hdHJpeCguMDUzNiAtLjk5ODYgLjk5ODYgLjA1MzYgNS40OTI1IDMyLjI0NSkiIGN4PSIxOS43NTciIGN5PSIxMy4yMjUiIHJ4PSIuNzc4IiByeT0iLjc3OCIvPg0KICA8ZWxsaXBzZSB0cmFuc2Zvcm09Im1hdHJpeCguMDUzNiAtLjk5ODYgLjk5ODYgLjA1MzYgLTcuNTgzOSAzMC42MjkpIiBjeD0iMTIuMzY2IiBjeT0iMTkuMzE1IiByeD0iLjc3OCIgcnk9Ii43NzgiLz4NCiA8L2c+DQo8L3N2Zz4NCg==","isTrigger":false,"operationName":"varTablestyle","operationDefinition":{"type":"InitializeVariable","inputs":{"variables":[{"name":"varTablestyle","type":"string","value":"<style>\n        #Mytable_001 {\n            background-color: black;\n            border: 0px solid black;\n            width: 100%;\n            border-collapse: collapse;\n            border-spacing: 1px;\n        }\n\n            #Mytable_001 th {\n                color: rgb(255, 255, 255);\n                text-align: center\n            }\n\n            #Mytable_001 td {\n                color: rgb(255, 181, 0);\n                text-align: center\n            }\n    </style>\n<br>\n<p style=\"font-size:25px;\">The following entries have not been posted yet</p>\n"}]},"runAfter":{},"metadata":{"operationMetadataId":"090ac058-5989-468a-b391-df25306eba71"}}}

3. Then add a new action and paste the code for the remainder of the flow

{"id":"7e7a45e3-13dc-49b4-a05e-3d27-3f0abe8c","brandColor":"#8C3900","connectionReferences":{"shared_office365_1":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_office365/connections/8d7c56c55da942b79593236a86dcede0"}},"shared_dynamicsax":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_dynamicsax/connections/shared-dynamicsax-4234cac7-b67a-437f-bda7-0f26-5bcf04f7"}},"shared_office365":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_office365/connections/7a6068d61ce14cec81c8e11a9ad6ffbb"}},"shared_office365users":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_office365users/connections/bd03cbfbebe04e84b7c6e20692827a6e"}}},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Scope","operationDefinition":{"type":"Scope","actions":{"Lists_items_present_in_table":{"type":"OpenApiConnection","inputs":{"host":{"connectionName":"shared_dynamicsax","operationId":"GetItems","apiId":"/providers/Microsoft.PowerApps/apis/shared_dynamicsax"},"parameters":{"dataset":"bpbcpa-prod.operations.dynamics.com","table":"LedgerJournalHeaders","$filter":"IsPosted eq Microsoft.Dynamics.DataEntities.NoYes'No'","cross-company":true},"authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{}},"Create_HTML_table":{"type":"Table","inputs":{"from":"@outputs('Lists_items_present_in_table')?['body/value']","format":"HTML","columns":[{"header":"LegalEntity","value":"@item()?['dataAreaId']"},{"header":"JournalBatchNumber","value":"@item()?['JournalBatchNumber']"},{"header":"JournalName","value":"@item()?['JournalName']"},{"header":"Description","value":"@item()?['Description']"},{"header":"JournalTotalCredit","value":"@formatNumber(item()?['JournalTotalCredit'],'C2')"},{"header":"JournalTotalDebit","value":"@formatNumber(item()?['JournalTotalDebit'],'C2')"}]},"runAfter":{"Lists_items_present_in_table":["Succeeded"]}},"FormatHTMLtable":{"type":"Compose","inputs":"@replace(body('Create_HTML_table'), '<table', '<table id=\"Mytable_001\"')","runAfter":{"Create_HTML_table":["Succeeded"]},"metadata":{"operationMetadataId":"31d14366-a5c1-4596-984c-ef0853fb5f1f"}},"Send_an_email_(V2)":{"type":"OpenApiConnection","inputs":{"host":{"connectionName":"shared_office365_1","operationId":"SendEmailV2","apiId":"/providers/Microsoft.PowerApps/apis/shared_office365"},"parameters":{"emailMessage/To":"jgrijalba@bpbcpa.com","emailMessage/Subject":"Unposted Entries","emailMessage/Body":"<p>@{variables('varTablestyle')}<br>\n@{outputs('FormatHTMLtable')}</p>","emailMessage/Importance":"Normal"},"authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{"FormatHTMLtable":["Succeeded"]}}},"runAfter":{"varTablestyle":["Succeeded"]}}}

4. Update the connection to your Dynamics and your Email

Hope this is helpful

Until next time

Juan Sebastian Grijalba, CPA