Creating Custom SQL Letter Loops for Local Print Letter Templates

Custom SQL letter loops in ACE allow you to present dynamic, repeating data within your letter templates. By creating custom SQL reports and integrating them into your letter templates, you can display detailed tables of information—such as lists of debts, transactions, or other relevant data—tailored to each account or context.

This guide provides a comprehensive walkthrough for setting up custom SQL letter loops, from writing the SQL report to testing the final letter output.

Prerequisites:

• Familiarity with SQL and ACE letter setup.

• Access to LibreOffice (compatible version with ACE).

Caution:

Developing and testing custom SQL letter loops should be conducted outside of working hours to prevent system impacts. Alternatively, consider requesting custom development through InterProse support.

Setup Overview

To create and use custom SQL letter loops, follow these steps:

1. Write a SQL report.

2. Download a CSV copy of the report.

3. Register the CSV as a LibreOffice database.

4. Create a letter template with a table for the letter loop.

5. Save and upload the letter template to a letter profile.

6. Test and verify the letter outputs.

Step 1: Write a SQL Report

Objective: Create a SQL report that retrieves the data you want to display in your letter loop.

1.1. Define the SQL Report Identifier

• Choose a clear and descriptive identifier for your SQL report. This identifier must match the name of the LibreOffice database you will create later.

• Example: LETTER_LOOP_DEBT_TRANS

1.2. Construct the SELECT Clause

• Include all the fields you want to display in the letter loop.

• Alias each field using the AS keyword with a single lowercase word or camelCase naming.

• Example:

SELECT
  debt.client_account_number AS "clientAccountNumber",
  debt_trans.debt_id AS "debtID",
  TO_CHAR(debt_trans.trans_date, 'MM/DD/YYYY') AS "transDate",
  debt_trans.type_name AS "type",
  '$' || TO_CHAR(SUM(debt_trans.amount * 0.01), 'FM999,999,990.00') AS "amount"

1.3. Construct the FROM Clause

• Include all necessary tables to retrieve the required data.

• Ensure proper joins between tables using INNER JOIN, LEFT JOIN, etc., or in the WHERE clause.

• Example:

FROM
  debt_trans
  INNER JOIN debt ON debt_trans.debt_id = debt.debt_id
  INNER JOIN client ON debt.client_id = client.client_id

1.4. Construct the WHERE Clause

• Include contextual identifiers to link the data to a specific account, bundle, or other context.

• Use supported parameters enclosed in ${Sequence:Parameter} format.

• Supported Parameters:

  • ${Sequence:Debt ID} or ${Sequence:debtID} or ${Sequence:DEBTID}

  • ${Sequence:Bundle ID} or ${Sequence:bundleID} or ${Sequence:BUNDLEID}

  • ${Sequence:Client ID} or ${Sequence:clientID} or ${Sequence:CLIENTID}

  • ${Sequence:Legal Case ID} or ${Sequence:legalCaseID} or ${Sequence:LEGALCASEID}

  • ${Sequence:Payment Plan ID} or ${Sequence:paymentPlanID} or ${Sequence:PAYMENTPLANID}

  • ${Sequence:Payment Trans ID} or ${Sequence:paymentTransID} or ${Sequence:PAYMENTTRANSID}

  • ${Sequence:Payment Schedule ID} or ${Sequence:paymentScheduleID} or ${Sequence:PAYMENTSCHEDULEID}

  • ${Sequence:Letter ID} or ${Sequence:letterID} or ${Sequence:LETTERID}

  • ${Sequence:Letter Config ID} or ${Sequence:letterConfigID} or ${Sequence:LETTERCONFIGID}

  • ${Sequence:Letter Destination ID} or ${Sequence:letterDestinationID} or ${Sequence:LETTERDESTINATIONID}

  • ${Sequence:Letter Series Config ID} or ${Sequence:letterSeriesConfigID} or ${Sequence:LETTERSERIESCONFIGID}

• Usage Notes:

  • These parameters allow the SQL report to dynamically retrieve data based on the context in which the letter is generated.

  • The parameter names are case-insensitive and can be used in any of the formats shown.

  • Incorporate the parameters into your WHERE clause to filter data appropriately.

• Example WHERE Clause Using a Parameter:

WHERE
  debt_trans.debt_id = ${Sequence:Debt ID}
  AND debt_trans.operator = 'RECEIVED'

In this example, ${Sequence:Debt ID} dynamically inserts the debt ID of the account for which the letter is being generated.

1.5. Group and Order the Results

• Use GROUP BY and ORDER BY clauses to organize the data as needed.

• Example:

GROUP BY
  debt_trans.debt_id,
  debt.client_account_number,
  debt_trans.payment_trans_id,
  debt_trans.type_name,
  debt_trans.trans_date

ORDER BY
  debt_trans.trans_date

1.6. Complete SQL Report Example

SELECT
  debt.client_account_number AS "clientAccountNumber",
  debt_trans.debt_id AS "debtID",
  TO_CHAR(debt_trans.trans_date, 'MM/DD/YYYY') AS "transDate",
  debt_trans.type_name AS "type",
  '$' || TO_CHAR(SUM(debt_trans.amount * 0.01), 'FM999,999,990.00') AS "amount"
FROM
  debt_trans
  INNER JOIN debt ON debt_trans.debt_id = debt.debt_id
  INNER JOIN client ON debt.client_id = client.client_id
WHERE
  debt_trans.debt_id = ${Sequence:Debt ID}
  AND debt_trans.operator = 'RECEIVED'
GROUP BY
  debt_trans.debt_id,
  debt.client_account_number,
  debt_trans.payment_trans_id,
  debt_trans.type_name,
  debt_trans.trans_date
ORDER BY
  debt_trans.trans_date;

Note:

The syntax and structure of the SQL report are critical. Errors can lead to system issues or incorrect data retrieval.

1.7. Understanding the Example SQL Report

The example SQL report LETTER_LOOP_DEBT_TRANS is designed to extract detailed received transaction information for a specific debt within InterProse ACE. This data will be used to build a letter loop in your letter template, allowing you to present a table of transactions dynamically within the letter.

What the SQL Report Pulls:

• Client Account Number (clientAccountNumber):

  • The debtor's account number assigned by the client.

• Debt ID (debtID):

  • The unique identifier for the debt.

• Transaction Date (transDate):

  • The date of each transaction, formatted as 'MM/DD/YYYY'.

• Transaction Type (type):

  • The type of transaction (e.g., PAYMENT, CREDIT, NSF).

• Amount (amount):

  • The total amount for each transaction type and date, formatted as currency.

How This Information Builds the Letter Loop Template:

1. Creating the Letter Template:

   1. Insert a Table:

      1. In your LibreOffice letter template, insert a table named sql_loop_LETTER_LOOP_DEBT_TRANS.

      2. The table will have headers matching the fields from the SQL report.

   2. Mapping Fields:

      1. Use the data source viewer to drag and drop the fields (clientAccountNumber, debtID, transDate, type, amount) into the table.

2. Implementing the Letter Loop:

   1. Dynamic Data Population:

      1. When generating the letter, the table will loop through each transaction record pulled by the SQL report for the specific Debt ID.

      2. Each row in the table represents a different transaction related to that debt.

Example of How the Data Appears in the Letter:

Step 2: Download a CSV Copy of the Report

Objective: Obtain a CSV file containing sample data from your SQL report for use in creating the letter template.

2.1. Run the SQL Report

• Execute the SQL report within ACE, using appropriate test parameters (e.g., a specific Debt ID).

2.2. Export the Report as CSV

• Save the report results as a CSV file.

• Naming Convention: The CSV file name must match the SQL Report Identifier.

  • Example: LETTER_LOOP_DEBT_TRANS.csv

2.3. Save the CSV File

• Save the CSV file in a dedicated folder for LibreOffice letter files.

• Recommendation: Keep all related files (CSV, LibreOffice database, letter templates) organized in the same folder.

Step 3: Register the CSV as a LibreOffice Database

Objective: Convert the CSV file into a registered database in LibreOffice for use in the letter template.

3.1. Open LibreOffice

1. File → New →Database

2. A Database Wizard will popup:

   1. Choose “Select Database” on the left section.

   2. Choose “Connect to an existing database” and select “Spreadsheet” from the dropdown.

3. Follow the prompts:

   1. Next → Browse, select (LETTER_LOOP_DEBT_TRANS.csv) → Next → Finish.

   2. Change the file name to "LETTER_LOOP_DEBT_TRANS.odb" and remember its location.

4. Once saved, you can close LibreOffice.

Important: If you update the CSV file later (e.g., after modifying the SQL report), ensure it remains in the same location with the same name so the LibreOffice database can continue to access it.

Step 4: Create a Letter Template with a Table for the Letter Loop

Objective: Design a letter template in LibreOffice Writer that incorporates the data from your SQL letter loop.

4.1. Open Your Letter Template in LibreOffice Writer

• Launch LibreOffice Writer to create a new document or open an existing letter template.

4.2. Insert a Table for Headers

1. Insert Header Table:

   1. Go to Table → Insert Table.

   2. Specify the number of columns you need (matching the fields from your SQL report).

   3. Set the number of rows to 1 (this will be used for the headers).

   4. Do not set a table name for this table.

   5. Click OK to insert the table.

2. Add Headers:

   1. In the header table, enter the headers for each column.

      1. Example Headers: Date, Amount, Type, Client Account Number, Debt ID.

4.3. Insert a Second Table for Data

1. Position Cursor:

   1. Place your cursor below the header table.

2. Insert Data Table:

   1. Go to Table → Insert Table.

   2. Specify the same number of columns as the header table.

   3. Set the number of rows to 1 (this will serve as a placeholder for the data).

   4. Click OK to insert the table.

3. Name the Data Table with a Loop Identifier:

   1. Right-click on the data table and select Table Properties.

   2. In the Table tab, find the Name field.

   3. Enter the loop name in the format sql_loop_[SQL_Report_Identifier].

      1. Example: sql_loop_LETTER_LOOP_DEBT_TRANS

   4. Click OK to apply the changes.

4.4. Insert Data Fields into the Data Table

1. Open Data Sources:

   1. Go to View → Data Sources to open the data sources panel.

2. Navigate to Your Database:

   1. In the data sources panel, expand your registered database (LETTER_LOOP_DEBT_TRANS).

   2. Expand Tables and select the table corresponding to your CSV data.

3. Insert Fields:

   1. Drag and drop the desired fields into the corresponding cells in the data table.

      1. Fields to Insert: transDate, amount, type, clientAccountNumber, debtID.

4.5. Format the Tables

1. Adjust Column Widths and Alignment:

   1. Modify column widths to ensure data displays correctly.

   2. Align text in cells as needed (e.g., center-align headers, right-align amounts).

2. Apply Styling:

   1. Format fonts, borders, and shading to match your organization's branding.

   2. Ensure consistency between the header and data tables.

4.6. Save and Upload the Template

• Save your letter template in the LibreOffice format (.odt).

• Proceed to upload the updated template to the appropriate letter profile in ACE (covered in Step 5).

Note: By configuring the headers and the data into two separate tables, you prevent issues where the headers might repeat for each data row. ACE will loop over the data table and insert as many rows as needed based on the data retrieved, while the header table remains static at the top.

Step 5: Save and Upload the Letter Template to a Letter Profile

Objective: Integrate your custom letter template into ACE for use in generating letters.

5.1. Save the Letter Template

• Ensure the letter template is saved with all recent changes.

• Use a clear and descriptive file name.

  • Example: CustomDebtTransactionLetter.odt

• Keep the file in the same folder as your CSV and database files for organization.

5.2. Upload to ACE

• Log into ACE and navigate to Setup → Letter Profiles.

• Create a new letter profile or select an existing one to upload your template.

• Important: The letter profile type does not affect the context of the letter loop. The data returned is based on the context identifiers in your SQL report.

5.3. Upload the Template

• In the letter profile, upload your saved letter template (.odt file).

• Save the letter profile settings.

Step 6: Test and Verify the Letter Outputs

Objective: Ensure that the letter generates correctly and displays the expected data.

6.1. Select a Test Account

• Choose an account that has relevant data matching your letter loop context.

• For example: If your letter loop displays transactions for a debt, select an account with multiple transactions.

6.2. Generate the Letter

• From the account, schedule or generate the letter using the letter profile you set up.

6.3. Review the Output

• Open the generated letter and verify:

  • The header table displays correctly at the top.

  • The data table populates with all relevant records.

  • All fields are populated appropriately.

  • The formatting is as expected.

6.4. Troubleshoot if Necessary

• If data is missing or incorrect:

  • Revisit your SQL report to ensure it retrieves the correct data.

  • Check the CSV file and database linkage.

  • Confirm that the fields in the letter template are correctly mapped.

  • Ensure the table names and loop identifiers are correctly set.

Additional Notes

• Context Identifiers: The letter loop data is determined by the context identifiers in your SQL report (e.g., Debt ID, Bundle ID). Ensure your SQL report uses the correct parameter for the desired context.

• Data Privacy: Be cautious with sensitive data. Ensure that any data displayed in letters complies with privacy regulations and company policies.

• Updating Reports: If you modify the SQL report after initial setup, remember to update the CSV file and refresh the LibreOffice database as needed.

• Limitations: SQL Designer report letter loop data is not available to letter vendor files. This method is intended for local print letters generated within ACE.