Contacts in LedgerSMB

Contacts Overview

In lsmb, a "Contact" is either a person, a company or a robot.

The person, company or robot has a uniquely corresponding entity, where it is assigned an entity_class such as Vendor, Customer, Employee, Robot. This class is a marker only. It has no bearing on what the Contact is able to do, so, for example, a Contact marked as "Employee" can also act as a vendor who we purchase from and a customer who we sell things to.

The entity may be further extended such that it becomes:

• A lsmb user, by linking with the users table. The schema allows the Contact to link with multiple lsmb users (so a person may have one username they use for general accounting and another they use for administrative functions), though the UI does not currently support this. A user belongs only to a single Contact entity.

• An employee, by linking with entity_employee. This is a 1:1 relationship. Table entity_employee is used to extend the entity. Note that employee has start and end dates, but there is no provision for multiple employments, such as when an employee leaves and is later re-employed by the company.

The Contact entity may have multiple entity_credit_accounts associated, which can be classed as either "Vendor or "Customer". These do affect what a Contact is able to do. For example, to act as a vendor who we purchase from, the Contact must have an entity_credit_account of class "Vendor". For us to be able to raise a sales invoice to the Contact, it must have an entity_credit_account of class "Customer".

A Contact may have multiple entity_credit_accounts of each class, for example it may have multiple "Customer" accounts corresponding with different divisions of a single company to be invoiced separately.

The entity_credit_account may optionally be assigned a business type.

A Contact may be known by multiple names through the entity_other_name table, though this is not yet supported in the UI.

Multiple notes, locations (being addresses) or contact elements (being a single telephone number, e-mail address, social media handle or similar) may be associated with either the overall Contact, or individual entity_credit_accounts.

An outline of the related database tables is as follows:

 person ┐
  robot ┤--entity
company ┘   |
            |--users
            |--entity_employee
            |--entity_other_name
            |--entity_note
            |--entity_to_contact
            |--entity_to_location ------location
            |--entity_credit_account
                 |--eca_to_location -----location
                 |--eca_to_contact
                 |--eca_note
                 |--person (via primary_contact)
                 |--business

Contact Search

The lsmb UI provides a Contact Search screen. Each filled-in field tightens the filter criteria. Clicking 'Search' without filling any fields in returns a list of all Contacts.

The search and filtering is performed by the contact__search function defined in sql/modules/Company.sql.

The behaviour of each filter field is as follows:

Entity Class

• Filter tests against:
  • entity.entity_class
  • entity_credit_account.entity_class
• Searches for an exact match.
• Returns the person or company records having the specified entity class, OR having entity_credit_accounts with the specified entity class.

Name

• Filter should test against:
  • entity.name
  • company.legal_name
  • person.[first_name|last_name|middle_name]
  • entity_other_name.other_name
  • robot.[first_name|last_name|middle_name]
• Filter should be case and accent insensitive
• Filter should match on start of words (~* '\m$SEARCH_TERM')
• Current behaviour is broken:
  • Search term is interpreted differently for a person compared to a company.
  • For a person, the filter is applied as a full-text search on the concatenated name, but this breaks in surprising and non-obvious ways. For example, it's not possible to search for the Chinese surname 'An' as this search term is normalised away. Searching for 'Banks' will also return results for 'Bank'.
  • For a company, the filter is applied on the start of its legal name (ILIKE '$SEARCH_TERM%'). The entity.name and entity_other_name fields are not searched at all. The filter should be applied consistently.

Control Code

• Filter tests against:
  • entity.control_code
• Filter should be case insensitive
• Matches first part of control code (ILIKE '$SEARCH_TERM%')
• Current behaviour is case-sensitive

Email

• Filter should test against:
  • entity_to_contact.contact WHERE contact_class_id IN(email classes)
  • eca_to_contact.contact WHERE contact_class_id IN(email classes)
• Filter should be case insensitive
• Filter should match any part of e-mail (ILIKE '%$SEARCH_TERM%')
• Currently present in UI but not implemented (issue #2821)

Phone

• Filter should test against:
  • entity_to_contact.contact WHERE contact_class_id IN(phone classes)
  • eca_to_contact.contact WHERE contact_class_id IN(phone classes)
• Filter should ignore spaces
• Filter should match any part of phone numbers (ILIKE '%$SEARCH_TERM%')
• Currently present in UI but not implemented (issue #2821)

Contact

• Filter should test against:
  • entity_to_contact.contact WHERE contact_class_id NOT IN(phone or email classes)
  • eca_to_contact.contact WHERE contact_class_id NOT IN(phone or email classes)
• Filter should be case and accent insensitive
• Filter should match any part of contact (ILIKE '%$SEARCH_TERM%')
• Currently does full text search of contact description

Only users

• Filter should test for:
  • EXISTS (SELECT 1 FROM users WHERE users.entity_id = entitity.id)

Notes

• Filter searches:
  • entity_note.note
  • entity_note.subject
  • eca_note.note
  • eca_note.subject
• Currently uses Full Text Search with English configuration
• Currently note subject field is not searched
• Misleading:
  • Search behaviour very different to other fields
  • Use of default English configuration is no good for other locales
• But what is alternative? ILIKE '%$SEARCH_TERM%'? But performance?
• If we use Full Text Search and cater for a variety of languages, should we use 'simple' configuration instead of default English language configuration?
• I propose leaving this unchanged for now and writing tests for the current behaviour.

Account Number

• Filter searches the credit account number:
  • entity.meta_number
• Filter should ignore spaces
• Matches on first part of nunber (ILIKE '$SEARCH_TERM%')

Address

• Filter should search against:
  • location.line_one linked via entity_to_location
  • location.line_two linked via entity_to_location
  • location.line_three linked via entity_to_location
  • location.line_one linked via eca_to_location
  • location.line_two linked via eca_to_location
  • location.line_three linked via eca_to_location
• Filter should be case and accent insensitive
• Filter should match on start of words (~* '\m$SEARCH_TERM')
• Currently doesn't search entity locations

City

• Filter should search against:
  • location.city linked via entity_to_location
  • location.city linked via eca_to_location
• Filter should be case and accent insensitive
• Filter should match on start of words (~* '\m$SEARCH_TERM')
• Currently doesn't search entity locations
• Currently matches on first part of city (ILIKE '$SEARCH_TERM%')

State/Province

• Filter should search against:
  • location.state_code linked via entity_to_location
  • location.state_code linked via eca_to_location
• Filter should be case and accent insensitive
• Filter should match on start of words (~* '\m$SEARCH_TERM')
• Currently doesn't search entity locations
• Currently matches on first part of state (ILIKE '$SEARCH_TERM%')

Zip/Postal Code

• Filter should search against:
  • location.mail_code linked via entity_to_location
  • location.mail_code linked via eca_to_location
• Filter matches on first part of mail code (ILIKE '$SEARCH_TERM%')
• Filter should ignore spaces
• Filter should be case insensitive
• Currently doesn't search entity locations

Country

• Filter should search against:
  • entity.country
  • location.country linked via entity_to_location
  • location.country linked via eca_to_location
• Filter accepts short country code ('CY') OR case-insensitive exact match on full country name ('Cyprus')
• Currently doesn't consider entity or entity_credit_account locations
• UI text field Should be changed to drop-down selection of active countries

Active From

• Filters on:
  • entity_credit_account.startdate
• Misleading:
  • Not strictly a contact filter
  • Contacts with no entity_credit_account are considered valid for all dates
  • Contact can still be active even if it's credit accounts aren't
  • Employee also has startdate but isn't considered
• Better name: "Account active during - period starting"

Active To

• Filters on:
  • entity_credit_account.enddate
• Misleading:
  • Not strictly a contact filter
  • Contacts with no entity_credit_account are considered valid for all dates
  • Contact can still be active even if it's credit accounts aren't
  • Employee also has enddate but isn't considered
• Better name: "Account active during - period ending"

Future

I wonder if the concept of a Contact's 'Entity Class' being user-selected from a range of options is wrong and misleading? Wouldn't it be better if this was dynamic according to its various properties and attributes?

For example... If an Contact has a user account, it is a 'User'. If it has a vendor credit account, it is a 'Vendor'. If it has an employee record, it is an 'Employee'. It can be all or none of these things at once, so manual selection of just one of the options at any time is wrong.

Data Duplication

• Date of birth is duplicated between person.birthdate and entity_employee.dob - it should only be recorded in the person, being a property of the person rather than the employee.
• Name is duplicated between entity.name and company.legal_name / person.[first_name|middle_name|last_name].