Wednesday, July 8, 2020

Integrating OneDrive with Business central

Since One Drive/ Sharepoint integration is the need of the hour for Dynamics 365 Business Central, I am going to write on how a file can be uploaded into One Drive from Dynamics 365 Business central. 

It's been a while since Business central started supporting Rest APIs. These days, Power-Automate and Azure logic apps are making it easier to integrate with Business central. Business central has a lot of potential to integrate with office 365 products. I've read the blogs discussing how it can be achieved using azure functions or power automate and I wanted to do this the pure AL way!

Since there are lots of other blogs where App Registration in Azure AD, API Permissions are shared in a detailed manner, I'm going to skip to the part where we get to the see how this integration is achieved.


For Starters, we are going to need a table to capture the One Drive credentials and Drive Folder Name. For this specific exercise, am capturing the Client ID, Client Secret, Directory ID, Drive ID (One Drive), Folder Name in the table OneDrive Setup.


Then we need the option to select file to upload. since File management is not accessible in BC Cloud, we'll use the UploadIntoStream. Using this, we can get the filename using a different function. We'll also be needing the length of the file in bytes. For this, I'm using Tempblob.Length function. we'll be needing the File Length when we upload the file. we'll get to that part soon.

1
2
3
4
5
6
7
8
9
        OnedriveSetup.Get();
        TempBlob.CreateInStream(InStr);
        UploadIntoStream('Import', '', ' All Files (*.*)|*.*', FilePath, InStr);
        FileName := GetFileType(FilePath);
        IStream := InStr;        
        LengthOfFile := TempBlob.Length();
        TempBlob.CreateInStream(InStr);
        IStream := InStr;
        FileName := GetFileType(FilePath);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
procedure GetFileType(pFilename: Text): Text;
    begin
        if StrPos(pFilename, '\') = 0 then
            exit(pFilename)
        else begin
            FilenamePos := StrLen(pFilename);
            while (pFilename[FilenamePos] <> '\') or (FilenamePos < 1) do
                FilenamePos -= 1;

            if FilenamePos = 0 then
                exit('');

            exit(CopyStr(pFilename, FilenamePos + 1, StrLen(pFilename)));
        end;
    end;

Next, we create a function to retrieve the bearer token which will enable us to upload the file. In this exercise, am using the Content type x-www-form-urlencoded. we use POST method to get the Bearer Token. Then, we parse the HTTP response content to a text variable, which is then read into a JSON Object. from there, getting the access token is pretty straight forward. am adding the function right below for those who need to try it.


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
procedure GetBearerToken(): Text
    var
        lClient: HttpClient;
        lResponse: HttpResponseMessage;
        lContent: HttpContent;
        lHeaders: HttpHeaders;
        lUrl: Text;
        lJsonObj: JsonObject;
        lJsonToken: JsonToken;
        Token: text;
        lClientID: text[250];
        lSecret: text[250];
        BaseTxt: Text[1024];
        APITokenLocal: Text;
    begin

        lUrl := 'https://login.microsoftonline.com/' + OnedriveSetup."Directory ID" + '/oauth2/v2.0/token';
        lClientID := Onedrivesetup."Client ID";
        lSecret := OnedriveSetup."Client Secret";
        BaseTxt := 'grant_type=client_credentials&client_id=' + lClientID + '&client_secret=' + lSecret + '&scope=https://graph.microsoft.com/.default';
        lContent.Clear();
        lContent.WriteFrom(BaseTxt);
        lHeaders.Clear();
        lContent.GetHeaders(lHeaders);
        lHeaders.Remove('Content-Type');
        lHeaders.Add('Content-Type', 'application/x-www-form-urlencoded');
        lContent.GetHeaders(lHeaders);
        if lClient.Post(lUrl, lContent, lResponse) then begin
            lResponse.Content().ReadAs(Token);
            lJsonObj.ReadFrom(Token);
            lJsonObj.Get('access_token', lJsonToken);
            lJsonToken.WriteTo(APITokenLocal);
            APITokenLocal := DelChr(APITokenLocal, '=', '"');
            Exit(APITokenLocal);
        end
        else
            error('API Token Request failed');
    end;

From this point on, things get interesting. The next step towards uploading files from business central would be to create an upload URL. for this, we create another function. This function will use a Post method to create an upload URL. I know that the syntax from Microsoft documentation of this URL requires a folder ID from one drive, but I've simply passed the folder Name instead. trust me, this works. if the Folder Name does not exist, then it will create one for you! Retrieving the Upload URL is done in the similar way we did with the bearer token. The function is as below:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Procedure CreateUploadURL(FileName: Text; BearerToken: Text): Text
begin
lUrl := 'https://graph.microsoft.com/v1.0/drives/' + OnedriveSetup."Drive ID" + '/root:/' + FolderName + '/' + FileName + ':/createUploadSession';
        Bearer := 'Bearer ' + BearerToken;
        lHeaders.Clear();
        lContent.GetHeaders(lHeaders);
        lHeaders.Remove('Content-Type');
        lHeaders.Add('Content-Type', 'application/json');
        lreqHeaders := lClient.DefaultRequestHeaders();
        lreqHeaders.Add('Authorization', Bearer);
        lreqHeaders.Remove('Accept');
        lreqHeaders.Add('Accept', 'application/json');
        lRequest.GetHeaders(lReqHeaders);
        lContent.GetHeaders(lHeaders);
        lRequest.Method := 'POST';
        lRequest.SetRequestUri(lUrl);
        lRequest.GetHeaders(lReqHeaders);
        Clear(BaseTxt);
        if lClient.Send(lRequest, lResponse) then begin
            lResponse.Content().ReadAs(BaseTxt);
            lJsonObj.ReadFrom(BaseTxt);
            if lResponse.IsSuccessStatusCode() then begin
                lJsonObj.Get('uploadUrl', lJsonToken);
                lJsonToken.WriteTo(WebUrl);
                WebUrl := DelChr(WebUrl, '=', '"');
                exit(WebUrl);
            end else
                Error(Text50001Lbl);
        end;
    end;

Now, we get to upload the file using the upload URL we just created. This is the part where the length of the uploaded file which we stored in a variable in the beginning comes to play. We need the length of the file in bytes to be passed in the header Content-Length. for this exercise, am uploading the file in one stretch instead of breaking the file down into multiple parts. Thus, I have passed the Full Length of the file in Content-Range. I've used Content-Type "octet-Stream" so that it will support all file types. Usually, the file content is converted into Base64 string and then uploaded, But I have passed the stream directly into the content. this worked out pretty well. The upload function is as below:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
procedure PutFileUsingUploadSession(UploadUrl: Text; BearerToken: Text)
begin
        lUrl := UploadUrl;
        Bearer := 'Bearer ' + BearerToken;
        lHeaders.Clear();
        lContent.GetHeaders(lHeaders);
        lHeaders.Remove('Content-Type');
        lHeaders.Add('Content-Type', 'application/octet-stream');
        lHeaders.Add('Content-Length', format(LengthOfFile));
        lHeaders.Add('Content-Range', 'bytes 0-' + format(LengthofFile - 1) + '/' + format(LengthOfFile));
        lreqHeaders := lClient.DefaultRequestHeaders();
        lreqHeaders.Add('Authorization', Bearer);
        lRequest.GetHeaders(lReqHeaders);
        lContent.WriteFrom(InStr);
        lContent.GetHeaders(lHeaders);
        lRequest.Method := 'PUT';
        lRequest.SetRequestUri(lUrl);
        lRequest.GetHeaders(lReqHeaders);
        lRequest.Content(lContent);
        Clear(BaseTxt);
        if lClient.Send(lRequest, lResponse) then begin
            lResponse.Content().ReadAs(BaseTxt);
            lJsonObj.ReadFrom(BaseTxt);
            if lResponse.IsSuccessStatusCode() then begin
                lJsonObj.Get('webUrl', lJsonToken);
                lJsonToken.WriteTo(WebUrl);
                WebUrl := DelChr(WebUrl, '=', '"');
		//At this point, the file is uploaded. The WebUrl is retrieved so that you can use it to store it as per your requirement
            end else
                Error(Text50001Lbl);
        end;
    end;

So far, I have tested this out with various file-types such as PDF, JPEG, PNG, PPT, XLSX, DOCX, MP4, etc. This code works for files within 40 MB. in order to upload large files, we need to break down into multiple recurrences of the last function. But that's a content for another post. Hope this helps anyone looking out to uploading files to one-drive using pure AL code. 

If you find this interesting or if you have any suggestion/feedback, please leave a comment below! Cheers!!

Sathyanarayanan.S

Monday, August 3, 2015

Conflicts of opening Report Builder in NAV 2013 R2 After installing NAV 2015

After having installed NAV 2015, which brings in the new report builder from SQL Server 2014 instead of Version 3 which was part of the NAV 2013 R2 installation, it is no longer possible to open the layout of a report in the NAV 2013 R2 Development Environment.
It throws the following error:

---------------------------
Microsoft Dynamics NAV Development Environment
---------------------------
An error occurred when opening the report designer. A supported version of Report Builder could not be found.
---------------------------
OK  
---------------------------
 
Solution:

Uninstall the SQL 2014 Report Builder and re-install the SQL 2012 Report Builder. It will work with both NAV versions.
 

Wednesday, July 29, 2015

Identify Company based on Colors

This Post will explain how one can set various colors to Company Name in RTC if your Customer has more than one legal entities(Company).

If your customer has more than one legal identity and wants to identify each legal entity based on its color, you need not bang your head over this. Dynamics NAV has an option that allows us to specify  System Indicator Styles for each company. By defining unique System Indicator Style for each company, You can identify it with unique colors.

However, There is a limitation for this. Microsoft Dynamics NAV allows us to define only 9 System Indicator Styles apart from the standard style. In simple words, you can define upto 9 different colors for companies in NAV RTC.

Now, Am going to explain how it is done: 

Go to the Company Information Page from Departments/Administration/Application Setup/General
Then, Go to the System Indicator fast tab of Company Information page. 
By default,The value of System Indicator Style will be populated as Standard.
However, users can choose from a list of options namely Accent1,Accent2,...,Accent9
The System Indicator Style field will determine the color of company name to be displayed.
For example Accent1 is RED, Accent2 is BLUE  and Accent9 is GRAY.

Hope this helps.

Tuesday, July 28, 2015

How to disable personalization of the Roletailored Client in Dynamics NAV

In this Post, I am going to explain how to disable personalization of the roletailored client in Dynamics NAV.
 
Dynamics NAV allows users to personalize the pages.

If you want to disable the personalization of RTC for your customers, you can do it by following the below steps.

Open RTC in configuration mode and go to:

Departments > Administration > Application Setup > RoleTailored Client > Profiles

edit the desired profile and check the Disable Personalization field.


After doing this, the customize part will be disabled and your customers will not be able to make any personalizations in the roletailored client.
  

How to change the Startup splash screen of Dynamics NAV

In this post, am going to explain how we can change the splash screen of NAV during startup.

Basically, Dynamics NAV fetches the splash screen image from a certain directory. So, If you can edit the image or replace it with another image (Of course with same resolution), you can manage to change the startup splash screen of Dynamics NAV.


Below location is where Dynamics NAV accesses the splash screen image from:

C:\Program Files (x86)\Microsoft Dynamics NAV\71\RoleTailored Client\Images

The filename of the Splash screen image is "Splash.PNG"

Now, edit whichever picture you want to put as your NAV Startup Splash Screen and save is as "Splash.PNG".

Replace this image in the above mentioned directory location.

VOILĂ€! your Splash screen is now changed!

Monday, July 27, 2015

How to Display Multiple Lines In a Message box or an Error Window?

This post is for people who have recently joined and are still Learning NAV.

In Programming Languages like C & C++ , the new line Character used is "\n".

However, in Dynamics NAV, the new line Character is "\".

Let's see an example:

Text0001 := 'This is the first line of message. \ This is the second line of message.';

MESSAGE(Text0001);

When i run these lines of code, the resultant output will be  

---------------------------
Microsoft Dynamics NAV
---------------------------

This is the first line of message.
This is the second line of message.
---------------------------
OK  
---------------------------


This can also be done like this:



MESSAGE('This is the first line of message.  \  This is the second line of message.');

The resultant output will still be
---------------------------
Microsoft Dynamics NAV
---------------------------

This is the first line of message.
This is the second line of message.
---------------------------
OK  
---------------------------



Hope you find this useful.
 
 

Mail Merge Report using Word Automation in NAV 2013r2

In this post, I'm going to explain how to create a mail merge report and send it to customer using word automation.

Pre-Requisites:
  • Word Automation should be installed.
  • Word Application, Document and Range variables should be used.
  • Preferably, a separate code unit should be used for word automation.
  • Mail merge template (Preferably in .DOT extension).
  • SMTP mail setup should be used for mailing.
  • Suitable action item from the respective card page should be used to call this code unit from current record.
  • In case of server implementation, use a shared folder to save the mapped word document and attach it in the mail.

steps to Create a mail merge document template:
  • Open word application.
    Go to Insert tab =>quick parts => Merge field => select mail merge.T
  • ype the name of the merge field and press OK.
  • Repeat the steps to create multiple merge fields as per your requirement.
  • Type the letter content as per your requirement.
  • Save the document as <DOCNAME>.DOT
Where to Place Automation Code:

You put all code in a separate code unit that is called from a menu item on the page.

It is recommended that you isolate the code that uses Automation in separate codeunits since Automation server must be installed on the computer, which compiles an object that uses Automation. You need to recompile and modify the entire object if the Automation server is not installed on your computer.It is best that the Automation code be placed in a separate codeunit than be defined as a function in any other codeunit.

Performance can be an issue if extra work is needed to create an Automation server with the CREATE system call. If the Automation server is to be used repeatedly, then you will gain better performance by designing your code so that the server is created only once instead of making multiple CREATE and CLEAR calls.

Variables

Name                  DataType                Subtype                    Length
WdApp                  Automation        'Microsoft Word 14.0 Object Library'.Application   
WdDoc                  Automation         'Microsoft Word 14.0 Object Library'.Document   
WdRange              Automation        'Microsoft Word 14.0 Object Library'.Range   
CompInfo                  Record            Company Information   
TemplateName         Text                                                       250
FileName                  Text                                                       250
SMTPMail              Codeunit             SMTP Mail   
SMTPSetup            Record                SMTP Mail Setup   
Cust                        Record                Customer   
Mail                        Codeunit             Mail   
Sales&RecvSetup   Record                Sales & Receivables Setup     
SalesHeader            Record                Sales Header

Before starting,go to the codeunit's properties and set the codeunit's  table No. to the associated page that you need to send mail from.

 Sample Code :
//Word Automation Begin

 IF CREATE(WdApp,TRUE,TRUE) THEN BEGIN // Initialize word application
 FileName := FORMAT("Sales&RecvSetup"."Server  Destination")+FORMAT(Description)+'TenderDocument.Doc';
  "Template Name" := "Sales&RecvSetup"."Template Location";
  WdDoc := WdApp.Documents.Add("Template Name"); // this enables word application to use the template
  WdApp.ActiveDocument.Fields.Update; // Initialize the document

  WdRange := WdApp.ActiveDocument.Fields.Item(1).Result; // This sets the range to the particular merge field using array Index
  WdRange.Text := "Bill-to Name"; // returns the customer name from the current record.
  WdRange.Bold := 1;

  WdApp.Visible := TRUE;
  WdApp.ActiveDocument.Fields.Unlink; // this will map the table records to the word document
  WdApp.ActiveDocument.SaveAs(FileName);
  WdApp.Quit;
  CLEAR(WdApp);
  //Word Automation End
 
  //SMTP Mail Begin
  SMTPSetup.GET;
  IF Cust.GET("Bill-to Customer No.") THEN BEGIN
    Cust.TESTFIELD(Cust."E-Mail");
    SMTPMail.CreateMessage(COMPANYNAME,CompInfo."E-Mail",Cust."E-Mail",'Sales Invoice','',TRUE);
    SMTPMail.AppendBody('Dear Sir / Madam,');
    SMTPMail.AppendBody('<br><br>');
    SMTPMail.AppendBody('Please Find the attached Sales Invoice.');
    SMTPMail.AppendBody('<br><br>');
    SMTPMail.AppendBody('Thanks for doing business with us.');
    SMTPMail.AppendBody('Regards,');
    SMTPMail.AppendBody('<br><br>');
    SMTPMail.AppendBody(CompInfo.Name);
    SMTPMail.AppendBody('<br><br>');
    SMTPMail.AppendBody('<HR>');
    SMTPMail.AppendBody('******This is a system generated mail. Please do not reply to this email ID.******');
    SLEEP(4000);
    SMTPMail.AddAttachment(FileName);
    SLEEP(4000);
    SMTPMail.Send;
    MESSAGE(Text0001);
    END;
    CLEAR(WdApp);
    END;
 //SMTP Mail End

**Note:
1. Use sleep function before and after calling add attachment function because it takes few seconds for the system to save your document and then attach the same. use CLEAR system calls if you plan to use this function multiple times.
2. Use a shared folder from the server in case if you are planning to implement this over a server. since the add attachment will look for the file from the server, it will throw an error that the file is missing or not available. if you are using this from your local machine's database, then it wouldn't be a problem.


Finishing Touch:
You can attach this code unit to an appropriate card page in a menu item from which the mail needs to be sent. By calling the codeunit, you'll have to pass the current record REC as a parameter.
Note: 

A much simpler way to use MS-Word for reports is available in NAV 2015. This process is the primitive method to connect to word application from NAV using Automation variables.

Please leave your queries in the comment section.