checking which borrowers have email addresses

The EMAIL table is not used and would therefore be empty. Sometimes tables were created in anticipation of functionality but the functionality is then implemented in a different way.

The borrowers email addresses are held in the CONTACT table. The CONTACT table holds email addresses but is actually designed that it can hold other types of contact data (except postal addresses) as required in the future. The EMAIL table is not.

The Borrower entity relationship diagram shows the tables associated with the BORROWER table.

In order to get email addresses out via SQL (It can also be done via Decisions) the SQL would need to join the BORROWER and CONTACT tables and look something like

select B.BARCODE,B.INDEX_NAME,C.DISPLAY_VALUE, C.PREFERRED
from BORROWER B, CONTACT C
where B.BORROWER_ID = C.TARGET_ID
and CONTACT_TYPE = 0
and TARGET_TYPE=0

The two _TYPE values are not strictly required as there is only borrowers and emails in the table but for future proofing it is better to be explicit.

This will list all borrowers who have rows in the CONTACT table. The PREFERRED value equates to whether the email address is the default or not.

If you want to list all borrowers regardless of whether they have CONTACT rows then the SQL would be

select B.BARCODE,B.INDEX_NAME,C.DISPLAY_VALUE, C.PREFERRED
from BORROWER B, CONTACT C
where B.BORROWER_ID *= C.TARGET_ID
and CONTACT_TYPE = 0
and TARGET_TYPE=0

Although this should be run with care as you will bring back every borrower. It should be tested on the MIS server and you may wish to set further conditions.

I would suggest using Decisions anyway. The email objects are under Borrowers | Borrower details | Contact details. The Contact Address would be the email. Again the term contact is used as in the future more than just emails may be stored under this object.

Brian Crampton
Developer, Talis