How can your company move faster and eliminate manual integration tasks today without custom programming? What tools are out there for you to connect your internal applications even to external systems if required? There are many options out there beyond these, but if you are a Microsoft shop Power Automate is an option or if you are using G Suite there is the Google App Engine.
What are some examples of processes you could automate:
- Do you have a few customers with invoicing requirements you just can’t meet without manually creating them in a spreadsheet? Instead use automation to create Excel/Google Sheets invoices and email them for you.
- You have a new employee home address or customer work location and want to validate the address before processing their first payroll.
- A customer had a requirement to store all their invoices as PDFs in Sharepoint/OneDrive. There are many ways those PDFs can be used and they then can be hyperlinked for anyone with access.
- You have one or more systems that generate invoices but you have a separate ERP system where you receive and apply payments. Automate the creation of the emails with the invoices attached.
Low-code/no-code automation tools can help automate manual tasks or high-volume tasks that could be too time consuming to do manually. Good automation tools will communicate with all your systems getting the information they need.
Here are some of the basic building blocks:
- You decide when to trigger an event:
- Manually
- On a schedule – Every Thursday at 5 or hourly every weekday.
- It may be triggered by another event/notification like an email being received at a specific address or a batch of transactions being processed.
- The automation tool can then read/write data from different systems and create/consume documents. Examples might be:
- Get a list of invoices with a balance from your ERP system
- Create a spreadsheet with selected data and save or email it
- Read a spreadsheet and iterate through all of the lines in a table
- Call out to an external service such as address verification
- Send an email and log the activity of the sent email to your ATS (Applicant Tracking System), CRM, or ERP system
- There are some tricks you can use to get you there faster and more securely:
- Does your requirement depend on a complex starting spreadsheet or document? You can save your template right in the process and create a new, uniquely named version of it on the fly.
- You can save steps work with Authentication by using something like an Azure Key Vault. As an example with Power Automate rather than getting a new Bullhorn REST token you can retrieve one from a secure Azure Key Vault in a single step.
- In many systems you can control what data is logged in the job/process execution history so you aren’t exposing passwords, tokens, etc. you may have extracted from a key vault.
- A company is invoicing out of Bullhorn One
- Acumatica is their main accounting/ERP system where they apply customer payments.
This blog will outline how this can be done with some technical detail on how it could be done.
Step 1 – Authentication and Returning a List of All Invoices With a Balance
The system you connect to is going to require some type of authentication. Cloud based systems will generally have a REST API to access. If you are using something like Dynamics GP it is more likely that some sort of gateway would be required to access your GP data.
- For Acumatica authentication Najaxa has created a blog post here.
- For Bullhorn REST API authentication documentation is here. Note that it isn’t best practice to repeatedly get a new token every time your process runs. There are many ways to solve this. Najaxa has a process to store an active REST Token in a secure Azure Key Vault to be shared by multiple processes. If you have an Azure subscription the Azure Key Vault can be set up in a few minutes and as you get started you can populate it manually to test it.
In the screenshot of a Power Automate Flow below:
- BhRestToken is returned in the Power Automate Flow from the Azure Key Vault. The BhRestToken is then an available variable that can be passed in the Header of subsequent queries.
- In the Query the RestURL variable points to your specific Bullhorn One Rest Endpoint and “~ERPInvoice” is a Mapped Entity in Bullhorn joining together multiple entities. This query could be used if the customer payment data from the ERP system was being used to keep invoice balances updated in Bullhorn.
- The Parse step converts the JSON returned from Bullhorn into re-usable Data Pills/Variables that you can use as needed.
- The Apply to Each step in this case would step through each invoice returned in the query.
NOTE: A Mapped Entity in Bullhorn is to REST what a View is to SQL. It joins multiple tables together into a single view making the data much easier to query. Mapped Entities are a great feature of Bullhorn One, however Bullhorn or your Bullhorn Systems Integrator may need to enable them in your environment. If you don’t see Mapped Entities in your Admin menu in Bullhorn either they are not enabled, or your user doesn’t have access rights to Mapped Entities.
NOTE 2: A Mapped Entity query allows the use of the * All Fields parameter which is otherwise not permitted. This allows you to request all of the fields in the Mapped Entity.
NOTE 3: The sample query above requested the first 500 invoices with a balance to keep the example simple. For more than 500 invoices you could use a totalOnly or showTotalMatched parameter on an invoice REST query then use a start parameter to get the next set from invoice 501 loop through to get all of the invoices with a balance.
If the invoice balances are stored in your ERP system, the process could query that for invoices with a balance. Here is a Postman example using a REST query with a $filter parameter to return Acumatica Invoices with a balance greater than $0.
[[YourAcumaticaURL]/entity/Default/22.200.001/Invoice/?$filter=Balance gt 0.0M
Note that Acumatica will by default return all fields. To return only specific fields in an Acumatica REST query, which is better practice, add a $select and the required fields like this:
{{url}}/entity/Default/22.200.001/Invoice/?$select=Customer,ReferenceNbr,Date,DueDate,Amount,Balance&$filter=Balance gt 0.0M
This is only returning fields from the top level entity. Here is another Acumatica REST API example to illustrate how you can select specific fields from multiple layers and child entities in Acumatica US Payroll:
[URL]entity/Default/22.200.001/PayrollBatch/?$filter=BatchID%20eq%20'000014'&$select=BatchID ,PayPeriod,PeriodEnd,EarningDetails/Amount,EarningDetails/Branch,EarningDetails/PlacementID,EarningDetails/Rate,EarningDetails/Employee,EarningDetails/Date,EarningDetails/Branch,EarningDetails/Code&$expand=EarningDetails
The Acumatica REST call above is selecting data from a Payroll Batch and returning specific fields from the detailed payroll lines. The same could be done with the Invoices request if you needed Invoice Line Item Detail.
Step 2 – The step to get invoices with a balance returned JSON which will need to be converted into usable data pills in Power Automate with a Parse JSON step. The easiest way to do this is to run the query in Postman and to then use the Generate From Sample button to paste that result into the step to automatically generate your parsing mapping:
Step 3: Send Files as attachments
At this point the invoices will need to be retrieved and attached to an email. This will focus on getting files stored in Office 365 – SharePoint in this case. Instead of repeating the same content here is a Microsoft learning article with detailed instructions on sending SharePoint files as attachments..
Step 4: Track the email sent to your customer
Depending on the systems you are using you could now have a step to add a comment/note/activity to your ATS or ERP system tracking the email activity as well as what emails were sent to the customer.
The Bullhorn public REST documentation for the Note entity is here.
In Power Automate you can use Pills (variables referenced from earlier steps in the process) to pass in the Invoice Numbers and Amounts and format the email text and tracked note to look something like this:
The Power Automate step would look something like this however you would use the pills in the “comments” area to reference specific invoices, etc.:
Here is some sample REST API syntax you can use with Postman or Flow to create a Note in Bullhorn. Note you will have to replace the unique ID with a number in two places in the Body. Those long strings should be replaced with just numbers (no quotes or brackets). Note this is a PUT statement.
PUT: URL/entity/Note
{
"comments": "Email sent with automation to AP@sample.net 4/1/2023 at 9:07 AM by Flow Automation.<br><br>You currently have three overdue invoices.<br><br>Invoice Amount Due Date<br>INV0012388 $5,340.00 3/16/2023<br>INV0012388 $5,897.00 2/17/2023<br>INV0012388 $5,340.00 3/9/2023<br><br>Thank you if you have already made your payment.<br><br>To ensure continued service please call us at 888-867-5309 or email invoices@example.net to arrange payment.",
"multipleNotes": false,
"personReference": {
"id": replacethiswithjusttheidnumberoftheclientcontact,
"_subtype": "ClientContact",
"firstName": "James",
"lastName": "Lifeson"
},
"action": [
"Email"
],
"nextAction": "None",
"minutesSpent": 0,
"clientContacts": {
"replaceAll": [
replacethiswithjusttheidnumberoftheclientcontact
]
}
}
We hope this helped generate some ideas and provided some examples of how you can build your own flows.
