Using PowerAutomate to Convert Contacts in Axcess

Below is a description of the steps required and materials needed to create this automation using PowerAutomate:

• Materials
  • Download PowerAutomate onto a firm computer
  • Ensure computer has MS Excel installed
  • Ensure computer has Axcess installed with access to Client Manager
  • **If firm is contracting Aiwyn to complete this process, the following additional IT items are needed:
    • Access to remote desktop in firm's network
    • SSO login credentials for access to remote desktop
    • Firm Axcess license number
• Setup Process
  
  • Tip for Success - This process is conceptually easy, but because it is new it can seem challenging. The provided video guide, the written instructions below, and the "Tips for Success" will make this process as easy as possible.
  • Open PowerAutomate and Create a New Flow
  • 
    • 1) This is the variables section. This is where you will find a variable and drag into into a flow
    • 2) This is the flow bar where all the flows are stored. When creating this automation, the Main flow is always present. This is the base into which all the other flows that you build are stored. 
    • 3) This is the Variables side bar. As you add variables screen elements to the automation, they will be stored and listed here. Tip for Success - here on this screen you can only see variables, but I mentioned "screen elements" above. These screen elements ("UI elements") do not show by default but can be summoned by navigating to the top option bar, clicking "View," and then clicking "UI Elements."
•  Building the CSV Feeder Files
  • Standard
    • Create CSV record for client featuring ClientIDSubID, COUNT of billing contacts
    • Sort file by COUNT of billing contacts and separate the CSV into sections
      • Ex: 1400 clients have 1 billing contact (file 1), 245 client have 2 billing contacts (file 2)
      • Tip for Success: If there are any counts, like 3 billing contacts, where there are only a few clients in that category, it is not worth setting up automation for that case, so it is best to complete the task by manually updating
    • Code to generate large file which can be separated by count of billing contacts

select c.ClientIdSubId, count(*) as Count
from [CCH_ENT].[dbo].CLIENT c
inner join [CCH_ENT].[dbo].CLIENTBILLINGEMAIL cbe on cbe.ClientIdent = c.ClientIdent
inner join [CCH_ENT].[dbo].CLIENTEMAIL ce on ce.EmailIdent = cbe.ClientEmailIdent
where ClientStatus = 'Active' and (cbe.InvoiceFlag = 'T' or cbe.StatementFlag = 'T') and
NAME.ClientSortName is null
GROUP BY  c.ClientIdSubId

• • Custom
    • Description: While most firms will need only a "Standard" file generation, if client IDs repeat for the firm for some reason, then a combination of client ID's (c.ClientIdSubId) and Client Names (ClientSortName) will be needed. Below is the complex code needed to generate these lists:

-- DATAPREP
-- User ClientSortName for automation main (1)
-- * Sort into 2 lists by SubId = 15 and not (2)
-- * Sort into 1 email and 2 email (4)
-- Remove duplicate ClientSortNames and use ClientIdSubId instead (1)
-- * Sort into 2 lists by SubId = 15 and not (2)
-- * Sort into 1 email and 2 email (4)
--*-- 8 Lists Total

-- Main List, count of clients by email contact
with COUNT as (
select c.ClientSortName, count(*) as Count
from [CCH_ENT].[dbo].CLIENT c
inner join [CCH_ENT].[dbo].CLIENTBILLINGEMAIL cbe on cbe.ClientIdent = c.ClientIdent
inner join [CCH_ENT].[dbo].CLIENTEMAIL ce on ce.EmailIdent = cbe.ClientEmailIdent
where ClientStatus = 'Active' and (cbe.InvoiceFlag = 'T' or cbe.StatementFlag = 'T')
Group by c.ClientSortName
),


-- MAIN LIST 1, clients by Name
with NAME as
(select C.ClientSortName, count(*) as Count
from Client C
where C.ClientStatus = 'Active' 
Group by C.ClientSortName
HAVING count (*) > 1)

select c.ClientIdSubId, case when ClientSubId = '15' then ClientSubId else null end as SentinelID, count(*) as Count
from [CCH_ENT].[dbo].CLIENT c
inner join [CCH_ENT].[dbo].CLIENTBILLINGEMAIL cbe on cbe.ClientIdent = c.ClientIdent
inner join [CCH_ENT].[dbo].CLIENTEMAIL ce on ce.EmailIdent = cbe.ClientEmailIdent
left join NAME on NAME.ClientSortName = c.ClientSortName
--left join COUNT on COUNT.ClientSortName = c.ClientSortName
where ClientStatus = 'Active' and (cbe.InvoiceFlag = 'T' or cbe.StatementFlag = 'T') and
NAME.ClientSortName is null
GROUP BY c.ClientIdSubId, c.ClientSubId
ORDER BY ClientIdSubId desc


-- MAIN LIST 2, clients by ID
with COUNT as
(select C.ClientSortName, count(*) as Count
from Client C
where C.ClientStatus = 'Active' 
Group by C.ClientSortName
HAVING count (*) > 1)

select CC.ClientIdSubId, case when ClientSubId = '15' then ClientSubId else null end as SentinelID, count(*) as Count
from COUNT C
inner join Client CC on CC.ClientSortName = C.ClientSortName
inner join [CCH_ENT].[dbo].CLIENTBILLINGEMAIL cbe on cbe.ClientIdent = CC.ClientIdent
inner join [CCH_ENT].[dbo].CLIENTEMAIL ce on ce.EmailIdent = cbe.ClientEmailIdent
GROUP BY CC.ClientIdSubId, ClientSubId
ORDER BY CC.ClientIdSubId desc

• Building the Main Flow
  • Read from CSV file
  • Create new data table
  • Set variable
  • Main Loop
• Building the Subflows which feed into the Main Loop
  • Label and Set Variable
  • Open Client Record
  • Open Contact Add Inbound
  • Open Client Record (again- separate flow)
  • Open Email Invoices
  • Add Inbound to Email Invoices
  • Add # Emails to Email Invoices
• Tips for Success
  • Build in "Wait Times" when opening client records and tabs on client records. 20 - 40 seconds per wait will suffice. This is done because access-speed to Axcess changes sprodically. What might take 5 seconds for the first client could take 25 seconds for the next client. The goal is to have this automation run without having to intervene manually.
  • Two "Open Client" flows, why?
    • In order to add a contact to a client, the client record must be saved and closed ("Save and Exit") in order for the contact record to be available to pull into Email Invoices.
  • To account for the varying speeds of Axcess tabs being opened, make sure "On Error" function of each step is edited (click "edit" on any given flow) and add 10 retries with a delay of 30 seconds on each.
  • Robot reading status of checkboxes - a key feature of this automation is having the robot read the status of a given checkbox so that in the moment it can "make a decision" to take or skip an action. This can be tricky because adding an "IF" workflow and "get status of selected checkbox" are out-of-the-box tools in PowerAutomate, you must edit the 
    UI Element to ensure that when you programmed the robot to read the checkbox in the first place, you disable the requirement of the checkbox to me TRUE or FALSE.
    • 
      • Click the vertical triple dots where the circled area shows. This is the UI Element "checkbox" that you selected to read
    • 
      • Click edit and open the options window, shown above
    • 
      • Find the "UI Custom" field and ensure that you uncheck "Enabled---TRUE/FALSE" as that will only allow the checkbox to be read if the status matches the value of the checkbox when you captured it
  • When Recording the screen, the most common practice you will experience when building these flows, you need to remove the specification of the screen you captured. Because the screen you captured belonged to one client, that means when you run the flow it won't work for the next client.
    • Navigate to View > UI elements.
    • When it pops up on the right, scroll to the bottom, find the items you captured, and click "Edit"
    • 
    • 
      • 1) Uncheck the "Process" checkbox, that field is not needed
      • 2) Set the Operator of "Name" to 'Contains' and delete all text except for "Client Profile"
      • This ensures that whenever the automation is pulling up a new client profile it is not looking for a specific client profile, simply any client profile
    • 
      • This is what this page should look like before you click "Save"
• To Do
  
  • IF in setting in Contact, not worth it
  • Write Output data to log file to confirm completion
  • Use wait for image to stop steps from being skipped
  • introduce UI elements
  • Expanding Client Manager and first screen to be maximum, causes OCR and scrolling issues otherwise
  • Process Review
    • Define the Data to be Updated
      • Segment data into files, print results to second file
    • Plan the stages in which to update the data
      • Base this on what we can draft or ask them to draft as source data
    • Define the Process
      • Updating Process/Coordinate with their teams
        • Plan length of update period
        • Plan for billing, need to halt or can work through as a transition?
    • Establish an audit- audit sample sets to ensure machine accuracy
    • Alert or correction when step is missed?
    • Email/Text alerts when batches are errored or completed
    • Side list of client numbers when something unexpected happens, automation can move forward
    • Detecting extra rows (Client Profiles and Blanks) by searching for an excluding "Client:
      " or blank
      
      

• • • • •