Advertisements

Dynamics Portal – ‘No list could be found with the relative URL’ Error – Fix

November 13, 2018 Leave a comment

Recently I was working on Dynamics Portals and SharePoint integration and while uploading the document from ‘Case’ entity, I was getting below error

SP_Error_1

Reason:

  • ‘Document Management’ was not enabled for ‘Case’ entity.

Fix:

  • Make sure ‘Document Management’ is enabled for ‘Case’ entity
    • Connect to Dynamics 365 Application
    • Open Settings -> Customizations -> Customize the System
    • Select ‘Case’ Entity and make sure ‘Document Management’ is checked.

SP_4

  • Go to Settings -> Document Management Settings
  • In the ‘Select entities’ grid, make sure ‘Case’ entity is selected.

SP_1

  • Click ‘Next’ and ‘Select folder structure’

SP_2

  • Click ‘Next’ to complete the set up

SP_Error_2

  • Try uploading the document from Portal and should be able to upload the document.

SP_13

  • Try restarting the Portal if you still getting the exception.

🙂

Advertisements

[Step By Step] Dynamics Portals – SharePoint Integration

November 13, 2018 1 comment

In this article I am going to walk you through the step by step process to integrate Dynamics Portal with SharePoint.

Why we need to integrate Portal with SharePoint?

  • To answer the question, lets take a scenario, A company has an Employee Health portal where employees upload their Medical Bills and diagnostic reports to reimburse the money incurred due to hospitalization. On the other side Companies admin team access the documents either from D365 application or SharePoint portal to validate and release the money.
  • If the documents get uploaded to SharePoint through the Portal, with the robust SharePoint document management features, the documents can be edited, versioned and accessed anywhere online just from a browser.

Now lets see the steps to integrate portals with SharePoint.

Pre-requisites:

  • Dynamics 365 subscription (Get 30 days trail)
  • SharePoint Site
  • Dynamics Portal

Configure SharePoint Site:

  • Once you have your Dynamics 365 subscription, login to your Office 365
  • Click on ‘SharePoint’ link to connect to SharePoint Portal

SP_14

  • Next step is to create a new ‘Site’

SP_15

  • Click on ‘Create Site’ and fill the details to complete Site creation.
  • Copy the URL which you would need in next steps.
    • I created a new site with name ‘D365’

SP_16

Configure SharePoint Site in D365 Application:

After creation of SharePoint Site in previous section, now we need to add this details in D365

  • Open your Dynamics Application
  • Go to Settings -> Document Management Settings
  • In the ‘Document Management Settings’ window, paste the SharePoint Site URL copied in previous section in ‘SharePoint Site:‘ text box.

SP_1

  • In the ‘Select entities’ grid, select the entities you would like to add Documents from your portal.
    • I selected ‘Case’ entity as I am using in this article
  • Click ‘Next’ and ‘Select folder structure’

SP_2

  • Click ‘Next’ to complete the set up.

SP_Error_2

SP_17

  • Click on ‘Set up SharePoint integration’ -> Enable SharePoint integration

SP_3

  • Provide credentials and complete the set up

SP_11

Configure ‘Case’ entity portal form and Entity Permissions:

To be able to upload documents from Portal, we need to add a sub-grid on Case form.

  • Connect to Dynamics 365 Application
  • Open Settings -> Customizations -> Customize the System
  • Select ‘Case’ Entity and make sure ‘Document Management’ is checked.

SP_4

  • Open ‘Case -> Forms -> Web-Edit Case‘ form
    • Note: As I am going to upload documents from existing Case in Portal, I chosen ‘Web-Edit Case’ form. Chose your from as per the requirement.

SP_5

  • Add a new sub-grid with below details.
    • Entity – Document Locations

SP_6

  • Save and Publish.

Lets grant ‘Entity Permissions’ to ‘Sharepointdocumentlocation’ entity whoever having access to ‘Case’ entity.

  • Navigate ‘Portals -> Entity Permissions’
  • As I am planning to upload documents from ‘Case’ entity, I am selecting ‘Customer Service – Cases where contact is customer‘ entity permission.

SP_7

  • Add a new ‘Child Entity Permission’ on ‘Sharepointdocumentlocation’ entity and grant below privileges.

SP_9

  • Save and Close.

How to upload the documents from Portal:

Once you made all the configurations, we are all set to upload the documents.

  • Connect to your portal
  • Create a new Case or open an existing Case

SP_12

  • Navigate to the ‘Document Location’ sub-grid and click ‘Add Files’

SP_13

  • Upload the document
  • You should see the uploaded document in the sub-grid.

🙂

 

 

 

 

Dynamics Portals – Restricting portal access by IP address

October 26, 2018 Leave a comment

The Dynamics 365 Portal is public when provisioned and accessible by anyone from any computer. Now we can restrict access to the portal from a list of IP addresses using ‘Set up IP address restriction’ page from your Portal Administration screen.

How it works:

  • To allow access to a portal from an IP address or a set of IP addresses, you can add the ‘Public’ IP addresses to the list. This allows the portal to be accessed only from the list of added IP addresses. If no IP address added, the portal will be accessible from all IP addresses.
  • When a request to the portal is generated from any user, their IP address is evaluated against the allow list. If the IP address is not on the list, the portal displays a web page with an HTTP 403 status code.
  • Only Public IP addresses are allowed and  Private IP address can’t be accessed by the portal.
  • If you try to access the portal from any other IP addresses, access will be denied and a web page with an HTTP 403 status code is displayed.

Steps to Add/Remove IP Addresses:

  • Login to your Dynamics 365 Administration Center
    • You must be having a Office 365 Global Administrator/ Dynamics 365 Service Administrator
  • Select your Instance and go to ‘Applications’ tab.
  • Select the ‘Portal Add-on’ from the ‘Applications’ list and click ‘Manage’

Portal_1

  • From the Portal Administration screen, select ‘Set up IP address restriction’

Portal_2

  • Click on + to add new IP address

Portal_3

  • To remove the IP address, use X from the grid.

Portal_4

🙂

Dynamics 365 – New Admin Center (Preview)

October 22, 2018 Leave a comment

Have you noticed the Try the new Admin center link when you connected to your Dynamics 365 Admin Center?

Admin Center_2

When you click on the link, you will be redirected to ‘Admin Center (Preview)’ built on Power platform.

Admin Center_1

Click on the Instance name (i.e.,MS) in above screen, will take you Instance Details page.

Admin Center_5

Click on ‘See all‘ to get the ‘Details’ pane.

Admin Center_6

Click on ‘Edit‘ to get ‘Settings’ edit pane where you can change the Instance Type (i.e., Production/Sandbox)

Admin Center_7

I started exploring and one standout feature so far is ‘Settings’ page where you can manage all your Admin related configurations (i.e., Short cuts) in one page.

Admin Center_3

To go to ‘Settings’ page, you first need to select the ‘Instance’ and click on ‘Settings’ button.

Admin Center_4

🙂

SQL Server- Troubleshoot ‘String or binary data would be truncated’ error

October 20, 2018 Leave a comment

In one of my Data Migration requirement, I had to join data from multiple tables and  move to a single table. Requirement is complex as data is around 20 GB and table has around 300 columns.

So, I built a Stored Procedure to join tables and move data to single table, the query ran for an hour and finally failed with below error:

String or binary data would be truncated.

Reason:

  • Length of the data I was trying to insert to is more than the defined column size in target table.

Fix:

  • Identifying the problematic column was difficult as my source tables had 300 columns.
  • To trouble the issue, I ran a query on my source tables to get max data length of all columns, which helped me to identify the mismatched column.
  • Below query helped me to identify the maximum data length of source table columns

DECLARE @SQLStatement VARCHAR(MAX);
DECLARE @TableName sysname;
–Set your table name here
SET @TableName = ‘Product‘;
SET @SQLStatement = ”;

SELECT @SQLStatement = @SQLStatement + ‘SELECT ‘ + QUOTENAME(cols.name, ””) + ‘ AS ”Column Name”, ‘ + QUOTENAME(types.name, ””) + ‘ AS ”Data Type”, ‘ +
QUOTENAME(cols.max_length, ””) + ‘ AS ”Defined Length”, MAX(DATALENGTH(‘ + QUOTENAME(cols.name) + ‘)) AS ”Max Data Length” FROM ‘+@TableName+ char(10) +’ UNION ‘
FROM
SYS.COLUMNS cols
JOIN
SYS.TYPES types
ON
types.system_type_id = cols.system_type_id and types.name != ‘sysname’
WHERE
cols.OBJECT_ID = OBJECT_ID(@TableName);

SET @SQLStatement = LEFT(@SQLStatement, LEN(@SQLStatement)-6)
— Execute SQL statement
EXEC(@SQLStatement)

  • To simplify the understanding I created a ‘Product’ table with 4 columns and added few rows

Product Table_1

  • Execute the query and it lists out ‘Column Name’,’Data Type’, ‘Defined Column Length’ and ‘Max Data Length’.
    • In the below result, my ‘Description’ column’s max data length is 142

Product Table_2

🙂

 

SQL Server- Drop If Exists (DIE) Objects

September 28, 2018 Leave a comment

Other day, I generated my Database SQL Script including script to create Tables, Procedures, Types, Views etc., using Tasks -> Generate Scripts… option which I had to deploy on my test instance.

DIE_1

‘Generate Scripts’ generate the SQL script with ‘CREATE’ statement (i.e., It will have ‘CREATE Table’, ‘CREATE PROCEDURE’ etc…)

Issue is, what if I already had Database script ran previously and want to only update the Database objects, you would get script error if you run the generated SQL Script.

Solution is, we need to check existence of the object (i.e.,Table, Procedure, View etc.) and Drop if exists the object and run the CREATE statement generated using ‘Generate Scripts…’ option.

Drop If Exists (DIE) statement in SQL Server 2016:

From SQL Server 2016 CTP3 we can use DIE  statements to check if the Object exists and Drop before run the CREATE statements.

  • Dropping ‘Procedure’ if exists:

DROP PROCEDURE IF EXISTS [Stored_Procedure_Name]

  • Dropping ‘Table’ if exists:

DROP TABLE IF EXISTS [Table_Name]

  • Dropping ‘Type’ (i.e.,User Defined Table Type/User Defined Data Types) if exists:

DIE_2

DROP TYPE IF EXISTS [Type_Name]

  • Delete Table’s COLUMN if Exists.

ALTER TABLE DROP COLUMN IF EXISTS

  • Delete Table’s CONSTRAINT if exists

ALTER TABLE DROP CONSTRAINT IF EXISTS

Below are the list of Objects that can be included in DIE statements.

DIE_3

Note:

  • If your SQL version is before 2016 below is the syntax

IF OBJECT_ID(‘Table_Name‘, ‘U’) IS NOT NULL
DROP TABLE Table_Name;

  • You can also use ‘Check for object existence‘ and ‘Script DROP and CREATE‘ options while ‘Generating Script’ which delete and recreate objects.

DIE_4.PNG

🙂

Categories: SQL Tags: ,

Web API Helper Code Compilation Error

September 18, 2018 Leave a comment

I was creating a console application to connect to Dynamics 365 Web API, and downloaded “Microsoft.CrmSdk.WebApi.Samples.HelperCode” NuGet package.

Compilation Error_1

I got “AcquireToken method is no longer available” compilation error, when I build the project

Compilation Error

Reason & Fix:

  • We have to use UserPasswordCredential class in ADAL v3.
  • Below is the code snippet

var credentials = new UserPasswordCredential(userName, password);
var context = new AuthenticationContext(authorityUri);
authResult = context.AcquireTokenAsync(serviceUrl, applicationId, credentials).Result;

Refer my previous article for step by step to connect to Dynamics 365 Web API.

🙂