Overview
There are two ways for a law firm to set up budgets in Apperio. Budgets on Apperio apply to the life of the matter. The preferred approach is to include budget data directly in the datafile upload — this keeps budgets dynamic and automatically updated with every upload. A manual platform entry option is also available on request.
- Option 1 (Recommended): Include budgets in the datafile upload
- Option 2: Enter budgets manually on the Apperio platform
This article covers both options, including a step-by-step guide for firms that store budgets outside their Practice Management System (PMS) and need to bring them into the datafile via a User Defined Field (UDF) approach.
Option 1 (Recommended): Budgets in the Datafile
Including budgets in the datafile means they are refreshed automatically every time a new file is uploaded — no manual intervention is needed after initial setup. Clients will see budget data in Apperio as soon as the file is processed.
What to include in the datafile
The datafile supports two budget columns:
- Matter Budget — the agreed fee budget for the matter, in the Matter Currency
- Matter Expense Budget — the budget for disbursements and expenses, in the Matter Currency
Both fields are optional but strongly recommended where budgets exist.
"capped" or "fixed" accordingly. This controls how Apperio presents the budget to the client — for example, capped matters will not display WIP above the agreed cap.If your PMS does not store budgets
If budgets are not held as a field in your Practice Management System, the recommended approach is to use a User Defined Field (UDF) — a custom field you create in your system or manage externally (e.g. in a spreadsheet) and then join into your SQL export query.
Follow the steps below to set this up.
Setting Up Budgets via UDF (Step-by-Step)
Use this approach when budgets are stored externally and cannot be pulled directly from a field in your PMS database.
1 Convert your budget spreadsheet to CSV
Save your budget data as a CSV file (e.g. budgets.csv). The file should include at minimum a Matter ID, a budget amount, and a currency. For example:
MatterID,BudgetAmount,Currency MAT001,50000,USD MAT002,75000,USD MAT003,20000,GBP
Example budget CSV file
123.7).2 Import the CSV into a SQL table
You will need to load the budget data into a SQL table so it can be joined to your existing query. There are two options:
Option A — Create a table and load via BULK INSERT
First, create a table to hold the budget data:
CREATE TABLE BudgetData (
MatterID VARCHAR(50),
BudgetAmount DECIMAL(18, 2),
Currency VARCHAR(10)
);
Then load the CSV using BULK INSERT:
BULK INSERT BudgetData
FROM 'C:\path\to\budgets.csv'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FIRSTROW = 2 -- Skip header row
);
Option B — Use SQL Server Management Studio (SSMS) Import Wizard
In SSMS, right-click the target database and select Tasks → Import Data. Follow the wizard to import the CSV directly into a new or existing table. This is a good option if you are not comfortable writing the BULK INSERT statement manually.
3 Join the budget data into your SQL query
Extend your existing Apperio export query to include a LEFT JOIN to the BudgetData table. This adds the budget columns alongside your existing matter and fee data.
For example, if your current query looks like this:
SELECT
m.MatterID,
m.ClientName,
m.MatterDescription,
m.FeesIncurred
FROM Matters mExtend it to include the budget join:
SELECT
m.MatterID,
m.ClientName,
m.MatterDescription,
m.FeesIncurred,
b.BudgetAmount,
b.Currency
FROM Matters m
LEFT JOIN BudgetData b ON m.MatterID = b.MatterIDUsing a LEFT JOIN ensures matters without a budget entry are still included in the export
BudgetAmount column should be mapped to the Matter Budget column in the Apperio datafile. If you also have expense budgets, include a separate column and map it to Matter Expense Budget.4 Export the result as CSV and upload to Apperio
Once the query returns the correct dataset with budget data included, export it as a CSV file in the standard Apperio datafile format and upload it as normal.
Export options include:
- In SSMS: right-click the results grid → Save Results As… → CSV
- Using a scheduled script (PowerShell, Python, or an SSIS package) to automate the export and upload
Automating the process
If budgets are updated regularly, consider automating the import and export steps to avoid manual work each time. Options include:
- A SQL Agent Job or SSIS package (SQL Server Integration Services) to schedule the import and export on a recurring basis
- A Python script using
pyodbcorpandas.to_sqlto push the CSV into SQL and trigger the export - A PowerShell script with a Windows Scheduled Task
Contact Apperio Support if you would like guidance on setting up automation for your environment.
Option 2: Entering Budgets Manually on the Platform
It is also possible to enter budgets directly on the Apperio platform, without including them in the datafile.
- Static data: Budgets entered manually are fixed at the point of entry and do not update automatically. Any changes must be made by hand.
- Outside the datafile process: Unlike the datafile method, manually entered budgets will not be refreshed or corrected by subsequent uploads. They sit entirely outside the datafile process.
- Best suited to: Fixed or one-off engagements where the budget is unlikely to change and including it in the datafile is not straightforward.
Summary
The table below summarises the key differences between the two approaches:
| Option 1 — Datafile (Recommended) | Option 2 — Manual Entry | |
|---|---|---|
| Updates automatically | ✓ Yes — refreshed on every upload | ✗ No — static until manually changed |
| Requires SQL / datafile setup | ✓ Yes | ✗ No |
| Enabled by default | ✓ Yes (if columns included) | ✗ No — contact Apperio Support |
| Best for | Ongoing matters, regularly changing budgets | Fixed/one-off engagements |
For any questions about budget setup, contact support@apperio.com.