ComputerEase Data Entry Automation with Power Automate

Will Cauthen

In this post, I will be highlighting how I was able to automate data entry into Deltek’s ComputerEase (CE) construction accounting software using Microsoft’s Power Automate. By utilizing the import capabilities of CE for CSV and XML files, I was able to significantly reduce job data entry time by 90% and minimize human error.

Using Power Automate, you can create CE import files for customers, jobs, pull lists, and purchase orders, which allows the streamlining of processes and increased efficiency. The Flow is triggered by the conversion of an estimate to a job in an estimating Power App, and includes the saving of the import files to their corresponding SharePoint folders and the sending of notification emails to relevant parties.

If you are a Power Platform professional or a business owner looking to improve your use of CE, I hope that this post will provide valuable insights and guidance based on my own experience.

Data Design

To preface, most of our data is already captured for this flow in the Estimating Power App. The flow’s purpose is to transform the data and integrate the data into the XML & CSV CE templates. The data in our estimating app is stored in Dataverse with the following structure:

  • OrderID – Information about the estimate/job (estimator, customer name, address, contract amount, material budget, commission, etc.)
  • OrderMeasurement – Child of parent table, OrderID. It is linked to OrderID through the unique job number. This table contains estimate measurements.
  • OrderLine – Child of parent table, OrderMeasurement. It is linked to OrderMeasurement through the unique job number. This table contains itemized materials and labor from our estimating app. Materials and labor are itemized and created based on measurements taken in the estimating process.

Schema Files

First, we need blank xml files so that we can insert variables in our flow. The XML files need to be in a specific format so that attributes are recognized when imported into CE. CE Support can provide XML schema files (XSD format). The easiest way to understand the structure is to import the schema file in Excel. This can be done under the developer tab. Source – > XML Map

Using Notepad++, I was able to create the XML structure by referencing the XML source format from the XSD file.

Building the Flow Base

In Power Automate, create a manual Power Apps trigger. In this use case, we want the CE job files to be created when a user converts an estimate to a job in the Power App. In the app, we are passing the auto-generated job file number to the flow. In the first few steps,

  1. Variable Order ID – Declare the job number from the variable passed from the Power App.
  2. Order Query – Query the table where the order data is stored and filter by order ID.
  3. Single Row Object – Select the first result from the returned array.
  4. Parse JSON – Parse the returned JSON from the query. Data points from this are set in the XML & CE files created in this flow.

When setting variables, some data may need to be transformed. In one example, the CE customer ID has a limit of 8 characters and needs to be unique. The ID is generated as MM-DD-Y + first initial + first two letters of last name.

Building the Basic XML files

Add a Compose data operation to create the first XML Customer file. The blank XML from the first step is used as a template and declared variables are inserted where needed. In the screenshot below, you will see parsed JSON values and the unique customer ID that we generated.

Repeat this to create an XML Job file. It is important to ensure that you have already captured all relevant data in your Power App, such as calculated material budget, commission, labor budget, and contract amount.

Building the Pull List XML with an Array

We need to build a pull list file for materials that we carry in the warehouse. First, we will query the pull list items from the OrderLines table, making sure we have the right ODATA filter. This query will return the materials in JSON format. We need to parse this and then declare a blank array variable. Parsing the Dataverse results allows us to access the attributes in the body of the JSON.

With the Apply to each function, we need to append an XML line for each material in the pull list. Each item will have its own part number, description, and quantity. For each record in the parsed JSON, a new row is added to Array Var.

After the ‘Apply to each’ step, we can embed the array we created in the following XML. The Array Var variable will contain all the materials needed for the pull list.

Build the Purchase Order CSVs

Building Purchase Order CSVs is a bit easier. Using the ‘List Rows’ Dataverse action, we can query the OrderLines table with an ODATA filter specific to the material type (material for Material PO, labor for Labor PO).

Parse the content to JSON. Using the Create CSV Data Operation, map header names as well as variables already set (ex: job number) and returned JSON data (ex: material name, quantity). Note: The header names come from the import tool in CE.

Saving the files

For each XML & CSV file created, we need to save them to SharePoint & send them through email. These can be configured easily with the ‘Create File’ SharePoint action and the ‘Send an email’ action. Variables and Outputs are added where needed. Once the files have been created, a user can import these into CE by clicking File -> Import.

Creating a SharePoint file
Email to Estimator & Sales Manager
Email to Procurement Coordinator / Job Builder

That’s it! If you have any questions, drop a comment or contact me here. Thanks for reading!

Subscribe
Notify of
1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
7 months ago

very cool Will! I am a user of CE for about 7 years now. I love it for financials but think it could do more for general office staff. I am always looking for things like this to enhance the experience for our employees.