Process Overview
Section titled “Process Overview”1. Create a new ticket in HaloPSA. It doesn’t really matter the type, but it would make sense for it to be a Service Request ticket.
Section titled “1. Create a new ticket in HaloPSA. It doesn’t really matter the type, but it would make sense for it to be a Service Request ticket.”2. Upload the current invoice to the ticket and make sure that it is named “invoice” and is of the file type .csv. Upload the template and be sure that it is named “template” and also is a .csv file. You can simply select the Attachments tab on the ticket and drag the files onto there. In regards to CSV files, see notes at the bottom of this WI.
Section titled “2. Upload the current invoice to the ticket and make sure that it is named “invoice” and is of the file type .csv. Upload the template and be sure that it is named “template” and also is a .csv file. You can simply select the Attachments tab on the ticket and drag the files onto there. In regards to CSV files, see notes at the bottom of this WI.”3. Go to the Rewst form for this automation at: https://app.rewst.io/organizations/01983d3f-ecb7-7b80-9d05-726dbe1a90d0/form/019a03e0-2b9e-7333-900d-ba8aa3a44f8e
Section titled “3. Go to the Rewst form for this automation at: https://app.rewst.io/organizations/01983d3f-ecb7-7b80-9d05-726dbe1a90d0/form/019a03e0-2b9e-7333-900d-ba8aa3a44f8e”4. Form Walkthrough
Section titled “4. Form Walkthrough”Halo Ticket: Select your ticket in halo. Easiest way is to copy paste the ticket #.
Section titled “Halo Ticket: Select your ticket in halo. Easiest way is to copy paste the ticket #.”Vendor Name: Defaults to ‘Pax8’. Change if necessary.
Section titled “Vendor Name: Defaults to ‘Pax8’. Change if necessary.”Invoice Number: The invoice number from the PDF document. Manual input.
Section titled “Invoice Number: The invoice number from the PDF document. Manual input.”Invoice Date: Date field, coming from PDF document. Manual input.
Section titled “Invoice Date: Date field, coming from PDF document. Manual input.”Date Date: Date field, coming from PDF document. Manual input.
Section titled “Date Date: Date field, coming from PDF document. Manual input.”Account ID: Defaults to ‘Acct #886294’. Adjust if necessary.
Section titled “Account ID: Defaults to ‘Acct #886294’. Adjust if necessary.”Client Line Item Exclusions: This is the list of client names that will be excluded from the output. You can click into this field and add more. If you have any additional entries that will always be excluded then let me know so that you don’t have to enter it every time. Be careful to enter these values very precisely in order for them to be successfully excluded.
Section titled “Client Line Item Exclusions: This is the list of client names that will be excluded from the output. You can click into this field and add more. If you have any additional entries that will always be excluded then let me know so that you don’t have to enter it every time. Be careful to enter these values very precisely in order for them to be successfully excluded.”5. Give the automation about 10-20 seconds and refresh your HaloPSA ticket. You should see a new document called ‘full_output’ that is a list of all line items. You will also have individual 100 line csv files that are this document broken down into smaller chunks. These will be called “Invoice#{number}.csv” and be sequentially numbered.
Section titled “5. Give the automation about 10-20 seconds and refresh your HaloPSA ticket. You should see a new document called ‘full_output’ that is a list of all line items. You will also have individual 100 line csv files that are this document broken down into smaller chunks. These will be called “Invoice#{number}.csv” and be sequentially numbered.”Aftermath and things to check:
Section titled “Aftermath and things to check:”Any line items that are unmatched will have these values under columns G,H and K. This just means that the description descriptions couldn’t be matched to anything in the template. These are the few (hopefully) that will need manual intervention. I understand from the screenshot above that CentrexIT should be excluded and it will be once this workflow is live.
Section titled “Any line items that are unmatched will have these values under columns G,H and K. This just means that the description descriptions couldn’t be matched to anything in the template. These are the few (hopefully) that will need manual intervention. I understand from the screenshot above that CentrexIT should be excluded and it will be once this workflow is live.”Notes:
Section titled “Notes:”- CSV files: As Rewst does not have a native upload feature, the initial data is coming from attachments in HaloPSA. Rewst doesn’t handle .xlsx excel file data well at all, so the initial input docs (invoice and template) will need to be .csv files. Happy to go over what the means, if unclear at all. If either of the uploaded docs come from Pax8 as an excel file, then here is a simple instruction set to convert to CSV.
Section titled “- CSV files: As Rewst does not have a native upload feature, the initial data is coming from attachments in HaloPSA. Rewst doesn’t handle .xlsx excel file data well at all, so the initial input docs (invoice and template) will need to be .csv files. Happy to go over what the means, if unclear at all. If either of the uploaded docs come from Pax8 as an excel file, then here is a simple instruction set to convert to CSV.”The output file(s) will also be csv. If for whatever reason Bill.com needs them to be in excel format, you can just create a new excel doc and import the csv file into it in one shot.
Section titled “The output file(s) will also be csv. If for whatever reason Bill.com needs them to be in excel format, you can just create a new excel doc and import the csv file into it in one shot.”- Commas: The bane of any CSV files existence is stray commas. Thankfully due to the client renaming fixes, these won’t trip up this process. The one place where I had to replace commas with a blank space is in descriptions. So things like ‘5,000’ will appear as ‘5000’. If that is an issue then let me know and I can try to find another workaround.
Section titled “- Commas: The bane of any CSV files existence is stray commas. Thankfully due to the client renaming fixes, these won’t trip up this process. The one place where I had to replace commas with a blank space is in descriptions. So things like ‘5,000’ will appear as ‘5000’. If that is an issue then let me know and I can try to find another workaround.”- Description Matching: Much of the hassle of getting this to work correctly came from trying to match on descriptions (invoice to template). While the human eye can logically place ‘?Avanan Advanced Protect - Arrears Charge - trishulatherapeutics - 33’ in the same category as ‘Avanan Advanced Protect - Arrears Charge - johnsonandjennings - 38 [options: ]’…even just grabbing the first 10 characters for comparison, code will see that question mark character and determine it to not match. The ‘{Month} partial:’ prefix was an issue as well. To KISS I used fuzzy matching on the first 12 characters after removing all special characters and converting to lowercase. When looking at the descriptions with these adjustments from both docs, a high success rate was achieved. Fuzzy matching on these string values is far from ideal, but there doesn’t appear to be any way around it
Section titled “- Description Matching: Much of the hassle of getting this to work correctly came from trying to match on descriptions (invoice to template). While the human eye can logically place ‘?Avanan Advanced Protect - Arrears Charge - trishulatherapeutics - 33’ in the same category as ‘Avanan Advanced Protect - Arrears Charge - johnsonandjennings - 38 [options: ]’…even just grabbing the first 10 characters for comparison, code will see that question mark character and determine it to not match. The ‘{Month} partial:’ prefix was an issue as well. To KISS I used fuzzy matching on the first 12 characters after removing all special characters and converting to lowercase. When looking at the descriptions with these adjustments from both docs, a high success rate was achieved. Fuzzy matching on these string values is far from ideal, but there doesn’t appear to be any way around it”Import note for your mental health:
Section titled “Import note for your mental health:”The way that Rewst works when kicking off automation from forms means that it won’t give you any feedback in the form window if something under the hood if something fails. It is a one-way trigger. If after 30 seconds nothing appears on the ticket, then reach out to me. Once in a while (about 1 of 20 times) I have seen the automation take an exceptional amount of time to complete due to network or other issues (about 3-5 minutes).
Section titled “The way that Rewst works when kicking off automation from forms means that it won’t give you any feedback in the form window if something under the hood if something fails. It is a one-way trigger. If after 30 seconds nothing appears on the ticket, then reach out to me. Once in a while (about 1 of 20 times) I have seen the automation take an exceptional amount of time to complete due to network or other issues (about 3-5 minutes).”You (whomsoever submits the form) will get an email readout of the unmatched and excluded items.
Section titled “You (whomsoever submits the form) will get an email readout of the unmatched and excluded items.”The exclusions accuracy can be checked by the left-most column where total should be 0 or, if there is a value, the customer name being part of the exclusions list. This should, at the very least, let you scan the exclusions and have a value for the needed matches that you can ctrl-f. You can also just search for NEED_MATCH_BLIDN. The way that the data transformation
Section titled “The exclusions accuracy can be checked by the left-most column where total should be 0 or, if there is a value, the customer name being part of the exclusions list. This should, at the very least, let you scan the exclusions and have a value for the needed matches that you can ctrl-f. You can also just search for NEED_MATCH_BLIDN. The way that the data transformation”Template Information
The template, which will be a living document is stored here.
Click the ellipsis to the right of the doc and select Download. Whenever you encounter a line item that needs to be accounted for going forward (these may very well be part of the unmatched items included in the report), then add the info for columns G,H,I and K to this doc while in sharepoint. The automation will be looking at that description (I) and mapping G,H and K to whatever you put.