Formula for Valid IF : Package selection

[Aditya_K1999]

I have 2 tables. One is Package details where all the package details are mentioned. Below are the fields in it. Any new package that is introduced has to be entered in this table first.

1. Package ID : Unique ID
2. Package Name : Text
3. Category : Enum
4. Package Cost : Price
5. Total No. of Days : Number
6. Validity : Number
7. Allowed Pet Sizes : Enumlist
8. Allowed Pet Coats : Enumlist
9. Applicable Pet Types : Enumlist
10. Require Pet Size : Y/N
11. Require Pet Coat : Y/N
12. Require Pet Type : Y/N

The other table is Packages with the below fields:

1. Pack ID : Unique ID
2. Customer ID : Ref Customer Database table
3. Pet ID : Enumlist with Base Type as Ref to Pet Details Table
4. Package Name : Ref to Package Details

Now basically I want you to help me with a formula which I can enter in the Valid If of field Package Name in the Packages tables.

I want the formula to show only those packages where pet type, pet size and pet coat of the selected pets in the dropdown matches with the packages requirement. The combination can be varied.

For example a 30 day overnight boarding package, will have TRUE for Require Pet Size and Pet Type but a False for Require pet Coat. So if the pet type is dog, and the allowed pet sizes in that package is small and medium, then that option should be visible in the packages form if the selected pets match the condition. And all the selected pets should match the condition.

[Steve]

You’ll need to add the following virtual columns to the Packages table:

• Pet Size: [Pet ID][Size]
• Pet Coat: [Pet ID][Coat]
• Pet Type: [Pet ID][Type]

Here’s your Valid If expression for Packages[Package Name]:

FILTER(
  "Packages",
  AND(
    OR(
      NOT([Require Pet Size]),
      ISBLANK([_THISROW].[Pet Size] - [Allowed Pet Sizes])
    ),
    OR(
      NOT([Require Pet Coat]),
      ISBLANK([_THISROW].[Pet Coat] - [Allowed Pet Coats])
    ),
    OR(
      NOT([Require Pet Type]),
      ISBLANK([_THISROW].[Pet Type] - [Allowed Pet Types])
    )
  )
)