Proactively enforcing validation constraints on value populated from linked sheet

I have two sheets, one is a table (“phone”) of phone numbers available to be assigned to a group with two fields:

  1. phoneNumber
  2. phoneType (e.g. typeA, typeB, typeC, etc.)

I have another sheet where a user assigns a phone number to a group. The group sheet has these fields:

  1. groupName (e.g. groupA, groupB, groupC)
  2. phoneNumber

I have linked the sheets and allow editing of the group.phoneNumber field to be populated with a selection from the phone.phoneNumber field.

My struggle is proactively enforcing two constraints:

  1. each group should have a unique phone number
  2. all phone numbers should be phone.phoneType = “typeB”.

Right now, I can enforce those constraints only when the user hits save. How can I narrow down the group.phoneNumber selection list to proactively only list available phone numbers of type B?

Thanks!!

Hi,

If the group.phone number field have link & load configured, or is a select from other sheet field. You could try Dynamic Filtering, add a new field in the group sheet and assign default value as typeB (If this field shouldn’t be viewed, it can be hided). And navigate to your form page’s design mode, and from Form Tools, click on the gear icon next to the related sheet name under Link & Load. This will show you the setting to create a dynamic filter.

Thanks for the reply. This suggestion can handle the type B case, but the dynamic filter does not restrict the phone numbers that can be selected on the group sheet to only those phone numbers that have not been selected yet. Might there be a way handle this uniqueness constraint when entering a new group?

Sorry that I miss this, there’s a workaround to restrict.

  1. Add a new field “validity” on both two sheets to tell if the phone number is available.

  2. Set a default value to the field validity in sheet group, which might be “unavailable”

  3. Use Update Value on Another Sheet to create a action button to update the field value “unavailable” to the field added step 1 on the sheet phone, only when the value of field phone number on group sheet equals to the value of field phone number on sheet phone. And choose Automatically execute when saving in advanced setting.

  4. Setting Dynamic Filtering, add another condition to filter out entries that the phone numbers are unavailable, which the condition should be the value of field validity on the other sheet = $EMPTY_VALUE.

1 Like