Introduction

Credit Hound's report writer features calculated fields, used for calculating additional data based off existing fields. This guide instructs how to add a calculated field.

Versions Applicable

• Credit Hound v3
• Credit Hound v4
• Credit Hound v5
• Credit Hound v6

Resolution

1. Open Credit Hound as a user who has permissions to modify letters

2. Click on Tools > Letters and click Edit

3. On the right-hand side, click on the pane Field Explorer

4. Select the top-most field, usually named the letter being modified and right-click

5. Select Add calculated field

A field named calculatedField1 will now appear in the list. Right-click on this field and select Edit Calculated Fields... The window CalculatedField Collection Editor will now be displayed

Select the calculated field's Data Membership (report, Account, Contact, Transaction etc.), effectively the subtable name for the field in the report.

Name the calculated field to something meaningful, by convention this author prefixes all calculated fields with cf...

To set the functions to be used for the field, click on the ellipsis in the Expression field. The Expression Editor will open

Here you enter the expression you want to evaluate. 

In the above example we are using a simple IIF (immediate IF) function to set a greeting to read "Sirs" if the contact name is blank, otherwise to use the contact name.

Example Expressions

You can use calculated fields to perform mathematical functions on values and dates, perform text transformations and do logical comparisons, amongst other things.

• Display one piece of text or another depending on a field's value: IIF([Name]='','Sirs',[Name]). 
• Calculate days overdue: DateDiffDay([DueDate],Today())
• Remove unwanted character from field: Replace([Reference],'INV','') - will convert "INV0123456" to "0123456".
• Convert text to Upper case: Upper([Name]) - Converts "Abbey Retail" to "ABBEY RETAIL"
• Return the first name from a contact's full name: Substring([Contact].[Name],0  ,CharIndex(' ', [Contact].[Name]) )
• Return the last name from a contact's full name: Substring([Contact].[Name], CharIndex(' ', [Contact].[Name]) )