Category Archives: Blog
Delete multiple CDS records from Grid using PowerApps.
Introduction: In this blog, we will learn how to Delete multiple records of CDS from the Grid. Use Case: We have a requirement where there is a Grid of CDS Data Source, on clicking the Delete Icon on top of the Grid, it should delete every record which is selected through the checkbox which is there on every record of the Grid. Solution: Steps to be followed: 1. There is a Screen on which there a Editable grid of Quote Product Entity. To Create an Editable Grid refer to the following link. https://www.cloudfronts.com/create-an-editable-grid-view-in-powerapps/ To add Lookup Fields in the Grid refer to the following link. https://www.cloudfronts.com/add-lookup-fields-in-an-editable-grid-using-powerapps/ 2. This is the grid with a checkbox. 3. To delete selected records, first create a Collection: OnSelect property of the Delete icon: Set DeleteIcon.OnSelect = ClearCollect(<VariableName>,Filter(<GalleryName>.AllItems,<CheckBoxName>.Value = true )) For eg: DeleteSelectedRecord.OnSelect= ClearCollect(SelectedQuoteProductDelete,Filter(GalleryQuoteProduct.AllItems,CheckboxQuoteProductGallery.Value = true)) 4. When we select the Delete Icon, it will collect all the records where the Checkbox is selected. 5. To delete the records from the CDS, set the OnSelect property of the Delete Icon to the following formula: OnSelect property of the Delete icon: Set DeleteIcon.OnSelect = ForAll(<CollectionVariable>,RemoveIf(‘Gallery Data Source’,<GUID you want to delete>)) For eg: DeleteSelectedRecord.OnSelect = ForAll(SelectedQuoteProductDelete,RemoveIf( [@’Quote Products’], ‘Quote Product’ in SelectedQuoteProductDelete[@’Quote Product’] )) 6. Combine the Whole formula in the Set OnSelect property of Set DeleteIcon : For eg: DeleteSelectedRecord.OnSelect = ClearCollect(SelectedQuoteProductDelete,Filter(GalleryQuoteProduct.AllItems,CheckboxQuoteProductGallery.Value = true)); ForAll(SelectedQuoteProductDelete,RemoveIf([@’Quote Products’],’Quote Product’ in SelectedQuoteProductDelete[@’Quote Product’]) ) Conclusion: Hope above Blog helps you delete multiple records of CDS from the Grid.
Share Story :
Hide certain Field of the form depending on Security Role of the User using PowerApps.
Introduction: In this blog, we will learn how to hide fields from the form based on the Security Role of the Logged in User. Use Case: We have a requirement where there are some Fields in the form, which should be hidden to the users who does not have the Security Role as Manager. Steps: 1. This is the form of Quote Product. We want to hide the Price Overriden field from the form to certain Users. 2. To hide the field, click on the DataCard. Visible property of the DataCard: Set Price Overridden_DataCard.Visible = If(LookUp([@’Security Roles’],Name = “Manager”,Role) in Concat(LookUp([@Users],’Full Name’ = User().FullName).’Security Roles(systemuserroles_association)’,Role & “;”),true,false) Conclusion: Hope the above Blog helps you to hide fields from the form based on the Security Role of the Logged in User.
Share Story :
Filter a Lookup Field on New Form Using PowerApps.
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.
Share Story :
Purchase order approval through a Mobile device in Microsoft D365 Fianance and Operations
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
Share Story :
How to Upsert Records in SQL(Sink) through ADF?
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.
Share Story :
x++ code to import data from excel to D365 FnO
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(); } }
Share Story :
Customize Purchase order approval status
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
Share Story :
Steps to Configure Environments through Life Cycle Services (LCS)
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.
Share Story :
Nested Filters in PowerApps.
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.
Share Story :
How to increase OneDrive for Business storage up to 5TB
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!