Home
Dynamics 365
Dyn365CE
Dynamics 365: JavaScript Enabled Button For Box.com Using KingswaySoft And Ribbon Workbench – Part 2

Dynamics 365: JavaScript Enabled Button For Box.com Using KingswaySoft And Ribbon Workbench – Part 2

Patrick McClureDyn365CE5 years ago11 Views

Recently I was tasked with devising a way where our end users could click a button located on an entity form in Dynamics 365 that would launch our Enterprise Box.com file sharing site. This button would be dynamic, driven via JavaScript, and would open the related Box.com client folder location that would be dependent upon the entity record the button was initiated from.

In this post I’ll show you the basic outline of our Box.com site along with our intermediate database for parsing and formatting our data into a usable form that can be pushed up to Dynamics 365. This basic design can be made to work with any online storage solution with an open API.

Series Links Parts 1-4:

Box.com Online Storage Solution Design

What I needed was a way to pull my online storage solutions folders from a large directory stored in Box.com. Each of the folders that I needed to retrieve were client folders at a certain level that had a client number attached to the name. So in essence I needed to gather and store all the folders that met the naming requirement and most importantly, were in a particular order beneath the primary A-Z and 123 index directory. That being said I needed to skip what we referred to as parent folders that were right beneath the primary A-Z and 123 index directory and instead just gather what we refer to as child folders that reside right below the parent folders. Below are pictures of this folder structure to help better visualize the design.

Folder Structure

Box-PrimaryDirectory Box-ParentFolders Box-ChildFolders

SQL Staging Database Design

Next I needed a place to stage the 3rd party online storage solution data so I could manipulate it, creating the necessary Absolute Urls that will be stored in Dynamics 365. The main SQL table contains Defaults that help create those values when data is inserted or updated. I also created error and audit tables to track progress and log any errors that might occur. You don’t need these additional tables but depending upon your scenario they could be very helpful when debugging issues or checking volume counts.

SSIS-HelperTables

I have provided the script for created the main staging table below. I use the error and audit tables in conjunction with both pulling Box.com data down and then after staging, deploy the data to Dynamics 365. I imagine that each scenario is different, thus those extra tables most likely will be different than mine. Since that is the case then I’m sure you can create them as needed.

SQL Table (Main):

The staging part comes from special columns in the below table that build the Absolute Url and create other necessary fields needed when storing in the SharePoint Document Location entity. These are accomplished by creating SQL column constrains like the one called SiteCollectionId and SQL computed columns like the AbsoluteUrl column, both shown below.

CREATE TABLE [ssis].[BoxDriveOnlineItems] (
  [ClientNumber] NVARCHAR(32) NOT NULL
 ,[BoxId] NVARCHAR(32) NOT NULL
 ,[Name] NVARCHAR(128) NULL
 ,[Description] NVARCHAR(1024) NULL
 ,[item_status] NVARCHAR(32) NULL
 ,[parent.id] NVARCHAR(32) NULL
 ,[parent.name] NVARCHAR(128) NULL
 ,[parent.type] NVARCHAR(32) NULL
 ,[Tags] NVARCHAR(4000) NULL
 ,[Type] NVARCHAR(32) NULL
 ,[PrimaryFolder] NVARCHAR(64) NULL
 ,[SiteCollectionId] UNIQUEIDENTIFIER NOT NULL -- BOX Registered SharePoint Site Collection
    CONSTRAINT [DF_BoxOnline_SSIS_SiteCollectionId]
      DEFAULT '{BOX_SITE_COLLECTION_ID}'
 ,[RegardingObjectTypeCode] INT NOT NULL -- Account=1
    CONSTRAINT [DF_BoxOnline_SSIS_RegardingObjectTypeCode]
      DEFAULT 1
 ,[ServiceType] INT NOT NULL
    CONSTRAINT [DF_BoxOnline_SSIS_ServiceType]
      DEFAULT 0
 ,[RelativeUrl] AS ([BoxId])
 ,[AbsoluteUrl] AS ('https://[SOME_NAME].[BOXSTUFF].box.com/folder/'+[BoxId])
 ,[SSIS_CreatedOn] DATETIME NOT NULL
    CONSTRAINT [DF_BoxOnline_SSIS_CreatedOn_CURRENT_TIMESTAMP]
      DEFAULT CURRENT_TIMESTAMP
 ,[SSIS_ModifiedOn] DATETIME NOT NULL
    CONSTRAINT [DF_BoxOnline_SSIS_ModifiedOn_CURRENT_TIMESTAMP]
      DEFAULT CURRENT_TIMESTAMP
    CONSTRAINT [PK_BoxOnline_CNumber]
    PRIMARY KEY NONCLUSTERED([ClientNumber] ASC)
    WITH(PAD_INDEX=OFF, STATISTICS_NORECOMPUTE=OFF, IGNORE_DUP_KEY=OFF, ALLOW_ROW_LOCKS= ON, ALLOW_PAGE_LOCKS=ON, FILLFACTOR=90)ON [PRIMARY]
)ON [PRIMARY];

CREATE CLUSTERED INDEX [CIX_Id]
  ON [ssis].[BoxDriveOnlineItems](
  [parent.name] ASC
 ,[Name] ASC
 ,[ClientNumber] ASC
)
  WITH(PAD_INDEX=OFF, STATISTICS_NORECOMPUTE=OFF, SORT_IN_TEMPDB=OFF, IGNORE_DUP_KEY=OFF, DROP_EXISTING=OFF, ONLINE=OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON)
  ON [PRIMARY];
GO

Staging: I am not really going to go into much detail concerning how I designed my Visual Studio SSIS Project with KingswaySoft since it’s a little outside the scope of what I’m trying to accomplish. Albeit using KingswaySoft did make this process go by so much easier than coding up something in say C# that would call the Box API and dump returned data into a staging database. Keeping that in mind, the point of this section is to quickly walk you through an example of how this might look like.

KingswaySoft-Design-00

First, you have to configure a connection to the Box API using the Box Connection Manager. The Box API is horrendously slow and their customer support sucks when debugging timeout issues. However my ridiculous story concerning timeout issues experienced via the Box API is for another time. The point here that I wanted to make was that in my time of need, KingswaySoft’s technical support team stepped up and created additional functionality to help me work through the slowness experienced when interacting with the Box API. Keeping that in mind, if dealing with the Box API, make sure to set the API Throttle Rate at 2 to minimize timeout errors return from the API.

KingswaySoft-Design-00-A

Next, we need to configure the KingswaySoft Box Source Editor and pull our cloud data down where we’ll dump the output into our staging database. Below is an example of what the KingswaySoft Box Source Editor looks like for one of my directories that I plan to retrieve. Make sure to review the settings below because when dealing with timeouts it’s best to keep the Batch Size to a minimum. Thanks to the KingswaySoft team for adding in the Max Depth feature for me as that allowed me to select only a subset of folders from the Box API reducing processing times dramatically.

KingswaySoft-Design-01

From there I have placed several SSIS objects with the sole purpose of isolating folders and filtering down folders that meet my naming requirements. I also incorporate the Script Component objects written in C# to further filter out any unwanted folders. Lastly, I use the KingswaySoft Premium ADO Net Destination object where I write the final output of folders returned to my staging database.

KingswaySoft-Design-05

There are a number of fields available when referencing Box.com API so whatever your scenario is, make sure to only select the fields you absolutely need in order to expedite the process. Your processing times will decrease dramatically when interacting with a smaller field set.

KingswaySoft-Design-06

Deploying: This final piece is a separate SSIS project that takes what has been pulled from the source, now in our staging database, and compares to the primary entity (in my case the account entity) for validation. I only want to create urls for accounts that actually exist in Dynamics 365. I use KingswaySoft’s nifty little Premium Lookup Component Editor that expands functionality over Microsoft’s out-of-the-box Lookup SSIS object. It makes the process of pulling source data for analyses against a lookup table ridiculously easy.

KingswaySoft-Design-D365-00

Matched Rows:

Source items matched against the target entity follow the Create and Update paths.
Matched items (Absolute Url and Site Collection Id) against the SharePoint Document Location entity route to the Update path.
Unmatched items (Absolute Url and Site Collection Id) against the SharePoint Document Location entity route to the Create path.

Unmatched Rows:

Source items unmatched against the target entity follow the Delete path.
Matched items against the SharePoint Document Location entity route to the Delete path for purging.

This works well for my scenario and I’ve definitely shown a very high level design overview. There are a lot of things going on here that need to happen in order to determine if an Absolute Url with correct Site Collection Id needs to either be created, updated, or deleted. So take that into account when designing your own unique process. All I can do is advise trying out KingswaySoft’s Ultimate Toolkit, as it’ll make this process much easier to design and manage.

Final Thoughts

In my next post I’ll walk you through how to create the logic behind your new Ribbon Workbench button coded in JavaScript. Once that has been completed I’ll show you where that file needs to be uploaded in Dynamics 365.

Series Links Parts 1-4:

Original Post https://therapyincode.wordpress.com/2020/01/15/dynamics-365-javascript-enabled-button-for-box-com-using-kingswaysoft-and-ribbon-workbench-part-2/

Upvote0PointsDownvote

0 Votes: 0 Upvotes, 0 Downvotes (0 Points)