Versions Compared

Version Old Version 79 New Version Current
Changes made by

albie.roets

albie.roets

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Info

Capturing centralised knowledge Centralising the knowledge from various sources (Jira, Confluence, Business Analysis, Salesforce, Business Knowledge) on a field by field basis ensuring uniformity and data quality

PLEASE COLLABORATE / PLEASE MAKE CHANGES - TEAM EFFORT !!

...

Salesforce Object

...

with the Business Rules - i.e. Address needs to follow Australia Post Standards, etc, etc, etc.

Starts with Section for Systems Logic, Contact Utilities / Reports, then field by field starting with Account and intending to capture all major fields from there on, including all the Objects and business rules / knowledge per field.

Village effort - Please collaborate and please add the knowledge that you have - feel free to make changes / add important knowledge.

Contactable Alumni Report

 Data Dictionary approve “Contactable Alumni Report” logic AlainGasquet ,
  •  Data Dictionary approve “Contactable Alumni Report” logic Miranda Adams ,
  •  Data Dictionary approve “Contactable Alumni Report” logic jessica.wood ,
  •  Data Dictionary approve “Contactable Alumni Report” logic apirami.kumaradevan ,
  •  Data Dictionary approve “Contactable Alumni Report” logic Polina Nikulina ,
 Data Dictionary approve “Account Type” logic AlainGasquet ,
  •  Data Dictionary approve “Account Type” logic Miranda Adams ,
  •  Data Dictionary approve “Account Type” logic jessica.wood ,
  •  Data Dictionary approve “Account Type” logic Monica Kluegel ,

Prospect Manager

 Data Dictionary approve “Households without a Primary/Sec contact” logic AlainGasquet ,
  •  Data Dictionary approve “Households without a Primary/Sec contact” logic Miranda Adams ,
  •  Data Dictionary approve “Households without a Primary/Sec contact” logic jessica.wood ,
  •  Data Dictionary approve “Households without a Primary/Sec contact” logic Monica Kluegel ,

Mailing Address Status

RTS Reason

Email Preference

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

Salesforce Object

Field Name/s

Contains Custom Code / Workflow

Field Description

Business Rule

Business Rules Meetings Minutes

Data Quality Rule

Business Rule Committee Approval Date

Lightning Contact Utilities

Lightning Contact Utilities - Join

Duplicate Contacts

View file
nameGuide - Join, Separate, Decease, Merge (3) (1).pdf

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

https://sydneyuni-adv.lightning.force.System Logic

Wellbeing Data

Image Added

  • A flag in Contact - Wellness Contact (USYD_wellness_contact__c) that identifies wellbeing Contact. Account associated to this contact is a wellbeing Account - this is only accessible to System Administrators.

  • The workaround to exclude Wellness Contacts is: Contact Type= Student and Secondary Contact Type = ““ AND Contact Type = Friend AND Do Not Contact = True

Image Added

System Logic

Duplicate Accounts / Contacts / Leads

Yes: TAP-1214 Jarvis Duplicate Rule Sets

TAP-1214 Jarvis Duplicate Rule Sets

  • Resolve Duplicate Accounts / Contacts

  • Can only be actioned by GA Team

Lightning Contact Utilities

Lightning Contact Utilities - SeparateJoinSeparate

Duplicate Contacts

View file
nameGuide - Join, Separate, Decease, Merge (3) (1).pdf

Lightning Contact Utilities

Lightning Contact Utilities - Decease

Deceased

View file
nameDeceasing Contacts (3) (1).docx

https

  • Can only be actioned by GA Team

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

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

Lightning Contact Utilities

Account Create

Utility to create account and contact.

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

??

Reports / Contacts

Lost

Image Removed

Lost: a contact who does not have any active modes of contact (excludes LinkedIn URL)Lightning Contact Utilities - Separate

Separate

View file
nameGuide - Join, Separate, Decease, Merge (3) (1).pdf

  • Can only be actioned by GA Team

Lightning Contact Utilities

Lightning Contact Utilities - Decease

Deceased

View file
nameDeceasing Contacts (3) (1).docx

  • Can only be actioned by GA Team

https://sydneyuni-adv.lightning.force.com/lightning/setupr/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 ,
  •  Data Dictionary approve “Lost” logic Miranda Adams ,
  •  Data Dictionary approve “Lost” logic jessica.wood ,
  •  Data Dictionary approve “Lost” logic apirami.kumaradevan,
  •  Data Dictionary approve “Lost” logic Polina Nikulina,

Reports

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

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

Account

Account

Account Name

AVE-105 Household Account Name

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)

N/A

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 Type cannot be NULL and needs to be one of the drop down items.

  • Personal = Household

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

Account

Is Active Prospect?

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

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

Account Information

The Prospect Manager for this Account.

The Prospect Manager for this Account.

Account Information

Account Name Override

Account Information

Primary Contact / Secondary Contact

This account does not have a Primary or Secondary Contact assigned.

  • Household accounts MUST always have at least a primary contact.

  • Corporations do not, only needed to have a direct contact to receipt gifts.

  • Contact can only have 1x direct linkage to account.

  • Kids on the account need to have their own account when they turn 18. (Less than 18 cannot have own account).

  • If only 1x contact and deceased, the account name gets moved to the estate. (Estate of the late …) → Harpreet does that.

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

Account Information

Do Not Contact

Account Information

Do Not Solicit

Account Information

Do Not Mail

Account Information

Account Record Type

Image Removed

Account Mailing Address

Account Mailing Address

Account Mailing Address

Mailing Address Type

Account Mailing Address

Mailing Address Status

Account Mailing Address

RTS Reason

Account Mailing Address

Phone

Account Phone = Home Phone

Mobile Phone = Individual Phone

Account Permanent Address

Account Giving Summary

Account Giving Summary

Account Giving Summary

Account Giving Summary

Contact

Name

Name

Contact

Contact Type / Secondary Contact Type

Categorisation of the contact.

AVE-16 Contact Types

Contact

Salutation

Salutation

Affinaquest Salutations Derivation

Contact

Contact Types

Contact Primary Diploma/Degree

Contact Address Preference

Contact/Account

Contact Address Preference

Address Preference

Image Removed

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”,

Jira Legacy
serverSystem JIRA
serverIde9de0c6d-9ef6-3ada-b4ce-7168c44d7e8a
keyAVE-25

Contact Address Preference

Contact Address Preference

Mailing Address Type

Contact Address Preference

Contact Phone and Email Preference

Phone Preference

Drop Down box with the values of the 4x Telephone types below.

Image Removed

Contact Phone and Email Preference

Phone

Phone is calculated field, pulling the values from the corresponding fields below. If Do Not Call box are ticked, it will not display a telephone number.

Contact Phone and Email Preference

Drop Down box with the values of the 4x email types below.

Image Removed

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.

Image Removed

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

vido1384068787.mp4

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

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

Contact Phone and Email Preference

Email

Email is calculated field, pulling the values from the corresponding fields below. If Email Opt Out box are ticked, it will not display an email address.

Same as above

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

Alternate ID’s (Nonunique Alternative id)

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

Image Removed

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

Salesforce Object

Field Name/s

Field Description

Business Rule

Data Rules (Input and Output)

Rule Violations (Jarvis Report)

Fix Instruction

Approval

Knowledge Now Uploaded?

Contact

Contact

Email / Email Preference

Jira Legacy
serverSystem JIRA
serverIde9de0c6d-9ef6-3ada-b4ce-7168c44d7e8a
keyAVE-344

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.
Image RemovedEmail 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.

Image Removed

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 ,'') ) ) )

Report/00O8v000000K2juEAC/view

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

Lightning Contact Utilities

Account Create

Utility to create account and contact.

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

??

Reports

Contactable Alumni Report

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

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

Account

Account Name

Jira Legacy
serverSystem JIRA
serverIde9de0c6d-9ef6-3ada-b4ce-7168c44d7e8a
keyAVE-105

Jira Legacy
serverSystem JIRA
serverIde9de0c6d-9ef6-3ada-b4ce-7168c44d7e8a
keyAVE-212

AVE-105 Household Account Name

Account

Account Mailing Address

Anchor
Account-Mailing-Address
Account-Mailing-Address

Rules:

  1. AVE-107 Address Management

  2. Address Formatting Business Rules

  3. 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.

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)

N/A

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

Anchor
Account-Type
Account-Type

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

Jira Legacy
serverSystem JIRA
serverIde9de0c6d-9ef6-3ada-b4ce-7168c44d7e8a
keyAVE-15

This is a standard Salesforce field.

All Household Accounts are set as Type = Household

All Non-Household Accounts are set as Type = Organisation

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

Account

Is Active Prospect?

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

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

Account Information

Prospect Manager

The Prospect Manager for this Account.

The Prospect Manager for this Account.

Account Information

Account Name Override

Account Information

Primary Contact / Secondary Contact

Anchor
Primary-Contact
Primary-Contact

This account does not have a Primary or Secondary Contact assigned.

  • Household accounts MUST always have at least a primary contact.

  • Corporations do not, only needed to have a direct contact to receipt gifts.

  • Contact can only have 1x direct linkage to account.

  • Kids on the account need to have their own account when they turn 18. (Less than 18 cannot have own account).

  • If only 1x contact and deceased, the account name gets moved to the estate. (Estate of the late …) → Harpreet does that.

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

Account Information

Do Not Contact

(AQB__DoNotContact__c)

None

Boolean Y/N

  • People who advise they do not want to be contacted;

  • People that are deemed Deceased needs to be marked DNC

  • All Wellbeing Contacts

Account Information

Do Not Solicit

Account Information

Do Not Mail

Account Information

Account Record Type

Image Added

Account Mailing Address

Account Mailing Address

Rules:

  1. AVE-107 Address Management

  2. Address Formatting Business Rules

  3. 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.

Account Mailing Address

Account Mailing Address Type

  • Jira Legacy
    serverSystem JIRA
    serverIde9de0c6d-9ef6-3ada-b4ce-7168c44d7e8a
    keyTAP-1355

Drop Down List:

Image Added

  • Jira Legacy
    serverSystem JIRA
    serverIde9de0c6d-9ef6-3ada-b4ce-7168c44d7e8a
    keyAVE-187

  • Jira Legacy
    serverSystem JIRA
    serverIde9de0c6d-9ef6-3ada-b4ce-7168c44d7e8a
    keyTAP-1355

  • Jira Legacy
    serverSystem JIRA
    serverIde9de0c6d-9ef6-3ada-b4ce-7168c44d7e8a
    keyTAP-1364
    - See ticket for development and final values

Rules:

Home Address = Default for Household Contacts (Merge “Home” + “Home Address - Unverified” + “Residential Address” to here)

Business Address = Default for Business Contacts (Merge “Business” to here)

Postal Address = Where the address is a PO Box

Internal USYD Address = Staff, etc that uses USyd Addresses

SITS Contact Address = the address is coming from SITS with a type of "Contact Address". Basically this is the address preferred by Student to communicate to them.

Other Student Address = Student Accommodation

Other - Seasonal + Vacation

  • Can only be Home Address; Business Address; Postal Address; Internal USYD Address; SITS Contact Address; Other Student Address; Other

  • When Address contains PO Box then should be Postal Address;

  • When Account Type = Household, then default to Home Address;

  • When Account Type = Household, then cannot be Business Address

  • When Account Type <> Household can only be Business Address or Postal Address

Account Mailing Address

Account Mailing Address Status

TEXT( Account.AQB__BillingStatus__c )

Picklist:

  • Active

  • Inactive = Deceased, Do Not Contact, etc.

  • Last Known

  • Needs Verification

  • Return To Sender (RTS) = Post Office Drive - leave as is

  • Partial Address = Street / State / Postcode / City is missing and needs to be fixed

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.

Picklist:

  • Active

  • Inactive = Deceased, Do Not Contact, etc.

  • Last Known

  • Needs Verification

  • Return To Sender (RTS) = Post Office Drive - leave as is

  • Partial Address = Street / State / Postcode / City is missing and needs to be fixed

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

Anchor
Address-Status
Address-Status

Account Mailing Address

RTS Reason

Account Mailing Address

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.

Account Phone = Home Phone

Mobile Phone = Mobile Phone

  • Mobile phones can only be 04 / 05

  • Landlines everything else

  • Need to be in the format +61 xxx xxx xxx

Account Permanent Address

Contact

Name

Contact

Contact Type /

Secondary Contact Type

Categorisation of the contact & contact pairs:

Alumni = Graduated with a Sydney University Award (Diploma/Undergraduate/Post Graduate) and they have an Award Date + Award Conferred (Conferred Date).

Alumni Affiliate = Graduated from one of the various institutions that Sydney Uni acquired over the years.

Graduand = Fulfilled all course requirements, and assessed as eligible, have an Award date, but Award yet to be conferred (No conferred date).

Donor = TAP-211 Definition of Donor

Staff / Former Staff = Staff / Former Staff

Organisation Contact = Organisation Contact

Friend = Associated with Sydney University through volunteering, etc.

Student = Enrolled as Student with Sydney University

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.

  7. Grateful Patient status is found in the Special Category field. It should not be in contact type because it is sensitive information

Anchor
Contact-Type
Contact-Type

Contact

Salutation

Salutation

Affinaquest Salutations Derivation

Contact

Do Not Contact

(AQB__DoNotContact__c)

None

Boolean Y/N

  • People who advise they do not want to be contacted;

  • People that are deemed Deceased needs to be marked DNC

  • All Wellbeing Contacts

Contact

Do Not Solicit

Boolean Y/N

Contact

Deceased

Lightning Contact Utilities

Lightning Contact Utilities

Image Added

Can only be done by the GA Team

View file
nameGuide - Join, Separate, Decease, Merge.pdf

View file
nameDeceasing Contacts.docx

Contact

Is Influential Alumni

Contact

Lost

Image Added

System Calculated Y/N Field:

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
)

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

https://sydneyuni-adv.lightning.force.com/lightning/

r/Report/00O8v000000K5TkEAK

setup/ObjectManager/Contact/FieldsAndRelationships/00N4a000000W1yD/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.

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 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.

Jira Legacy
serverSystem JIRA
serverIde9de0c6d-9ef6-3ada-b4ce-7168c44d7e8a
keyAVE-187
- 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

Contact

Mailing Address Status

AVE-177

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

For manual

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).

Contact: Primary Diploma/Degree

Contact: Address Preference

Contact Mailing Address

Rules:

  1. AVE-107 Address Management

  2. Address Formatting Business Rules

  3. 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.

Contact: Address Preference

Address Preference

Image Added

Jira Legacy
serverSystem JIRA
serverIde9de0c6d-9ef6-3ada-b4ce-7168c44d7e8a
keyTAP-1018

A read only field used to indicate what type of address the contact current mailing address is associated.

Pick List:

  • Account Mailing

  • Business

  • Alternate

Rules:

  • Account Type = Household → Auto Defaulted to ‘Account Mailing’.

  • Account Type = Corporation → Business

  • If Address Blank then <Blank>

  • Jira Legacy
    serverSystem JIRA
    serverIde9de0c6d-9ef6-3ada-b4ce-7168c44d7e8a
    keyAVE-25

  • <> Isnull

  • When Account Type = Household = Account Mailing

  • When Account Type <> Household = Business

Anchor
Address-Preference
Address-Preference
&

Contact: Address Preference

Mailing Address Status

Picklist:

  • Active

  • Inactive

  • Last Known

  • Needs Verification

  • Return To Sender (RTS)

  • Partial Address

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

Image Removed

View file
nameGuide - Join, Separate, Decease, Merge.pdf
View filenameDeceasing Contacts.docx

Picklist:

  • Active

  • Inactive = Deceased, Do Not Contact, etc.

  • Last Known

  • Needs Verification

  • Return To Sender (RTS) = Post Office Drive - leave as is

  • Partial Address = Street / State / Postcode / City is missing and needs to be fixed

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

00O8v000000K2juEAC

00O8v000000K2kJEAS/view

View file
nameDeceasing Contacts.docx

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

Estate’sAccount

Contact: Address Preference

Mailing Address Type

Drop Down List:

Image Added

  • Jira Legacy
    serverSystem JIRA
    serverIde9de0c6d-9ef6-3ada-b4ce-7168c44d7e8a
    keyAVE-187

  • Jira Legacy
    serverSystem JIRA
    serverIde9de0c6d-9ef6-3ada-b4ce-7168c44d7e8a
    keyTAP-1355

  • Jira Legacy
    serverSystem JIRA
    serverIde9de0c6d-9ef6-3ada-b4ce-7168c44d7e8a
    keyTAP-1364
    - See ticket for development and final values

Rules:

Home Address = Default for Household Contacts (Merge “Home” + “Home Address - Unverified” + “Residential Address” to here)

Business Address = Default for Business Contacts (Merge “Business” to here)

Postal Address = Where the address is a PO Box

Internal USYD Address = Staff, etc that uses USyd Addresses

SITS Contact Address = the address is coming from SITS with a type of "Contact Address". Basically this is the address preferred by Student to communicate to them.

Other Student Address = Student Accommodation

Other - Seasonal + Vacation

  • Can only be Home Address; Business Address; Postal Address; Internal USYD Address; SITS Contact Address; Other Student Address; Other

  • When Address contains PO Box then should be Postal Address;

  • When Account Type = Household, then default to Home Address;

  • When Account Type = Household, then cannot be Business Address

  • When Account Type <> Household can only be Business Address or Postal Address

Anchor
Address-Type
Address-Type

Contact: Address Preference

RTS Reason

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 = OrganisationHousehold 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,

Contact: Phone and Email Preference

Phone Preference

Managed field that populates Phone field

AVE-106 Phone Number and Email integrity

Drop Down box with the values of the 4x Telephone types below.

Image Added

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

If Do Not Call box are ticked, it will not display a Preferred Telephone Number.

AVE-106 Phone Number and Email integrity

Business Rule:

  • Jira Legacy
    serverSystem JIRA
    serverIde9de0c6d-9ef6-3ada-b4ce-7168c44d7e8a
    key

AVE

  • TAP-

31

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.

Code Block
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'

Code Block
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'

  • Status
    colourRed
    title138 RECORDS EXIST
    and
    Status
    colourYellow
    title511,825 RECORDS EXIST
    respectively (for secondary, maybe we leave it for now and understand why)

Code Block
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'

Code Block
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'

  • Status
    colourRed
    title138 RECORDS EXIST

Code Block
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

View file
nameSchools - Faculty Mapping.xlsx

Contact Mailing Address w/o City

Experian data

Contact Types

Contact Type

Secondary Contact Types

Jira Legacy
serverSystem JIRA
serverIde9de0c6d-9ef6-3ada-b4ce-7168c44d7e8a
keyAVE-16

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

Jira Legacy
serverSystem JIRA
serverIde9de0c6d-9ef6-3ada-b4ce-7168c44d7e8a
keyAVE-79

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

Address - Town

AVE-107 Address Management

Needs to be capitals

View file
namestandard_postcode_file_pc001_14092022.csv

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:

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

101 Marlow Street
#12-05 Clife Parkview
Singapore 059020

Landed Properties

Code Block
[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:

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

101 Marlow Street
#12-05 Clife Parkview
Singapore 059020

Landed Properties

Code Block
[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:

  • 860

  • Phone preference be defaulted to “Mobile” (because iMod leads are coming in with Mobile numbers)

  • Email preference defaulted to “Personal Email”.

Contact: Phone and Email Preference

Phone

AVE-106 Phone Number and Email integrity

Phone is calculated field, pulling the values from the corresponding fields below. If Do Not Call box are ticked, it will not display a telephone number.

Image Added

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

Phone is a standard Salesforce field.

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

If Do Not Call box are ticked, it will not display a Preferred Telephone Number.

AVE-106 Phone Number and Email integrity

Contact: Phone and Email Preference

Account Phone

No

AVE-106 Phone Number and Email integrity

Phone at Account Mailing Address

Business Rules Meetings Minutes :

  • Australian Land Lines

Contact: Phone and Email Preference

Business Phone

No

AVE-106 Phone Number and Email integrity

Phone at Contacts Primary Business Location

  • Phone at Contacts Primary Business Location

Contact: Phone and Email Preference

Mobile Phone

No

AVE-106 Phone Number and Email integrity

Business Rules Meetings Minutes :

  • Australian Mobile Phones

Within Australia, mobile phone numbers begin with 04 or 05 – the Australian national trunk code 0, plus the mobile indicator 4 or 5 – followed by eight digits. This is generally written as 04XX XXX XXX within Australia, or as +61 4XX XXX XXX for an international audience

  • Length => 10

  • Starts with “+” or “04” OR “05”

    • Look for “61” to add “+”

    • Look for “4” / “5” to add “0”

  • Not Like 12345678

  • Not like 000 000 000

  • Not Like 999 999 999

Anchor
Mobile-Phone
Mobile-Phone

Contact: Phone and Email Preference

Fax

No

AVE-106 Phone Number and Email integrity

Contact: Phone and Email Preference

Email Preference

Managed field that populates Email field

AVE-106 Phone Number and Email integrity

Managed Picklist: values of the 4x email types below.

Image Added

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.

Business Rule:

  • Jira Legacy
    serverSystem JIRA
    serverIde9de0c6d-9ef6-3ada-b4ce-7168c44d7e8a
    keyTAP-860

  • Phone preference be defaulted to “Mobile” (because iMod leads are coming in with Mobile numbers)

  • Email preference defaulted to “Personal Email”.

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

Anchor
Email-Preference
Email-Preference

Contact: Phone and Email Preference

Email

AVE-106 Phone Number and Email integrity

Email is calculated field, pulling the values from the corresponding fields below. If Email Opt Out box are ticked, it will not display an email address.

Image Added

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.

Image Added

Rules:

  1. Bounced emails needs to be removed from all fields, including alternate channels.

  2. USyd deactivates student emails after graduation - remove Graduant / Alumni student emails

00O8v000000K3JxEAK
?queryScope=userFolders

Account Name

Account Types

Account Type

Account Sub Type

Jira Legacy
serverSystem JIRA
serverIde9de0c6d-9ef6-3ada-b4ce-7168c44d7e8a
keyAVE-15

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

Jira Legacy
serverSystem JIRA
serverIde9de0c6d-9ef6-3ada-b4ce-7168c44d7e8a
keyAVE-105

Jira Legacy
serverSystem JIRA
serverIde9de0c6d-9ef6-3ada-b4ce-7168c44d7e8a
keyAVE-212

AVE-105 Household Account Name

Award

Award

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

Account, Contact, Alternate Channels

Account

Mailing Address Type

Permanent Address Type

Contact

Mailing Address Type

Alternate Channels

Type

Jira LegacyserverSystem JIRAserverIde9de0c6d-9ef6-3ada-b4ce-7168c44d7e8akeyAVE-187

Anchor
Email
Email

Contact: Phone and Email Preference

Business Email

Managed Field - Email at contact's primary place of employment - AQB__BusinessEmail__c

AVE-106 Phone Number and Email integrity

Email at contact's primary place of employment

Contact: Phone and Email Preference

Preferred Email

Captures Email if either or both Do Not Email and Do Not Contact are unchecked:

IF(OR(HasOptedOutOfEmail = TRUE,AQB__DoNotContact__c= TRUE) ,"",USYD_Best_Contact_Email__c)

AVE-106 Phone Number and Email integrity

Alternate ID’s

Alternate ID’s (Nonunique Alternative id)

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

Image Added

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

Award Recipients

Award

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

Education

Schools / Faculty Mapping

View file
nameSchools - Faculty Mapping.xlsx

Employment

Industry

AVE-102 Industry Cleanup

Gift Recognition Credits

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

Anchor
Address-Preference
Address-Preference

Address Formatting Business Rules