Jarvis Data Dictionary / Business Rules

Reports / Contacts

Lost

Lost: a contact who does not have any active modes of contact (excludes LinkedIn URL)

https://sydneyuni-adv.lightning.force.com/lightning/setup/ObjectManager/Contact/FieldsAndRelationships/00N4a000000W1yD/view

Official USyd definition of “Lost Contact” : Strict - All fields in all the contact methods must be blank, (as opposed to incomplete addresses where some fields are blank and make the contact uncontactable).

IF(
AND(
ISBLANK( Phone ),
ISBLANK( MobilePhone ),
ISBLANK( HomePhone ),
ISBLANK( AQB__BusinessPhone__c ),
ISBLANK( AQB__MobileWork__c ),
ISBLANK( AssistantPhone ),
ISBLANK( Fax ),
ISBLANK( OtherPhone ),
ISBLANK( Email ),
ISBLANK( AQB__PersonalEmail__c ),
ISBLANK( AQB__BusinessEmail__c ),
ISBLANK( AQB__SchoolEmail__c ),
ISBLANK( USYD_Best_Contact_Email__c ),
ISBLANK( AQB__OtherEmail__c ),
ISBLANK( AQB__BusinessAddress__c ),
ISBLANK( USYD_Contact_Mailing_Street__c ),
ISBLANK( USYD_Contact_Mailing_City__c ),
ISBLANK( USYD_Contact_Mailing_State__c ),
ISBLANK( USYD_Contact_Mailing_Zip_Postal_Code__c ),
ISBLANK( USYD_Contact_Mailing_Country__c ),
ISBLANK( OtherStreet ),
ISBLANK( OtherCity ),
ISBLANK( OtherState ),
ISBLANK( OtherPostalCode ),
ISBLANK( OtherCountry )
),
TRUE,
FALSE
)

• Data Dictionary approve “Lost” logic AlainGasquet , 10 Feb 2023
• Data Dictionary approve “Lost” logic Miranda Adams , 10 Feb 2023
• Data Dictionary approve “Lost” logic jessica.wood , 10 Feb 2023
• Data Dictionary approve “Lost” logic apirami.kumaradevan, 10 Feb 2023
• Data Dictionary approve “Lost” logic Polina Nikulina, 10 Feb 2023

Reports

Contactable Alumni Report

https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000KIfiEAG/view?queryScope=userFolders

Official report in the Executive folder

Total Alumni minus:

• Deceased Alumni,

• Lost - i.e. all contact method fields isnull

• Do Not Contact / Do Not Call / Do Not Mail

• Mailing Address Status = Inactive, Partial Address, Return to Sender

• Data Dictionary approve “Contactable Alumni Report” logic AlainGasquet , 10 Feb 2023
• Data Dictionary approve “Contactable Alumni Report” logic Miranda Adams , 10 Feb 2023
• Data Dictionary approve “Contactable Alumni Report” logic jessica.wood , 10 Feb 2023
• Data Dictionary approve “Contactable Alumni Report” logic apirami.kumaradevan , 10 Feb 2023
• Data Dictionary approve “Contactable Alumni Report” logic Polina Nikulina , 10 Feb 2023

Contact

Alternate ID’s (Nonunique Alternative id)

Store Student, Staff, Seymore Centre, etc ID’s

Values are captured in field Nonunique Alternative id (Not Alternative Id’s)

• All Contact Type ‘Alumni’, ‘Graduant’, ‘Student’ should have Student ID’s,

• All Contact Type ‘Staff’ should have Staff ID’s,

• Student id must = 9 Characters

• Contact Type = Alumni, Graduant, Student, then Nonunique Alternative id cannot be NULL

• Data Dictionary approve “Alternate ID’s” logic AlainGasquet , 24 Feb 2023
• Data Dictionary approve “Alternate ID’s” logic Miranda Adams , 24 Feb 2023
• Data Dictionary approve “Alternate ID’s” logic jessica.wood , 24 Feb 2023
• Data Dictionary approve “Alternate ID’s” logic apirami.kumaradevan , 24 Feb 2023
• Data Dictionary approve “Alternate ID’s” logic Polina Nikulina , 24 Feb 2023

Account Create

New Account Entry

Utility to create account and contact.

AQ Managed - Can only use for household records / Cant use for organizational records - Cant restrict

Account

Account Name

Account Name

• AVE-105 Household Account Name , Need to decide future structure, 28 Feb 2023

Account

Account Mailing Address

AVE-107 Address Management

AVE-107 Address Management,

Account Mailing Address Formatting:

Aust Post Mailing format:

• Country -> Capitalized - Need it - to excluded via formula in mailing

• State → Capitalized - Abbreviated,

• Aperture DS Format

Account

RFM

A star is awarded for each of the following five conditions: having a giving record, having a pending opportunity, having a largest gift over a defined amount, having a total amount of giving over a defined amount, and having made a payment this year.

Not used (See if can be removed from layout)

Account

Most Recent Gift/Pledge Date

The date of the most recent gift.

Pulling any gift / pledge - does it do soft credits? Hard vs Soft Credits - includes soft credits

Account

Account Type

A picklist of the highest level categorization of this Account. The category can be further described in the Secondary Type field.

Account

Account Phone

Account Phone = Home Phone

Mobile Phone = Individual Phone

Account

Is Active Prospect?

Checkbox, Describes whether a prospect is active or Inactive. For example, inactive in case of deceased.

Account

Prospect Manager

The Prospect Manager for this Account.

Lightning Contact Utilities

Join

Lightning Contact Utilities

Separate

Lightning Contact Utilities

Decease

Contact

Salutation

Title

Affinaquest Salutations Derivation

Contact

Name

Name

Contact

Contact Types

AVE-16 Contact Types

Contact

Contact

Contact

Contact

Address

Transaction

Acknowledgement

Transaction

COA DFN

look up from COA

Transaction

COA code

look up from COA

Transaction

COA designation

look up from COA

Transaction

COA School/Unit

Used for receipting purpose

look up from COA

Transaction

Designation

The entry in the Chart of Accounts to which the donor wishes this Transaction to be deposited

look up from COA

Contact

Contact

Email / Email Preference

AVE-344 - Getting issue details... STATUS

https://sydneyuni.atlassian.net/jira/software/projects/AVE/boards/1066?selectedIssue=AVE-351

Email is a standard Salesforce field.

Email is a linked field, syncing to the value on the associated ‘Email Preference’ selection. eg Personal Email.

When an ‘Email Preference’ is selected, a link is formed with the value in the email fields, changes to the associated email field or into ‘Email’ directly will affect a change to the the corresponding linked field value.

In reports, field ‘Preferred Email 'suppresses the field value in reports when a contact is marked as either do not email or do not contact. Value will still be visible on screen, and can still be extracted in reports from Field 'Email’ or specific email type.

Validations are in place to prevent a preference being selected with a null value.

Email is locked to read only on USYD Contact Lightning Page Layout to reduce integrity errors.

To understand how the Email field is populated, please watch this video:

vido1384068787.mp4

Input:
Email address data should be added to the specific email fields. The contacts preferred email address for communications should then be selected in 'Email Preference'.
Same process should occur from both a single or mass entry.

Output:
When exporting email details, best practise is to export ‘Preferred Email’ field value. While this field applies a validation on suppressing DNC and No Email flags, all Handling Preferences should be applied relevant to the communication type.

In addition a USYD formula field 'Best Contact Email' has been created to follow the below logic to identify email addresses where no preference is selected.

'Best Contact Email' formula: IF(NOT ISBLANK(Email) && FIND("uni.sydney.edu.au",Email)=0, Email, IF(NOT ISBLANK( AQB__PersonalEmail__c) && FIND("uni.sydney.edu.au",AQB__PersonalEmail__c)=0, AQB__PersonalEmail__c, IF(NOT ISBLANK(AQB__BusinessEmail__c) && FIND("uni.sydney.edu.au",AQB__BusinessEmail__c)=0, AQB__BusinessEmail__c , IF(NOT ISBLANK( AQB__OtherEmail__c) && FIND("uni.sydney.edu.au",AQB__OtherEmail__c)=0, AQB__OtherEmail__c, IF(NOT ISBLANK(AQB__SchoolEmail__c) && FIND("uni.sydney.edu.au",AQB__SchoolEmail__c)=0, AQB__SchoolEmail__c ,'') ) ) ) )

https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K5TkEAK/view

https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K5sfEAC/view

Single Instance:

1. Copy the value in ‘Email’ to the appropriate email field below, (i.e. “Personal Email”)

2. Select the email preference based on the field that it was copied to, (i.e. “Personal Email”)

3. Save

Mass Fix:

1. Export values from ‘Email' field. Note, Preferred email will suppress any DNC and Do Not email values

2. Import email address to correct email field eg, Personal Email

3. Set ‘Preferred Email’ based on step 2

• AlainGasquet
• Jessica
• Rommel
• Miranda
• Yeng/Manpreet
• Liz (Single CRM)
• Adriana (PO)
• Api
• SME/Business Owner

Contact

Phone / Phone Preference

AVE-417

Phone is a standard Salesforce field.

Phone is a linked field, syncing to the value on the associated ‘Phone Preference’ selection. eg Mobile.

• rommel.ngo as discussed, can you please investigate ? (FI jessica.wood , AlainGasquet , Miranda Adams , Yeng Sembrano )

Contact

Contact Type

TAP-211

Primary & Secondary Contact Type - How to define donor- as soon give legal credit amount < 0.01 - problem is people with soft credit - gift attached to another account - what do we do with people who only has a soft credit ?? soft credit = donor ?? GRC ?? Gift Recognition Credit always 100% ?? (Only Household account)

Contact/Account

Mailing Address Preference

A read only field used to indicate what type of address the contact current mailing address is associated. The valid values are “Account Mailing”, “Business”, or “Alternate”,

AVE-25 - Getting issue details... STATUS

Contact/Account

Mailing Address Type

A Description of the nature of the address information, such as residence, business, etc. [Drop down list with selection: Home, Business, Vacation, Other Sydney Student Mailing Address, etc.

AVE-187 - Getting issue details... STATUS - See ticket for development and final values

• Permanent address = Home Address;

• Business address = Business Address;

• SITS Address = Sydney Student Contact Address

• Internal USYD Address

• PO Boxes = Postal Address

https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K2kOEAS/view

• jessica.wood
• Miranda Adams
• AlainGasquet
• Monica Kluegel

Contact

Mailing Address Status

AVE-177

Mailing Address Status indicates whether the Mailing Address is Active, Inactive, RTS etc.

For manual data entry, if a standard TAP user updates the address, the Mailing Address Status is automatically set to ‘Active’

For mass data activity such as re-formatting and data cleansing, it is imperative that users extract the Mailing Address Status along with the address details and re-upload the reformatted data with the pre-existing status. This will prevent Inactive, Last Known, RTS data being incorrectly marked as Active.

Contact

Deceased

AVE-53

https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K2juEAC/view

Contact/Account

Account/Contact

Account Phone

Account Phone is designed to represent the Phone number of the Physical Location eg The Household/Home Phone number, Non-Household Account the Business number. This number will be displayed on direct contacts.

Phone can be found on both the Household and the Non-Household Account.

Contact

Business Phone

Business Phone is a linked field pulling from the contacts employment record.

Account/Contact

Address

Address is a standard Salesforce field.

Contact Mailing City (State), Contact Mailing State/Province (Suburb) and Contact Mailing Country (Country) fields have a validation applied to capitalise all input values.

Australian addresses should be formatting in line with Australia Post Guidelines.

Contact Mailing City (State), Contact Mailing State/Province (Suburb) and Contact Mailing Country (Country) should all be capitalised.

Contact Mailing Country should not be printed on local mail.

Validate Address Data through Experian to gain correct Australia Post Formatting and missing values.

Account/Contact

Address Updates

https://affinaquest.zendesk.com/hc/en-us/articles/4406887628429-How-Contact-and-Account-Addresses-are-updated-

Contact

Address Preference

Address Preference creates a link between Address values.

Account Mailing is linking to the Account Address

Business Mailing

Alternate Mailing

Account/Contact

Address DPID

‘Address DPID’ is a USYD Custom field. It holds an eight digit numerical value.

This field contains the eight digit Delivery Point Identifier (DPID) associated with a unique address in Australia. The Delivery Point Identifier (DPID) is a randomly generated, unique 8-digit number, which is allocated for every new address added to the Australia Post source address database. All DPIDs for complete addresses fall within the range of 30000000 to 99999999.

A workflow is in place that if the Address is updated to a new value, the DPID will be stripped from the field to ensure it remains consistent with the address.

DPID will not be added uniquely by a user, without an on screen validation tool within salesforce, this field will be updated on mass through the validation of address data through applications like Experian.

Address data will be validated and DPID supplied to associated address by validation tools. DPID numerical value will be imported into the ‘Address DPID’ field.

Address DPID should be exported along with address fields on all print mail lists eg Direct Mail Appeals, SAM Print

Account

Account

Account Type

This is a standard Salesforce field.

All household Accounts are set as Type = Household

All non household Accounts are set as Type = Organisation

Household Accounts are for individual residences, grouping people together at one address.

Organisation Accounts are for any type of entity record that is not a residential home eg Businesses, Private or Family Foundations, Member Groups or Associations, Estate’s

Account

AVE-31 - Getting issue details... STATUS

Household Accounts should have at least 1 contact that is directly linked to it (no empty household), and should be set to Primary Contact. Second directly linked contact should be linked as Secondary Contact.

Household Accounts should NOT have any contact that is indirectly linked to it (Affiliation relationship is only for non Household)

Household Account should have the primary and secondary contact directly linked to it.

select id, AQB__AccountStatus__c,  Name, AQB__PrimaryContact__r.name, AQB__SecondaryContact__r.name  
from account where id not in (select AccountId from contact)
and RecordType.name = 'Household'
and AQB__AccountStatus__c <> 'INACTIVE'

select id, AQB__AccountStatus__c,  Name, AQB__PrimaryContact__r.name, AQB__SecondaryContact__r.name  
from account where id in (select AQB__Account__c  from AQB__Affiliation__c)
and RecordType.name = 'Household'

• 138 RECORDS EXIST and 511,825 RECORDS EXIST respectively (for secondary, maybe we leave it for now and understand why)

select id, AQB__AccountStatus__c,  Name, AQB__PrimaryContact__r.name, AQB__SecondaryContact__r.name  
from account where AQB__PrimaryContact__c not in (select id from contact)
and RecordType.name = 'Household'
and AQB__AccountStatus__c <> 'INACTIVE'

select id, AQB__AccountStatus__c,  Name, AQB__PrimaryContact__r.name, AQB__SecondaryContact__r.name  
from account where AQB__SecondaryContact__c not in (select id from contact)
and RecordType.name = 'Household'
and AQB__AccountStatus__c <> 'INACTIVE'

• 138 RECORDS EXIST

select id, AQB__AccountStatus__c,  Name, AQB__PrimaryContact__r.name, AQB__SecondaryContact__r.name  
from account where AQB__PrimaryContact__C = null
and RecordType.name = 'Household'
and AQB__AccountStatus__c <> 'INACTIVE'

Account

Account Names

Age >= 110

Mark as Deceased

Contact with Grad Year but not Alumni

go to manpreet to investigate

Schools / Faculty Mapping

N/A

Contact Mailing Address w/o City

Experian data

Contact Types

Contact Type

Secondary Contact Types

AVE-16 - Getting issue details... STATUS

See Contact Types: AVE-16 Contact Types

Rules:

1. No empty primary contact type

2. Primary contacts can only be as per the list in AVE-16

3. “Parent of Student - Alumni” cannot be primary, only secondary

4. Secondary contact type is as per the table values in Jira ticket (AVE-16 Contact Types )

5. Never have friend as secondary contact

6. If contact type is empty, then should default to Friend.

Contact Pairs

AVE-79 - Getting issue details... STATUS

• jessica.wood please attach once agreed

https://unisyd-my.sharepoint.com/:x:/g/personal/rommel_ngo_sydney_edu_au/EY7bXJjJ225DvGS0B4skWRwBJtZzPymmC2EfPmj8mwiT0Q?e=emMwmq

• rommel.ngo to convert the contact pair spreadsheet into confluence page

Industry Types

AVE-102 Industry Cleanup

• beha.parsa please attach

Address - Town

AVE-107 Address Management

Needs to be capitals

Address - State

AVE-107 Address Management

Abbreviated

Address - Postcode

AVE-107 Address Management

Address - Country

AVE-107 Address Management

Mailing Address

Address - Singapore

Generally, there are only two address formats in Singapore: one for buildings and another for landed properties:

Buildings:

[Block Number] [Street Name]
#[Unit Level]-[Unit Number] [Building Name]
Singapore [Postal Code]

101 Marlow Street
#12-05 Clife Parkview
Singapore 059020

Landed Properties

[Unit Number] [Street Name]
Singapore [Postal Code]

60 Aria Street
Singapore 602580

https://www.lingonomad.com/singapore/address-format

Generally, there are only two address formats in Singapore: one for buildings and another for landed properties:

Buildings:

[Block Number] [Street Name]
#[Unit Level]-[Unit Number] [Building Name]
Singapore [Postal Code]

101 Marlow Street
#12-05 Clife Parkview
Singapore 059020

Landed Properties

[Unit Number] [Street Name]
Singapore [Postal Code]

60 Aria Street
Singapore 602580

https://www.lingonomad.com/singapore/address-format

Student Accomodations/Emails (@uni.sydney)

At the point, contact type is moved from Graduand to Alumni, all Student Accomodation addresses/Emails (@uni.sydney) should be deactivated and deleted from the main page.

Report: https://sydneyuni-adv.lightning.force.com/lightning/r/Report/00O8v000000K3JxEAK/view?queryScope=userFolders

Account Name

Account Types

Account Type

Account Sub Type

AVE-15 - Getting issue details... STATUS

https://unisyd-my.sharepoint.com/:x:/r/personal/jessica_wood_sydney_edu_au/_layouts/15/Doc.aspx?sourcedoc=%7B3239AC92-C38E-479B-998B-C15372B0F399%7D&file=Account%20Types.xlsx&action=default&mobileredirect=true

Account Name

Account Name

Account Name Override

AVE-105 - Getting issue details... STATUS

AVE-212 - Getting issue details... STATUS

AVE-105 Household Account Name

Award

Award

https://www.sydney.edu.au/scholarships/

• beha.parsa ,
• Binila Russel - please capture the final outcome here !!

Account, Contact, Alternate Channels

Account

Mailing Address Type

Permanent Address Type

Contact

Mailing Address Type

Alternate Channels

Type

AVE-187 - Getting issue details... STATUS