Introduction: In this blog, we will learn how to filter a Lookup Field on the New Form. Use Case: We have a requirement where there is a Lookup Field on the form, which should only show those values whose Status is Active. Steps: 1. This is the form of Quote Product. We want to filter the Existing Product field. 2. To filter the field, click on the Combo Box.         Items property:          Set ExistingProduct.Items =  Filter(<Data Source>,Status = <Field Name>.Active) For eg: ExistingProductQPEditForm.Items = Filter([@Products],Status = ‘Status (Products)’.Active)   Conclusion: Hope the above Blog helps you to filter a Lookup Field on the New Form.

In this blog I am going to explain in brief about  the Purchase order approval mobile workspace. This workspace lets you view purchase orders and respond to them through actions. For example, you can approve or reject a purchase order. After a purchase order (PO) has been created, it might have to go through an approval process. After the vendor has agreed to the order, the PO is set to a status of Confirmed. POs that don’t use change management have a status of Approved as soon as they are created. A PO creates inventory transactions only when it reaches the Approved status. Once you’ve activated the Change management, approval workflow is introduced. As a system administrator, you must publish the Purchase order approval mobile work space To do that Click Settings> Mobile app. Select the mobile workspace to publish. Click Publish Now on your mobile go to Playstore and download Microsoft Dynamics 365 Unified Operations App Once you have downloaded the App and logged in you should see the Purchase order approval workspace on your Mobile App  

Introduction We are performing Integration of Accounts from CRM to SQL using ADF Copy activity pipeline. We want to upsert the accounts instead of inserting duplicate records again. Step 1: Auto create the Table named “accounts” in SQL Server during the first Integration run by selecting the Auto create table option.   Step 2: Create a custom data type named “AccountType” using following query.   CREATE TYPE AccountType AS TABLE( accountid uniqueidentifier, transactioncurrencyid uniqueidentifier, address1_city nvarchar(MAX), createdon datetime2(7), accountnumber nvarchar(MAX), name nvarchar(MAX), address1_country nvarchar(MAX), address1_composite nvarchar(MAX), telephone1 nvarchar(MAX), emailaddress1 nvarchar(MAX), websiteurl nvarchar(MAX), primarycontactid uniqueidentifier ) Step 3: Create a Stored Procedure named “spUpsertAccounts”.   CREATE PROCEDURE spUpsertAccounts @account AccountType READONLY AS BEGIN   MERGE dbo.accounts AS target_sqldb USING @account AS source_tblstg ON (target_sqldb.accountid = source_tblstg.accountid) WHEN MATCHED THEN UPDATE SET accountid = source_tblstg.accountid, transactioncurrencyid = source_tblstg.transactioncurrencyid, address1_city = source_tblstg.address1_city, createdon = source_tblstg.createdon, accountnumber = source_tblstg.accountnumber, name = source_tblstg.name, address1_country = source_tblstg.address1_country, address1_composite = source_tblstg.address1_composite, telephone1 = source_tblstg.telephone1, emailaddress1 = source_tblstg.emailaddress1, websiteurl = source_tblstg.websiteurl, primarycontactid = source_tblstg.primarycontactid     WHEN NOT MATCHED THEN INSERT ( accountid, transactioncurrencyid, address1_city, createdon, accountnumber, name, address1_country, address1_composite, telephone1, emailaddress1, websiteurl, primarycontactid ) VALUES ( source_tblstg.accountid, source_tblstg.transactioncurrencyid, source_tblstg.address1_city, source_tblstg.createdon, source_tblstg.accountnumber, source_tblstg.name, source_tblstg.address1_country, source_tblstg.address1_composite, source_tblstg.telephone1, source_tblstg.emailaddress1, source_tblstg.websiteurl, source_tblstg.primarycontactid ); END Step 4: Enter the Stored Procedure Name, Table Type and Table type parameter as shown in the image below:     Step 5: Publish all the changes and debug your Pipeline.  You can Verify the results in SQL Server “accounts” table.  

We can also import data through code in D365 FO. Data import through code in D365 works differently than Ax2012 since cloud services.  Import Class we are trying to import data from Excel to D365FO through the following code.   using System.IO; using OfficeOpenXml; using OfficeOpenXml.ExcelPackage; using OfficeOpenXml.ExcelRange;   class EmplAttendance { public void run() { this.updateDailyAttendance(); }   void updateDailyAttendance() { System.IO.Stream                     stream; ExcelSpreadsheetName            sheeet; FileUploadBuild                       fileUpload; DialogGroup                             dlgUploadGroup; FileUploadBuild                       fileUploadBuild; FormBuildControl                    formBuildControl; EmplAttendance_CFS              emplTimeAttendance, insertTimeAttendance, updateTimeAttendance; COMVariantType                     type; Dialog                      dialog =    new Dialog(“Daily Attendance Imported”);   dlgUploadGroup          = dialog.addGroup(“@SYS54759″); formBuildControl        = dialog.formBuildDesign().control(dlgUploadGroup.name()); fileUploadBuild           = formBuildControl.addControlEx(classstr(FileUpload), ‘Upload’); fileUploadBuild.style(FileUploadStyle::MinimalWithFilename); fileUploadBuild.fileTypesAccepted(‘.xlsx’);   str COMVariant2Str(COMVariant _cv) { switch (_cv.variantType()) { case COMVariantType::VT_BSTR: return _cv.bStr();   case COMVariantType::VT_EMPTY: return ”;   default: throw error(strfmt(“@SYS26908”, _cv.variantType())); } }   if (dialog.run() && dialog.closedOk()) { FileUpload fileUploadControl     = dialog.formRun().control(dialog.formRun().controlId(‘Upload’)); FileUploadTemporaryStorageResult fileUploadResult = fileUploadControl.getFileUploadResult();   if (fileUploadResult != null && fileUploadResult.getUploadStatus()) { stream = fileUploadResult.openResult();   using (ExcelPackage Package = new ExcelPackage(stream)) { int                         rowCount, i,columncount,j; Package.Load(stream); ExcelWorksheet   worksheet   = package.get_Workbook().get_Worksheets().get_Item(1); OfficeOpenXml.ExcelRange    range       = worksheet.Cells; rowCount           = (worksheet.Dimension.End.Row) – (worksheet.Dimension.Start.Row)  + 1; columncount      = (worksheet.Dimension.End.Column);     for (i = 2; i<= rowCount; i++) { str Emplid; TransDate                 WorkingDate; emplid                       = (range.get_Item(i, 1).value); WorkingDate            = str2Date((range.get_Item(i, 2).value),123);   select * from emplTimeAttendance where emplTimeAttendance.EmplId              = =  Emplid && emplTimeAttendance.WorkingDate       = =  WorkingDate;   if(emplTimeAttendance)     //if record already exists update it { emplTimeAttendance.selectForUpdate(true); emplTimeAttendance.Timein                          =   any2Str(range.get_Item(i, 3).value); emplTimeAttendance.Timeout                        =   any2Str(range.get_Item(i, 4).value); emplTimeAttendance.OT                                =  any2Real(range.get_Item(i, 5).value); ttsbegin; emplTimeAttendance.update(); ttscommit; } Else      //insert the new record { insertTimeAttendance.EmplId                         = (range.get_Item(i, 1).value); insertTimeAttendance.WorkingDate               = str2Date((range.get_Item(i, 2).value),123); insertTimeAttendance.Timein                         = any2Str(range.get_Item(i, 3).value); insertTimeAttendance.Timeout                       = any2Str(range.get_Item(i, 4).value); insertTimeAttendance.OT                                = any2Real(range.get_Item(i, 5).value); insertTimeAttendance.insert(); } } } } else { error(“Error here”); }   } }   public static void main (Args args) { EmplAttendance     emplDailyAttendanceImport;   emplDailyAttendanceImport = new EmplAttendance (); emplDailyAttendanceImport.run(); } }  

In the case of D365 Finance and Operations when you approve purchase requisition by default system creates Purchase order with approval status as “Approved” as follows     To change this default behavior of system such that once purchase requisition is approved the approval status of the purchase order as a draft you can use the following class class CFSPOStatus {     /// <summary>     ///     /// </summary>     /// <param name=”args”></param>     [PostHandlerFor(classStr(PurchAutoCreate_PurchReq), methodStr(PurchAutoCreate_PurchReq, endUpdate))]     public static void PurchAutoCreate_PurchReq_Post_endUpdate(XppPrePostArgs args)     {         //PurchTable  purchTable = args.getThis(‘purchTable’);         PurchAutoCreate_PurchReq purchReq = args.getThis() as PurchAutoCreate_PurchReq;         PurchTable  purchTable, purchTablenew;         purchTable = purchReq.parmPurchTable();         ttsbegin;         select forupdate purchTablenew where purchTableNew.PurchId == purchTable.PurchId;         if(purchTablenew && purchTablenew.DocumentState == VersioningDocumentState::Approved)         {             purchTablenew.DocumentState = VersioningDocumentState::Draft;             purchTablenew.update();         }         ttscommit;     } }and your final result looks like And after changing status you can apply your own purchase order workflow on it. For purchase order workflow you can refer to my blog

Configuration of Environment through LCS. After we purchase licence, Login the LCS through Admin account. You can see the follow link to complete setup environment. Before configuring the environments there are some pre-requisites need to be performed. Declaration of project milestone. Click on setup milestone, Enter the end date for each milestone and save. 2. VSTS Setup. Before this we need to follow the below steps: Login in Azure DevOps. Create a project. 3. Create personal access token. Save this token. Click on “Setup Visual Studio Team Services” a.  Enter the site Enter the AzureDevOps url, which consists of https://organizationname.visualstudio.com/ and click on continue. Enter Personal access token generated above in Azure DevOps. b. Select the project Select the project from the list and click continue. c. Review and Save 3. Project configuration and project on-boarding. Click on “Complete project configuration”(This is one time setup) And click on “Project onboarding” Check all the 12 points by clicking on next and then finish the complete onboarding review page. And click on configure button of environment Enter the name of environment and select the region. Then you can see the status of environment in queued state. After 7-8 hours you can login to your environment.      

Introduction: In this blog, we will learn how to use Nested filters in PowerApps. Use Case: We have a Gallery(GalleryQuoteLineDetail) where we need to put filter which equals Quote Product selected in another Gallery. Once we get those Quote Products, we need to filter the Gallery(GalleryQuoteLineDetail) based on ‘Transaction Type’ Field,  which is a field on Quote Product. The ‘Transaction Type’ should be equal to “Project Contract”. Solution: Steps to be followed: 1. Below is the CDS Data Source, we want to filter. 2. To filter the Gallery Based on another Gallery, Use the below Formula: Items property of Gallery:      Set: GalleryQuoteLineDetail.Items: Filter( ‘Data Source’,Condition)      For eg: GalleryQuoteLineDetail.Items : Filter(‘Quote Line Detail’,’Quote Line’.’Quote Product’ =  GalleryQuoteLine.Selected.’Quote Product’)   3. To filter the Gallery Based on “Transaction Type”, Transaction Type is of Option Set Data Type, Use the below Formula: Items property of Gallery:      Set: GalleryQuoteLineDetail.Items : Filter( ‘Quote Line Detail’,  ‘Transaction Type’ in “Project Contract” ) 4. To Combine both the Filters in a single Formula, use the first filter as ‘Data Source’ of the second filter: Following is the formula:  Filter( Filter (‘Quote Line Detail’, ‘Quote Line’.’Quote Product’ =  GalleryQuoteLine.Selected.’Quote Product’ ), ‘Transaction Type’ in “Project Contract” )   Conclusion: Hope this Blog helps you to combine multiple Filters into single Filter.

You must have known that OneDrive for Business storage has a limitation of 1TB but what if you have utilized the full capacity (1TB). There is no such announcement for the same, but the limit is not 1TB. In this article, we will see how we can increase the storage capacity for OneDrive for Business up to 5TB through PowerShell. As per one of the Microsoft articles “If your organization has a qualifying Office 365 plan and 5 or more users, you can change the storage space to more than 5TB”. So as per this statement, we can get more than 5TB as well. That means the limitation is not 5TB as well. Increasing the storage from 1TB to 5TB can be done with the help of your Microsoft 365 Administrator through PowerShell, but going beyond 5TB, you will need the help of Microsoft Support with justification and then it might get increased up to 25TB quota. Pre-requisites: Download SharePoint Online Management Shell. https://www.microsoft.com/en-us/download/details.aspx?id=35588 Connect to SharePoint Online through PowerShell. $adminUPN=”<the full email address of administrator account>” $orgName=”<name of your Office 365 organization>” $userCredential = Get-Credential -UserName <the full email address of administrator account> This is prompt to enter Message, enter the admin account password and hit enter. Connect-SPOService -Url https://$orgName-admin.sharepoint.com -Credential $userCredential You will be prompted to provide admin credentials. Increasing the storage quota for OneDrive for Business: Check the current set quota for a user. Get-SPOSite -Identity <User’s OneDrive URL> | select $StorageQuota Note: – Storage quota is in MB. Change the storage quota for a specific user. Set-SPOSite -Identity <User’s OneDrive URL> -StorageQuota $StorageSize Note: – Replace $StorageSize with 5242880 (MB) which is 5TB To verify the change, run cmd Get-SPOSite -Identity <User’s OneDrive URL> | select $StorageQuota You can also verify the same from Office 365 Admin Center > Active Users > Select User > OneDrive.  This article will help you to increase storage quota for OneDrive for Business up to 5TB without any need of calling Microsoft Support. If you need to increase it more than 5TB then you will need to take help from Microsoft Support with justification. Thanks!

Problem: A friend of mine was looking to migrate their Business Central development and support from one partner to another. While, asking me for advice, I was evaluating what was the best solution knowing that their Business Central had customization installed and I found this which made it easy for them as their going to continue customizing Business Central from another partner. Solution: In Business Central version 14 Sandbox, you are allowed to Download Source Code. But in Business Central version 14 Production, this was not the case. No Apps. could have been downloaded even though you got it developed from a Partner/ Developer and installed from them.In Business Central version 15 Sandbox, you are allowed to Download Source Code just like you would in Business Central Version 14 Sandbox. Download Source Code for Installed Extensions in Business Central V15 Sandbox And this is also the same condition  for Business Central version 15 Production tenant as well. Download Source Code for Installed Extensions in Business Central V15 Production Without this, the possible solution to get your app source code back from Production, you would be required to make a copy of Production into Sandbox and download it from there in Business Central Version 14. Note: This doesn’t apply to apps installed through MarketPlace.  Trust me I have tried 😜!!! Conclusion: When App was installed in Business Central Version 14 Production ensured Partners / Developers that app couldn’t be reused on other tenants. This put most of the pressure on the Customers to explicitly to ask the Vendor to provide the App File. Whereas when an App is installed on Business Central Version 15 Production, it can be downloaded and installed in another Business Central Tenants. This means that if the Customers want to change their partners, they can seamlessly do this without having the awkward conversation of asking the code from their previous partner.

Problem Statement: In Microsoft Business Central, there is a way to attach attachments only on Documents or Master Table records. But, what if this requirement is for other tables such as Opportunities, custom tables, etc. Introduction: I have seen many developers afraid to touch the attachment-related customization as it seems complicated. Well, I have found a solution and here it goes. In this blog, I’m attempting to create a generic template for code that needs to have an attachment feature on any table that you like using AL Code. This means that you simply cannot copy-paste the same code for all the tables but a simple change in variable sub-type will ease your work significantly Pre-requisites: Microsoft Dynamics Business Central VS Code Al Language Extension Source Code: https://github.com/olisterr/Generic-Attachment-Template Demonstration: 1. How it works: Document Attachment is a table which stores a few things that help in tracking information related to the attachment Document Attachment Table Fields The main unique thing that works for all the different tables is the TableID, Line No and No. fields. Along with this, the document is attached through a stream inside a Media DataType. There are 2 important functions 1. SaveAttachment2 : This function takes a few parameters RecRef for origin Table ID, FileName to store the file name, Blob which will be imported a Stream in Document Reference ID Media type field, Recs.No which will store the unique record for which the attachment is attached. This function when the line has no attachment attached to it. This is done by checking if the field Document Reference ID has no value. SaveAttachment2   2. Export2: This function exports the attached BLOB to a file. This is done if the Document Reference ID field has any value. Export2 This is both functions are complimentary of each other and are trigger on FileName field DrillDown DrillDown to Attach OR Export   2. Things that you need to change: Either you are using the Generic Attachment Template code first time or mulitple times in the same project, you need to find //Change the Table Name Here—-OLISTER and //Change the Page Name Here—-OLISTER comment and replace your table name. Also, you will need to manage with the PageName and IDs. Read through README.TXT in the project. README.TXT   3. Custom Attachments for multiple tables: In the above case, I have created the code for the Opportunity Card page. I will repeat the same for Item Card Page. Prior to Items Card Page Make a copy of all the 3 Pages and change their names and Ids. After adding a new set of pages for different tables Change the Table Nos on the comment line //Change the Table Name Here—-OLISTER and //Change the Page Name Here—-OLISTER After changing Table Names After changing Page Names After changing Part Page Names 4. Output: Finally, I got this. Opportunities Attachment Items Attachment   Conclusion: Thus, we are successful in creating a generic code template for attachments. Hope this helps!