Preferred email or phone

[tcb]

I would like to have a rule at the table level or set primary keys that
would allow only one preferred phone number and one preferred email
address.

Persons are in a parent table: tbl_Person.

Phone numbers and emails are in the child table tch_Phone_Email as
below:

PE_EMPLID PE_PorE PE_Type_k PE_Phone_Email PE_PreferredYN
0282634 P Cell 555-555-555 0
0282634 E Email1 McGilliguddy@somewhere 0
0282634 E WEmail McGilliguddy@elsewhere -1
0282634 P Work 444-444-4444 0
0282634 P Current 222-222-2222 0
0282634 P Home 333-333-333 -1

 

[Allen Browne]

It is possible to use the BeforeUpdate event of the form where these entries
are made to test if the Preferred fields changed value, and if they were
turned on, to either Cancel the event or execute an Append query statement
to turn off the Preferred fields in all other records of this type for this
person so there cannot be a duplication.

IMHO, it's not worth the effort. Consider adding a Priority field (type
Number), so the user can specify a priority for each contact type (1 =
highest priority, 2 = next, ...) If 2 phone numbers have equal priority,
that's no drama. If they don't you can easily pick the preferred one. And if
you need to pick the preferred subtype (e.g. it must not be a mobile phone),
you can still pick the preferred one with this kind of priority system.

 

[tcb]

Thanks. Your opinion is always well appreciated up here in Minneapolis
(when I do searches for answers in this forum I include "Browne" in the
string). I think I'll go with the priority scheme or variation
thereof. I'll need something similar for address and something at the
form level to make sure a person always has at least one person type
(student, faculty, alumni, work supervisor, etc).