3.0 BillTrust Integration

Modified on Mon, 17 Nov at 1:59 PM

Please Note: There is an additional charge for this interface. Please contact sales if you are interested.

Overview:

This interface will send PDF copies of all invoices on a defined schedule via FTP to the bill trust system. The Dealer must have a relationship with BillTrust for this interface to be used.

Requirements:

Softbase 460 or above.

Windows Workstation or Server that has access to the SQL DB that can be used to schedule the process.

Thin File and PDF.

PDF Requirements

• Each invoice sent as an individual PDF file.

• PDF files follows specific naming convention - ACCOUNT#_INVOICE#_INVOICEDATE.pdf

Thin Billing File Requirements

• CSV Format

• The Thin Billing File is bundled with the corresponding PDFs into a zip file for processing.

• The billing file includes the following information:

Invoices: PDF name (to associate the line to the actual PDF), Account Number, Billing Address, Invoice Number, Total Amount Due, Invoice Date, and Due Date are required fields in all cases. Billtrust can fail the invoice file as a safeguard if any of these fields are blank.

Branch is required when utilizing virtual sites with multiple online portals (i.e., eInvoice Connect, Invoice Central), and/or if you have branch-level deposit accounts.

.Zip file is named:

Install Information

Install SoftbaseBilltrustSetup.exe

The Default Directory is - C:\Program Files\SoftbaseBillTrust\

Create windows task

Using the “Windows Key“ + “R” to open run and type “taskschd.msc“. This will open Task Scheduler.
Under the actions panel, you can choose to create a back task or create a task. Click “Create Task“.The “Create Task” screen will appear. Select the “General” tabIn the “Name” field, give the task a name. Example: “BillTrust Nightly“.In the “Description” field, here you can describe what the task is for and what it will do.The last section is the “Security options“, here you can set who can run this task and if the task has admin right.Select the “Triggers” tab.Select “New…“.The “New Trigger” window will appear, here you have the option to set when the task will start. (For example Select when you would like the task to start in the “Begin the task” drop-down menu.Modify the “Settings” area as desired.“Enabled” is checked by default.Select “OK“.Select the “Actions” tab, then select “New“.The “New Action” window will open.In the “Action” drop down, “Start a program” is set by default. Change it if desired.Select “Browse…” next to the “Program/script” fieldBrowse to the program you are wanting to schedule a task for ( C:\Program Files\SoftbaseBillTrust\SoftbaseBillTrust.exe).Select “OK“.Select “OK“.

Setting the login information:

This is done at the company level.

Notes:

The first time the program runs it will mark all invoices as sent and will not send a new file. This prevents sending old invoices created before the dealer has a relationship with BillTrust. The second time and all subsequent time the program runs it will send all invoices not marked as sent.

Running the Billtrust program will add needed fields to the Softbase DB.

Log File:

There is a log file in the default directory that will help in supporting any possible issues.

Sample: SoftbaseBillTrust_Log.txt

2021-10-21 16:04:58 - Program Starting...

2021-10-21 16:05:03 - Processed 4 invoices.

2021-10-21 16:05:03 - Program eneded normally.

SQL Used to gather Invoices to Send

WITH CTE_ARDetail
AS (
SELECT InvoiceNo, SUM(Amount) AS Balance, Max(Due) AS DueDate
FROM ARDetail
WHERE HistoryFlag = 0
GROUP BY InvoiceNo
)
SELECT InvoiceReg.InvoiceNo, DueDate, InvoiceReg.SaleBranch, InvoiceReg.SaleDept, InvoiceReg.SaleCode, InvoiceReg.ExpBranch, InvoiceReg.ExpDept, InvoiceReg.ExpCode,
WO.FormOfPayment, InvoiceReg.Customer, InvoiceReg.GuaranteedMaintenance, InvoiceReg.BillTo, InvoiceReg.BillToName, InvoiceReg.BillToZipCode, InvoiceReg.ShipTo,
InvoiceReg.ShipToName, InvoiceReg.PONo, InvoiceReg.InvoiceDate, InvoiceReg.TotalTax, InvoiceReg.GrandTotal, InvoiceReg.BillTrustFlag, Customer.Address, Customer.SubName,
Customer.POBox, Customer.City, InvoiceReg.Customer As CustInt, Customer.State, Customer.ZipCode, Customer.Country, Customer.NoPrintInvoice, Customer.EMailInvoice,
Customer.EMailInvoiceAddress, WO.Type, WO.ShipName, WO.ShipAddress, WO.ShipCity, WO.ShipState, WO.ShipZipCode, WO.Salesman Salesperson
FROM InvoiceReg
INNER JOIN Customer ON InvoiceReg.BillTo = Customer.Number
INNER JOIN WO ON InvoiceReg.InvoiceNo = WO.WONo
INNER JOIN ARTerms ON WO.FormOfPayment = ARTerms.Terms
INNER JOIN CTE_ARDetail ON InvoiceReg.InvoiceNo = CTE_ARDetail.InvoiceNo
WHERE ((InvoiceReg.BillTrustFlag = 0) OR (InvoiceReg.BillTrustFlag IS NULL)) AND (InvoiceReg.BillTrustExclude = 0 OR InvoiceReg.BillTrustExclude IS NULL)
ORDER BY InvoiceNo DESC