Introduction: In NAV when building a custom report that deals with  totalling the amount by multiple G/L accounts by week then the report becomes tough to read as well as develop  in standard SSRS Report. A possible and easy workaround is dumping all the entries in the Excel and performing the pivot table operation on the entries to give a precise results. Pre-requisite: Microsoft Dynamics NAV 2017 Demonstration: 1. List of Variables: 2. UpdateRange  function to automatically update the sheet range of the current row: 3. Creation of Excel Entries Sheet: Creation of Excel Sheet: Creation of Column Titles: Insertion of Data Entries: 4. Pivoting the Data from the sheet containing entries: Pivoting operation in Excel takes place with the help of Pivot Cache. This Cache is modified using the Automation Variable. Conclusion: The method of using Automation Variable is allowed in On-Prem version of NAV, as it uses Excel libraries that already installed with Microsoft Office. Although this method works perfectly fine Windows Client, it does not work on WebClient. Thanks for reading!!

In the August 2018 update of Power BI, we can now apply conditional formatting based on field value. This allows us to use a measure or column that contains a color name, a hex name/code and apply that color to the background of the cells and the font in the cells of that column. For example, if I want to use color to signify the product name, we can create a calculated column to assign color names to each product as shown below: Then add that calculated column (‘Color’) to your table, and go to the Conditional Formatting dialog for ‘Color’. Select ‘Background Color’.   Select ‘Field Value’ in the ‘Format by’ drop-down menu. Since I only have one color name per row, I will select First ‘Column Name’, Summarization ‘First’  and click OK. The background color of the cells in the calculated column will now be according to the color name in the cell. If you don’t want to see the color names, repeat the steps but select ‘Font Color’ as shown below. The entire cell will now be filled. If you want to use hex names/codes instead of color names, you can create a calculated measure/column using them as shown below. Now follow the same steps as above to apply conditional formatting to the table using hex codes/names. This can help highlight sets of data easily based on their values and without the need to set rules.

Issue: In SSRS if we are using an aggregate function in a group at the Tablix level, then you may realize that the simple row numbering function does not give a current sequential ranking. Using the function RowNumber(Nothing) gives something like this. Using RowNumber(“GroupName”) would also give an incorrect row numbering and look something like this. This is because RowNumber does not actually give the row count. Rather it counts the incidences of the data in the group and returns that value. Solution: We can use the “RunningValue” function in SSRS. The format for the expression would be. =RunningValue(<Grouped field>,CountDistinct,”<DataSet>”) Eg: =RunningValue(Fields!Name.Value,CountDistinct,”Accounts”) This would return something like this.   This should fix your issue!

Introduction: In this blog we will demonstrate the working of HTTP Request -Response in Microsoft Flows. Implementation: Step 1:  In our example we will make a POST call to our Microsoft Flow so that we can further apply some logic in Flows. The POST request contains the following data. JSON: [{ “name”: “TestName”, “Address”: “TestAddress” }] Now we can use online editor tools to create a schema file of the above JSON. (https://jsonschema.net/). Submit the JSON data and the schema will be generated as shown below. Step 2: Now we go to Microsoft Flows,create a new flow and add the first step as “Request” as shown below: Step 3: We then paste the JSON schema that we earlier saved In the “Show advanced”  options we have to add the following details: Once that is done we select a condition. In our example we  will pass a JSON Array hence we will loop through each item. Here we add a apply to each condition as shown And we add a dynamic output from the previous stage Then inside the apply to each loop we can add any action and take the values passed from the POST request. For this example I create a new account in CRM with the name and address provided as shown below Step 4: Now when we save this flow a URL is generated in the First HTTP Request step. We test the flow by calling this URL from Postman. The URL generated is show below Step 5: Now we open Postman and write the request as shown below Also in the Header section we add the following details Step 6: Once we click on Send the two records are created in CRM as shown below Conclusion: We can call this URL from outside Dynamics CRM as well and using this we explore many more functionalities provided by Microsoft Flows to achieve the desired outcome.

Introduction: In this article, we will learn how to dynamically use Dynamics 365 CRM option set/ CRM picklist in Power BI. As you may be aware that CRM picklist stores Integer value in the backend and not the actual display name. So, whenever you query an entity with picklist fields in Power BI you will get picklist Integer value and not the display names. Pre-requisite: The option set/picklist should be a global. You can check the IsGlobal property for the picklist field in the Metadata Browser. In Metadata Browser, when you expand Get Options for OptionSet there is a (+) button. Click on that button and option set properties will appear. Steps to get Option Set Values: Login in to Power BI Click Get data Select Odata Feed as the connector Enter the OData URL for querying the Global option sets and click Ok. URL Format: https://orgname.api.crm.dynamics.com/api/data/v8.2/GlobalOptionSetDefinitions(OptionSetMetadataId) Example: https://contoso.api.crm.dynamics.com/api/data/v8.2/GlobalOptionSetDefinitions(ae2a3b9d-395a-4dbd-9abc-c32aeb10888b)Note: OptionSetMetadataId is highlighted in the Pre-requisite screen capture Once the query is loaded, the Edit Query window will pop up.  Click on Advance Editor and modify the code as below (Add null and MoreColumns parameter) and then click Done: let Source = OData.Feed(“https://fasttrackleasingllc.api.crm.dynamics.com/api/data/v8.2/GlobalOptionSetDefinitions(ae2a3b9d-395a-4dbd-9abc-c32aeb10888b)”, null, [MoreColumns = true]) in    Source After loading, an additional row will be added to the table named More Columns. Click on the Record field beside More Columns. A new view of the table will be loaded as shown in the below screenshot. Right Click on List and Select ‘Into Table’ Once the table is loaded, expand the Value column by clicking on ‘Expand to New Rows’ Expand the Value Column and select ‘Value’ and ‘Label’ Columns. Click Ok. Expand the Value.Label Column and select ‘UserLocalizedLabel’ Column. Click OK. Expand the Value Column and select ‘Value’ and ‘Label’ Columns. Click Ok. Finally, we get the Option set Code (Value.Value) and Option Set Value (Value.Label.UserLocalizedLabel.Label) from CRM. Conclusion: As the above method creates a separate table for option set, you have to add relationship (1-*) between the newly created Option Set table and the Main table.

Introduction: While scheduling a meeting from Outlook client, sometimes we may also need to select the room where the meeting will be held. Room Finder in Outlook client helps us to find the available rooms. Sometimes it may happen that when a user creates a new meeting in Outlook, no conference rooms are listed in Choose an available room box in Room Finder. The issue is that the user doesn’t select a room list. Users can select a room list from Show a room list option in Room Finder. But before that admin needs to create a room list so that the option “Show a room list” will be visible in Room Finder. Steps: Open Exchange Online Power Shell and connect your Office 365 environment. Run the following command to create a Room List Distribution Group – New-DistributionGroup <RoomListName> -RoomList -Members $Members<RoomListName> – Provide a name to the Room list. Run the following command to add existing rooms to the list –Add-DistributionGroupMember <RoomListName> -Member <RoomMailbox><RoomListName> – Put the name of the room list which was created in the earlier step.<RoomMailBox> – Put the name of the room mailbox. You can find the room mailbox name from the Exchange Admin Center. If you want to add multiple rooms at once, you need to create a .txt file and add all the room mailboxes and save the file. Run the following command to add multiple rooms –Get-Content <EnterFileLocation> | Add-DistributionGroupMember -Identity <RoomListName> <EnterFileLocation> – Where the .txt file has been saved.<RoomListName> – Provide the room list name. You might need to confirm whether the rooms have been added to room list. Run the following command to check the same –Get-DistributionGroupMember -Identity <RoomListName><RoomListName> – Put a name of the room list, which you have created. After following above steps, you will be able to see the Show a room list option in Room Finder. There you can choose the room list and select the rooms. Conclusion: This is how you can enable Room List in Room Finder, which helps users while scheduling a meeting through Outlook clients and can easily see the available rooms and timings through Room Finder. Want to get consultation or training for your company? Please email us at ashah@cloudfronts.com

Introduction: This blog explains how to enable custom field in Filter section of Schedule Board in D365 Field Service & PSA. Scenario: Need to filter resources on Schedule Board by custom fields other than standard fields like characteristics, roles, business units, terriotries etc. Pre-requisites: Configure 2 Custom Entities with below Details 1. Crew Entity with Name as text field. 2. Crew Member Entity with Crew and Bookable Resource as lookup field. Steps: Below are steps to configure custom field “Crew” of type lookup in filter section of Schedule Board. 1. Open Schedule Board to Customize 2. Customize the Filter Layout Template Open Filter Layout as highlighted below. Insert below code above controls closing tag. <control type=”combo” source=”entity” key=”ResourceCrew” label-id=”Crew” entity=”cf_crew” multi=”true” /> Click “Save” button to save the changes to Schedule Board. Crew field will be shown in filter section as highlighted below. 3. Customize the Retrieve Resources Query. Open Query Template as highlighted below. Insert below code in Query template above closing tag of filter. Click “Save” button to save the changes to Schedule Board. 4. Click “Apply” button to save the changes to Schedule Board. 5. Select Crew and click Search button in Filter Section and  Resources are filtered as below Refer this link for more information. Conclusion: This blog explains steps to filter resources based on custom entities in Filter section of Schedule Board in D365 Field Service and PSA.

Overview: Now, you often must have noticed that the field Est. Revenue on Opportunities is either locked or is open to enter value into it. There’s a reason for this as well. Revenue Field: So basically, there is a field on the Opportunity that drives this operation. It is called as Revenue field! System Calculated- If you select System Calculated, the Opportunity Lines’ Total Amount will add up to the Est. Revenue field and the field will be locked for you. User Provided- If you select User Provided, the Est. Revenue field will be unlocked with whatever value it already has and you can change the same. It will no longer be driven of the Opportunity Lines.

Introduction: If you use Microsoft Dynamics 365 for Sales for customer engagement then you can use Microsoft Dynamics 365 Business Central for order processing and finances and have seamless integration in the lead-to-cash process. When you integrate you application with sales, you can have all the access to Sales data from Business Central and the other way around in some cases. Thus, Integration enables you to work with and synchronize data types that are common to both services, such as customers, contacts, and sales information, and keep the data up to date in both locations. Pre-requisites: Microsoft Dynamics 365 Business Central Microsoft Dynamics 365 for Sales Demonstration: Steps for Integration: 1. Business Central and Microsoft Dynamics 365 Setup Go to Business Central, in the Setup and Extensions select Assisted Setup. Select Set up Dynamics 365 for Sales Connection for establishing a connection between Business Central and Sales in Microsoft Dynamics 365. In the textbox, Insert the link of the Microsoft Dynamics 365 Sales. Click on Next and insert the Email and Password for the user that will be used for Synchronization between Business Central and Microsoft Dynamics 365 Sales. Click on Finish. Then insert the credentials of the user account which must be used to import the solution. The user account must have Security Roles of System Administrator and Solution .   2. Couple Business Central Salespeople to Microsoft Dynamics 365 User Before a customer can be synchronized to an account, the salesperson that is assigned to the customer must be coupled to a user in Sales. Go to that Salesperson and in navigate select Coupling. A coupling provides the basis for integrating records in Microsoft Dynamics 365 with records in Business Central. Essentially a coupling associates or links a Microsoft Dynamics 365 record to a Business Central record. Hence, coupling needs to be setup for integration. Select the CRM user you want to synchronize the Salesperson with and click on OK. If you want to synchronize data in the account in Microsoft Dynamics 365 and the customer in Business Central, set the Synchronize After Coupling field to one of the following options. Yes – Use the Business Central Data : Copies the data from mapped fields of the customer in Business Central to the account in Microsoft Dynamics 365. Yes – Use the Dynamics 365 Sales for Data: Copies the data from mapped fields of the account in Microsoft Dynamics 365 to the customer in Business Central. 3. Couple Business Central customers to Microsoft Dynamics 365 In the Customer table of Business Central, select the customer you want to couple with Account in Microsoft Dynamics 365 Sales. Set the Coupling of the Customer with the related Account in Microsoft Dynamics 365 and click on OK. 4. Couple Business Central Unit of Measures to Microsoft Dynamics 365 Units Groups In the Unit of Measure table of Business Central, select the Unit of Measure you want to couple with the Unit Group in Microsoft Dynamics 365 Sales. Set the Coupling of the Measure with the related Unit Group in Microsoft Dynamics 365 and click on OK. 5. Couple Business Central Currencies with Microsoft Dynamics 365 transaction Currency In the Currencies table of Business Central, select the Currency you want to couple with the Transaction Currency in Microsoft Dynamics 365 Sales. Set the Coupling of the Currency with the related Transaction Currencies in Microsoft Dynamics 365 and click on OK. 6. Couple Business Central items to Microsoft Dynamics 365 products In the Items table of Business Central, select the item you want to couple with the Product in Microsoft Dynamics 365 Sales. Set the Coupling of that item related to the Product in Microsoft Dynamics 365 and click on OK. 7. Sales Order Integration Create a Sales Order in Microsoft Dynamics 365 and when submitted it appears in Business Central. After submitting sales order, in the search box of Business Central enter Sales Orders, and then choose the related link. In the Sales Orders window, on the Navigate tab, in the Dynamics 365 for Sales group, choose Sales Order List. The Microsoft Dynamics 365 Sales Orders windows opens to display a list of only sales orders in Microsoft Dynamics 365 that have their status as Submitted. Select the Create in Central Business Action to get the Sales Order in Business Central. 8. Sales Invoice Integration On selecting the post action, a posted invoice is generated in Business Central and the status of the Order changes from Submitted to Invoiced. Status changed to Invoiced. In the Posted Sales Invoice window of Business Central, on the Navigate tab, in the Dynamics 365 Sales group,choose  Create Invoice in Dynamics 365 for Sales. Go to Microsoft Dynamics 365 check the Invoices. Conclusion: Thus, we have integrated Microsoft Dynamics 365 Business Central and Microsoft Dynamics 365 for Sales. The table below indicates the direction of mapping of different Data Types. Sr No. Business Central Entity Mapping Direction D365 Entity Default Filter 1 Salespeople/Purchaser Sales -> Business Central User Sales contact filter:  Status is No, User Licensed is Yes, Integration user mode is No 2 Customer Business Central -> Sales and Sales -> Business Central Accounts Sales account filter:  Relationship Type is Customer and Status is Active. 3 Unit of Measure Business Central -> Sales Unit Groups 4 Currency Business Central -> Sales Transaction Currency 5 Items Business Central -> Sales and Sales -> Business Central Product Sales contact filter: Product Type is Sales Inventory 6 Sales Order Sales -> Business Central Sales Order 3 Posted Sales Invoice Business Central -> Sales Invoice

Introduction: In this blog we will discuss some extended custom functionality of uploading documents to SharePoint and CRM. Purpose: By default, document location is enabled for some OOB entities like Accounts, Contacts, Opportunities and by default document location gets created for records on first time load of Document Location associated view. This is the OOB functionality but sometimes we might need some different structures for document storage according to the organization need. In this blog, we will discuss one scenario where we can have a need to customize this Document Locations and automatically create a predefined folder structure in CRM and SharePoint which will help us to store our documents according to needed structure. This functionality is described using the below screenshots. Open Documents view for any record. As you can see by default, document location gets created and documents get stored in highlighted location. We have created a custom button which takes parameters and create or update the folder structure. On click of Custom button, window opens where you can enter values and click Ok. Type: Create or Update Structure: Tree or Acyclic (Depends on Organization) URL: This is the SharePoint URL Site where you want to create folder structure. You can see the predefined folders getting created. You select the folder you want to upload the file. Chose file and click Ok. In below screenshot you can see the file gets uploaded in the selected folder. Similarly, you can see in SharePoint how the folders get created and file automatically gets uploaded.