Automate Customers Color Based on AR Aging

Posted inBlog

Spire has a feature that allows you to customize the color of a customer name in a list. But it’s not just the customer list where this color is used. For example it’s also used in the Sales Order list, or anywhere you browse to select a customer.

One reason you would want to highlight customers is if they have an overdue balance 90 days or older. In this example company, aging period 3 is set to 90 days. So any customer with a balance over 90 days should show in red. This is just one example of many possible useful color updates. Keep reading for other possible scenarios.

This is what the results look like:

In Spire, setting customer colors must be done manually for each customer. So if a customer pays their balance, they would need to be manually changed back to the default color. But we can automate this with a simple batch file. Ideally this batch file is scheduled to run regularly, for example, every few minutes.

Batch File

Included in the package is the batch file below that will perform this task. This batch file must be run from your Spire Server. The file should also be located on your server and not a network share as it does contain a password. There are 2 minimum things to edit in the batch file:

  • DBNAME is your Spire database name which can be found in the companies tab of your Spire Admin web page
  • PGPASSWORD is your “spireapp” password. This is on your server in the file: C:\ProgramData\Spire\spire.ini. Here is the entry in the spire.ini that corresponds to this example password (bold text): dburl =postgresql://spireapp:dFy7U1iis7FO4Y8K@localhost/spire
@echo off
setlocal EnableExtensions DisableDelayedExpansion

rem === Config === Minimum settings to change: DBNAME and PGPASSWORD
set "DBNAME=INSPIRE"
set "PGPASSWORD=dFy7U1iis7FO4Y8K"

rem === Config === Optional settings
set "SERVER=localhost"
set "PGUSER=spireapp"
set "PSQL=C:\Program Files\Spire\PostgreSQL\14\bin\psql.exe"
set "LOG=%~dp0%~n0.log"

rem === SQL ===  You can change this to be any PosgreSQL that you want run on a schedule

set "SQL=UPDATE customers SET  color_text = CASE WHEN ar.aged3 > 0 OR ar.aged4 > 0 THEN 4278190080 ELSE 0 END, color_back = CASE WHEN ar.aged3 > 0 OR ar.aged4 > 0 THEN 4294901760 ELSE 16777215 END FROM ar_aged_list(CURRENT_DATE) AS ar WHERE customers.cust_no = ar.cust_no;"

rem === SQL END ===


rem === LOGGING ===  log keeps only the last run to save space.

echo [%date% %time%] Starting %~nx0... > "%LOG%"

if not exist "%PSQL%" (
  echo [%date% %time%] ERROR: psql not found at "%PSQL%". >> "%LOG%"
  endlocal & exit /b 2
)

"%PSQL%" -X --set=ON_ERROR_STOP=1 -U "%PGUSER%" -h "%SERVER%" -d "%DBNAME%" -c "%SQL%" 1>>"%LOG%" 2>&1
set "RC=%ERRORLEVEL%"

if %RC% neq 0 (
  echo [%date% %time%] ERROR: %~nx0 returned %RC%. >> "%LOG%"
) else (
  echo [%date% %time%] Success. >> "%LOG%"
)

endlocal & exit /b %RC%

Note: If you are using an older version of Postgres, the line starting “C:\Program Files\Spire\PostgreSQL\14\bin\psql.exe” might need the number 14 changed to another Postgres version number. Check the file folders in C:\Program Files\Spire\PostgreSQL on your server to confirm.

Create Scheduled Task

To keep this color coding constantly up to date, you can run it as a scheduled task.

Included in the package is a batch file you can run from the server (as admin). Once created, the task will run every 10 minutes from 9:00 am for 8 hours. Adjust the timing as necessary.

@echo off
set "TASKNAME=UpdateCustColors"
set "BATCH=%~dp0UpdateCustColors.bat"

REM Create task: daily, start at 9am, repeat every 10 min, for 8 hours run as SYSTEM
schtasks /Create ^
  /TN "%TASKNAME%" ^
  /TR "\"%BATCH%\"" ^
  /SC DAILY ^
  /ST 09:00 ^
  /RI 10 ^
  /DU 08:00 ^
  /RU SYSTEM ^
  /K ^
  /F
echo Created task "%TASKNAME%".

Deployment

To deploy this solution:

  1. Download UpdateCustColors.zip
  2. Unzip on your server to a folder eg: C:\Scripts\UpdateCustColors
  3. Edit the UpdateCustColors.bat with your spireapp password and Spire database name
  4. Run the batch file to test it (See log file it creates for confirmation)
  5. Install scheduled task with right-click on SetupCustColorsTask.bat and choose: “Run As Administrator”

Summary

There are many possibilities for this type of solution. The SQL can easily be edited to only highlight customers with balances over “Period 2” (often 60 days). Colors can be added to Customers, Vendors and Inventory Items. For example, you could color code inventory items when on-hand is below re-order point, or you could automatically synchronize Spire UDF fields between modules.