Tech Blog

Migrating invoices from Millennium to Alma

Intro

It is possible to migrate Millennium payment data without III’s help and without significant technical skills. However, you are accepting certain losses of data and some clean up work. What you get is the ability to search Alma by invoice number and find past paid invoices. You also get payment history on your serial records. The more years you migrate the more work it is. We migrated six years—45,894 invoices, 326,408 invoice lines. What doesn’t migrate is taxes, shipping, and service charges unless they are paid on their own PO. These can’t cleanly be extracted from Millennium if you’ve distributed them across invoice lines. Whether you migrate your invoices or not, you won’t have this data except in your “post out” files.

Assumption—all payments in the payment file are in the same currency.

To migrate past invoices you need “invoice lines”, “invoice headers”, and ledgers for each year. Completed fiscal year’s invoice data can be worked on and cleaned-up months before it is needed. You can work on one fiscal year at a time over a few weeks or do one year a day. 

  1. Data gathering

Invoice lines come from Millennium pay lines. Use Create List

  1. Search for orders paid date between start and end of fiscal year (best to just do one year at a time, but it depends on your size.)
  2. Export the PO#, fund, Paid line limited to the same fiscal year.
  3. The output can be converted into clean pay lines with a special script that was created by UNC Chapel Hill, https://github.com/UNC-Libraries/III-ILS-Helpers). This script allows you to have one line per payment, per year.
  4. When you import this file into Excel, set the “Invoice Num” field to text type from “general.” Also, cut and insert a row with an invoice number that contains text at the top. This preserves all the invoice numbers when imported into MS Access. Otherwise, Access will set the field type to number and you will lose your invoice numbers that contain letters.
  5. Add a column VCODE (vendor code), leave the values blank
  6. To begin with, keep your fiscal years in separate Excel files. Merge them later.

Invoice headers

Create a list of invoices paid during each of the same fiscal years as your order data.

  1. Export record number, invoice number, paid date, invoice totals, and invoice vendor. Do NOT export the line data. (It doesn’t work.) 
  2. Within the “inv vendor” field is the vendor code, the voucher number, and the invoice total.
    1. “n11253514″,”620203″,”07-02-2020″,”darul 0 411430 78000
    2. Import the file into Excel 

Ledger and Funds

  1. From Millennium, you can copy and paste into Excel the current and previous fiscal year ledger from the basic list of all funds
  2. On the “current funds” section of the Millennium ledger, click into a cell, ctrl-a, ctrl-c. 
  3. Paste into Excel
  4. Add a header field.
  • The queries are expecting the following names:
    • Table: Ledger_millennium
    • Fund
    • Appropriation
    • Expenditure
    • Encumbrance
    • Free balance
    • Cash
    • FY (format must match the format from the invoice lines file–FYyyyy-yyyy)
  1. You can do this for the current fiscal year and the prior fiscal year
  2. If you have been copying and saving this data in prior years, you can use those spreadsheets as the basis of your past fiscal year ledgers
  3. If not, you can use a dummy allocation for prior years.
  4. In this file you should also create your summary and ledger code and names.

NOTE: Prior fiscal years must have the year in the fundcode. Example: histm for FY2019 should be histm-2019. Fund codes for current fiscal year should NOT have the year in it. (This is a migration requirement.) Best practice is to retain the original fund code AND create a concatenated column. 

You can only migrate three levels of hierarchy: Ledger > Summary > Allocated fund. After migration, you can create a new Ledger, and add an additional summary level, or more and then move all the summary funds into the new levels. We have a four level hierarchy now. Ledger > Summary (Division) > Summary Subject (includes endowments and general funds) > Allocated

  1. Data Clean-up

Invoice Lines

  1. Split funds. If you use split funds (multiple funds for an order) you will need to clean them up. Either default to one fund or manually create the specific charge per fund. Once you’ve run the UNC script, you can open the file in Excel and get your split funded POs/payments sorted or filtered together. Manually, insert lines for each fund, and then calculate the correct share. (Copy other elements down, ctrl-D.) I’ve done this at UC Berkeley. We love split funding, it’s a bit tedious, but even if you had 2,000 orders like this, it’s not a huge amount of work for clean data. [Link to video split_fund_fix] This process may change as I work with Ex Libris. It is good, but not the most optimal approach.

Invoice Header

  1. Within the “inv vendor” field is the vendor code, the voucher number, and the invoice total.
    1. “n11253514″,”620203″,”07-02-2020″,”darul 0 411430 78000
  2. If an invoice was paid to the wrong vendor and later fixed, “inv vendor” field would look like:
    1. china 0 414410 24968;”tuxin 1 414411 1897″
  3. Look at the PO to decide which vendor and voucher to use. [Link to Video Voucher_Vendor_Cleanup]  
  4. This cleanup process either involves changing the voucher number on the invoice line sheet, or inserting a new row in the “header” file, as done with the split funds. The video shows how to do both.
  5. Once complete, use text to columns to break out the vendor from the voucher number
  6. You will only need the vendor code and voucher number
  7. Column names/headings in this file:
    1. RECORD #(INVOICE)
    2. INV NUMBER
    3. PAID DATE
    4. INV TOTALS
    5. VCODE
    6. VOUCHERNUM

Invoice Line Numbers

Once any fixes have been made to your invoice lines from the header data, create invoice line numbers in the invoice lines 

  1. sort by voucher id, smallest to largest
  2. Insert a new column
  3. use an if/then formula =IF(A3=A2,B2+1,1) 
    1. Column A refers to your voucher number column and Column B refers to your invoice line number column. Change the letter as appropriate for your data. Enter a 1 in the first row. The formula goes in the second row of invoice line data. 
  4. Fill down
  5. Copy the column and paste values  [Link to video creating invoice line numbers]
  6. Column names/headings in the invoice line file:
    1. RECORD #(ORDER)
    2. FY
    3. FUND
    4. Paid Date
    5. Invoice Date
    6. Invoice Num
    7. Amount Paid
    8. Voucher Num
    9. INVLINENUM
    10. Copies
    11. Sub From
    12. Sub To
    13. Note
    14. VCODE

 

  1. Manipulation with Microsoft Access

The Basics

  1. There’s an Access database you can download and use. (Contact me for the database, until I figure out where to host it.)
  2. Your tables and your column names need to match
    1. either change your column names in Excel to match
    2. OR change the MS Access field names to match your Excel column names
    3. Table names:
      1. Invoice_head
      2. Paylines
      3. Ledger_millennium
      4. fundadj (made from a query of paylines data)
  3. Import your data 
    1. For each import, allow Access to assign a primary key
    2. Name each import fo the corresponding table name listed above
    3. Allow Access to overwrite the existing data
  4. Check if any “ImportErrors” matter and need to be fixed.
    1. invoice numbers were blanked out because Access thought they were numbers and wiped out all the invoice numbers that had a letter in them
    2. Dates were lost (note that Alma doesn’t handle dates older than 01/01/1950 correctly, not that your invoice data will be that old, but your order create dates could be)
    3. Data is lost from other columns
  5. Run the query “update_vendor” to insert the vendor code from the “invoice_head” table into the paylines.

Invoice Header

  1. Run the query called “Header”, it prompts for INV_ENTRY_STATUS “CLOSED”; Currency, use the three digit code for your currency; Invoice Status “PAID”; Pro_Rata “N”
  2. Copy and paste the results into Excel.
  3. NOTE: Invoice numbers need to be unique, so the database concatenates the invoice number with the voucher number, eg. Y321002_459699. You can search in Alma using the asterisk wildcard–Y321002*. 

Invoice Lines

  1. Run the query called “InvoiceLines”, it prompts for currency and for start and end line ID
  2. Copy and paste into Excel.
    1. if you have more than about 66,000 lines you need to run a query filtered by the ID number from 1 to 60,000, and then 60,001 to 120,000, etc. The query will prompt for the ID.
  3. You may need to update your fiscal year from the FYyyyy-yyyy format to the format in your ledger. You can do that with find/replace in Excel.
  4. The query formats all fundcodes with the fiscal year “-YYYY” concatenated to the end. 
  5. In Excel, find/replace the “-YYYY” for the current fiscal year. Only prior fiscal years require a year in the fund code.

Ledger

You want to be sure that all the funds used in your invoice lines exist in your ledger.

  1. Import your ledger with the fields as specified above
  2. Run the query “Funds_Exist_QuestionMark”
  3. You’ll need to add the funds that are missing, if any. You can type them into the Access table
  4. You will need to either export this full ledger for migration or also fix missing funds in your Ledger in Excel.

Expense adjustments

Millennium allows expenses to be moved from fund to fund. If you do this, these expenses are not in your invoice lines. You can create an invoice with these lines. 

  1. The query currently prompts for the fiscal year from the invoice line data. (enter the fiscal year using the format that matches the data, eg: if the data is FYyyyy-yyyy, use that)
  2. The query prompts for voucher num. You make up a voucher number that is not otherwise in your invoice line data. (Find the lowest voucher number in your data, and start with a number significantly lower, to be obvious)
  3. It prompts for an invoice line number, enter 1, and later fix with fill series down in Excel
  4. It prompts for a “paid date.” I use a date at or near the fiscal year end of the year in question–06/28/2021.
  5. Do not enter a record #, it’s just a place holder
  6. Copies: enter 1
  7. Currency enter your currency code
  8. Enter the fiscal year that you are appending to your fund code (for past fiscal years, only)
  9. Enter the fiscal year as formatted in your payment line data, most likely FYyyyy-yyyy 
    1. this value is a condition for the query
    2. Only run one fiscal year at a time
  10. Copy and paste into your Excel invoice lines data
  11. Fill series down to set the proper invoice line numbers (I couldn’t make a query do this)
  12. Add a single line to the invoice header
    1. This can be done manually
    2. Use the voucher num from the lines in the voucher column
    3. Make up an invoice number and invoice date: fundadjFY21
    4. Use the paid date from the lines
    5. Enter a vendor code (we migrated a “none” vendor, so we used that code)
    6. Enter the total amount of the invoice lines
    7. “Fill down” the other fields
    8. Repeat for each fiscal year

 

Important

If you are changing your fund codes at migration, all of this will be more complicated. You will need to update all the funds in your payment data file. The easiest way would be before you export the data to Excel and append the fiscal year to the fund code. Build a spreadsheet that maps old fund to new fund. Import into MS Access, link this table to your payment line table joining on fund code, and update the fund code in the payment line table with the new fund code.

Conclusion

There are a lot of steps and I’m happy to work with anyone to make it clearer or answer questions, including handling more complicated situations. 

Leave a Reply