IDI’s Insert and Message Import tool lets you manage invoice billing messages and inserts, self-sufficiently via IDI’s Secure File Transfer Protocol (SFTP) site.
Implementation Notes:
Always bracket your text messages in double quotes (“2,100,10/1/2022,12/1/2022”). This lets you to freely add commas in your invoice messages, and keep your message rows in order, within a comma separated values (CSV) file. Avoid using special characters (e.g. # & * ! ^ > < + – = / | \) within your messages, as they can make you PDF Invoice Messages are out of order.
Steps to follow if you have uploaded a badly formatted message file:
DELETE out all bad message rows, by uploading an EMPTY file, with the identical filename to the SFTP site.
REGENEATE the message file, without Viewing or Editing it in Microsoft Excel. You can use a text editor to open and verify these .csv message files are correctly formatted.
UPLOAD the newly generated file to the SFTP site.
If you have multiple 255-character messages and want them to display in a specific order, load the correctly formatted messages in the order you want them to appear on the PDF Invoice.
IMPORTANT:
The Insert and Message Import filenames MUST be less than or equal to 50 characters in length (including spaces and .csv extension), as the [CustInvoiceMsg] field, within the [dbo].[BatchIdentifier] table that stores these filenames, has a Data Type of Varchar(50).
If you want to add a blank line between your invoice messages, simply add a space (” “) within a message column, then add it to the IDI system, in the order you want the blank line to appear within messages section.
When URL address and\or email address are passed within the message column, they become active links when using the Customizable PDF Invoice Generation Solution.
If using a print vendor, insert IDs mustbe sent to IDI’s Billing Clerk Team (BCT) when approving Vendor Print and Mail on E-Support as part of a bill run. When approving the Vendor Print and Mail task for billing, mark your Insert ID information in the approval screen; otherwise, the print vendor will not receive the information they require.
All types of invoice messages should be tested; otherwise, there is a risk the invoice messages will not be formatted correctly. To ensure you get the desired results, test your invoice messages within your P_TEST environment first.
Run the IDI Invoice and Message Search to find invoice messages and inserts for a bill period, and the reports as instructed later in this article to ensure you do not include unintended messages/inserts.
To run the Invoice and Message Search, from the Applications menu in the IDI Desktop Client select Applications > BackOffice Management > Insert and Message Search. This opens the Insert and Message Search dialog.
The search results are described below:
Acct Number | Subscriber’s Account Number in the IDI Platform. |
Name | Name of the customer associated with the account. |
Message | Complete text of the invoice message. |
Insert | Number of the insert included with the invoice. |
Start | Beginning date the insert or message is to be included. |
End | Ending date the insert or message is to be included. |
Market | Name of the customer’s Market. |
City | Primary contact’s city. |
State | Primary contact’s state. |
Zip | Primary contact’s Zip Code. |
Establish | Date the account was established. |
Acct Type | Kind of account, such as Business or Residential. |
Acct Status | Current state of the account. |
Invoice Format | Name of the customer’s invoice format. |
Ensure the invoice message end dates that you have chosen, do not fall into future bill periods; otherwise, the messages/inserts could be included in the next bill run.
It is recommended that you add your bill messages or inserts at least 15 days before billing, to allow time for test billing and PDF generation requests to be completed and re-run, if necessary.
If you need assistance, please contact IDI at https://support.idibilling.com/.
Overview
There are currently two ways to add Invoice Messages to a PDF invoice self-sufficiently within the CostGuard system:
At the Market level. This method does not involve the Invoice Insert and Messaging tool. Rather, you can simply modify the Footer and Remit Supplemental Invoice Messages configured for the market via the IDI Admin Console under Data Management > Market Configuration. This method works well when the messagecontent does not change frequently, for example, for seasonal invoice messages.
At the customer account level. A flexible targeted invoice messaging solution. You can do this using the Invoice Insert and Messaging tool. You can update message/insert content this way before the next bill run as needed. This is the preferred method when message content changes frequently.
NOTE:
You can also add invoice messages to an account manually in IDI Desktop Client by selecting View > Invoice Messages on the account. This displays the Invoice Message window.
The invoice message list indicates the message and the start/end dates. These dates establish the time range for which the message will be available in the list. The right-click menu lets you add a new message and edit or delete existing messages.
Tips:
Always first upload your updated CSV Invoice Message files to IDI’s P_TEST SFTP site, to test and verify, then upload your tested files to IDI’s P_PRODUCTION SFTP site before billing runs.
When adding customer level messages/inserts using the insert/message tool, keep in mind what may exist at the market level.
It is recommended that you create a list of invoice messages/inserts assigned at the market level. This can help you avoid accidentally flagging unintended accounts for inserts or messages. To keep track of the invoice format level settings, you can use the following:
In Admin Console > Data Management, the Market Configuration table shows the populated supplemental invoice messages per market when you have Columns > All enabled (via context menu when you right click on Showing n rows). The remit and footer supplemental invoice message columns are located at the end of the table.
The Customer Invoice Messages report displays a list of all customer accounts that have custom invoice messages assigned. This report resides in the CostGuard Client > Report Explorer > Audit > Customers & Services folder.
Note:
Market level and customer level messages/inserts are not necessarily interchangeable. Test with your PDF Generation solution BEFORE trying to assign a message/insert at the customer level when it was originally designed for adding at the invoice level, or vice versa. Here’s how the two messages differ when coded in the raw xml that is sent to print:
Invoice Inserts and Invoice Messages Import Tool
The Insert and Message Import Tool allows for invoice messages and invoice inserts to be updated using a Microsoft Office Excel Comma Separated Value (CSV) file.
Notes:
You CAN undo any previous changes that you have made with the insert/message tool. When you upload a file with the same name as a previously uploaded file, the new file first removes all data uploaded within the previous file from the IDI system, then writes in the new data.
CSV invoice message files can be created, transferred, and then automatically processed within the IDI system, after being tested.
The insert/message tool can be effectively used by querying all or specific customers where you wish to add inserts/messages.
If you do not have access to write a SQL query, you can export an IDI report to get your customer account number list.
If you review your invoice messages in the IDI Desktop Client and see unwanted punctuation marks, try reloading your file as a CSV UTF-8. This format sometimes works better with SQL Server.
CAUTION – the insert/message tool works on Billing Inactive as well as Billing Active customers.
ALWAYS test with your PDF Generation Solution, if you have multiple invoice messages on a PDF.
Exporting in SQL Server:
Once you have your CSV export, you will need to format it correctly. Here are the technical details on the correct CSV data formatting:
Column Letter | Field | Data Type |
|---|---|---|
A | Record Type (1 = Insert, 2 = Message) | Integer |
B | CustomerAccountNumber | Text |
C | StartDate (Can be blank) | Date |
D | EndDate (Can be blank) | Date |
E | For Inserts – Insert Flag # (1, 2, or 3 – whatever row the code is entered on in the Vendor Print and Mail Task on E Support) | Integer (must be 1, 2 or 3) |
E | For Messages – Print vendor Message ID or Message Text (max length 255 characters) Suround your invoice message with double quotes to allow commas within your message. | Integer/Text (max length 255 characters) |
If you start out with your data in a .txt file, here is an example of the correct data format, comma separated:
1,CustomerAccountNumber,StartDate,EndDate,3
2,CustomerAccountNumber,StartDate,EndDate,Message
Lastly, here is an example of correct formatting within a CSV:
Data must be added to columns A through E only, using the above format
By adding hundreds of rows to a CSV file, the insert/message tool lets you update hundreds of accounts at once. You can also undo any changes you have made.
CAUTION – When you upload a file with the same name as a previously uploaded file, the new file first removes all data uploaded within the previous file from the IDI system, then writes in the new data. Therefore, be very careful when naming your files. Accidentally uploading a new file with the same name as a previous file will remove all data from the previous file (if the same data is not included in the new file). This process is illustrated in the Timing section of this article.
Once you have formatted your CSV file, simply drop the fle into the In folder within the Insert and Message Import SFTP directory. If you do not have access to this directory, open a Service Request with IDI BCT:
In folder is destination where you upload the CSV file
Done folder shows past uploads
Errors folder shows failed uploads
Logs folder lists logs that contain errors and details on your import/deletion
Timing for Insert/Message Updates
Insert/Message updates must be made before XML Invoices are generated within the IDI platform.
File Naming
File naming is extremely important.
When importing new invoice messages/inserts – Choose a completely unique file name to import. Choosing a file name that has already been used will undo any changes you have made.
When removing invoice messages/inserts from past imports: To remove changes completely from a previous import, upload a completely blank file (no insert/message data) with exactly the same file naming convention as the previous import.
Example: File name IDI_04_24_2017 is imported and updates invoice messages for 1,000 accounts. If you reload a blank CSV with file name IDI_04_24_2017, this will remove the previous file updates.
Removing some, but not all invoice messages/inserts use the same process as the example above for removing updates, except the second file you import named IDI_04_24_2017 contains only the invoice messages/inserts you would like to keep. Any messages/inserts that are not included in the new file that were in the original file will be removed.
Also, any time you re-load a file with the same naming convention and the same insert/message data, the insert/message data will remain, but the log in the Logs folder of the Insert and Message Import Directory will indicate an insert/message was deleted. The insert/message tool is deleting and re-writing in this instance.
File Naming Suggestions:
Adding a monthly file with a naming convention of the bill cycle period code is highly recommended. This requires you to add inserts/messages by billing cycle. If the next month’s messages/inserts are the same, you can simply copy the old file, rename it to the newest bill cycle period code, and import it again.
Example: 31230101.csv (if your 1st billing cycle falls on January 1st 2023)
Two other options are:
Naming your file with the active date ranges of the messages/inserts you are adding.
Naming your file per the category associated with the customer account numbers being added (If you are adding for a billing group or market).
More Naming examples:
Messages01_01_2017–01_01_2018.csv
BillingGroupA_01_01_2017.csv
MarketB_01_01_2017.csv
Note:
Any combination of the above examples may best suit what your business needs. Use these examples as a guideline.
How Errors Work
Note:
Always review the logs. When there is an error in the logs, you cannot add data, but you can still delete it
The insert/message tool works in the following way:
Errors are only detected from the Add data from current file stage to ensure the deletion will happen if the file is a recognized format and name.
Note:
An error is thrown in the logs is not the same as a failure, which happens when the file cannot even be read (An example of a failure would be if you import a .txt or .rtf (WordPad)).
These errors occur when you delete values out of the cells in a CSV, but do not delete the entire rows:
Error in file YourCSVname.csv on line X: Unknown record type
Error in file YourCSVname.csv on line X: Unable to find Customer with Customer Account Number.
Error in file YourCSVname.csv on line X: Invalid line contained X number of fields. Valid lines contain 5 fields.
Example:
Cells vs. Rows
The cells in the CSV must have never contained any data for the message/insert removal to not throw an error in the logs. The file still removes the inserts/messages, it just lists an error.
These errors occur when 1 of the 3 mandatory fields are left blank (2 date fields are optional), or more than 5 fields are filled in for a row:
Error in file YourCSVname.csv on line X: Invalid line contained X number of fields. Valid lines contain 5 fields.
Error in file YourCSVname.csv on line 1: Unable to find Customer with Customer Account Number.
This error happens when the cell for customer account number is left blank.
Error in file YourCSVname.csv on line 3: Unable to find Customer with Customer Account Number 111000123.
The specified customer account number does not exist in the database
Note:
If you add text to a column that should contain date/time or integer data types, an error will be thrown.
Historical Data Best Practices
It is recommended that you do not remove data uploaded by this tool unless it is truly inaccurate.
The IDI system keeps a log of past entries with date ranges for Invoice Messages/Inserts. This can be viewed by selecting the View drop-down menu on the customer account. The Backoffice Management > Insert and Message Search is another useful tool for viewing past entries on the bill period level.
Controlling whether messages/inserts appear on invoices by selecting appropriate date ranges is the recommended best practice. Deleting messages/inserts to remove them from future invoices is not advisable.
Date Ranges on Invoice Messages/Inserts
The start date and end date you choose will determine whether invoice messages/inserts will appear on the customer’s bill. If the start/end date touches a date during an active bill cycle, messages/inserts will be available for the active cycle.
There is no limit to the number of billing cycles where messages/inserts can be active. Messages/inserts will remain active during the timeframe selected. Evaluating start and end dates closely is an important step.
I many cases, it may not be possible to foresee when you would like to end date messages/inserts. Reloading files of the same name with different end date values is an option for adjusting the availability of inserts/messages after the fact. Adding a file with a new name and a new date range, but with the same customer account numbers and messages/inserts, is an effective way to extend a date range while keeping historical data intact.
Notes:
Consider Historical Data Best Practices when choosing date ranges.
Work with your PDF Generation solution to ensure it is prepared to look for insert/message information. Otherwise, the PDF Generation solution will ignore any additions made by this tool. IDI recommends that you create and send a sample file to test whether the insert/message is being included by the PDF Generation solution.
Viewing Results on the Customer Account Level
Inserts and invoice messages can be managed at the customer account level. This is where they can be managed currently:
Viewing Results on the Bill Cycle / Period Level
Insert and Message Search is another useful tool in the IDI system. This search lets you view all invoice inserts and messages for a bill period and search for specific insert numbers or invoice messages within a bill period. This search only returns messages/inserts on the customer level, it DOES NOT show who may receive the message/insert because they’ve been flagged on the market or invoice format level.
Before you can use this feature, ensure the permission flag in Admin Console is enabled (green), not disabled (red), under Security > Profiles > Permission Profiles:
Accessing Insert and Message Search:
Example Search:
