Duplicate Entry ENUM Field

[Kumarin_Rajoo]

Hi,

I have 2 tables.
The 1st table has a list of:
UniqueID
Date
Name (Building Name)
Stage (Floor, Roof, etc)
Task (Description of work)

The 2nd table is a form with:
UniqueID
Date
Name (Building Name)
Stage (Floor, Roof, etc)
Task (Description of work)

The form (table 2) has a ENUM field for Name, Stage & Task.
The formula used for the valid_If:
Field Name: (Table1[Building Name]
Field Stage: (SELECT(Table1[Stage],[Building Name]=[_THISROW].[Building Name])
Field Task: (SELECT(Table1[Task], AND ([Building Name]=[_THISROW].[Building Name], [Stage]=[_THISROW].[Stage] ) )

The issue I am having is that this is allowing users to create duplicate entries of the same task for the same building and stage. How do I prevent this because this has multiple criteria to take into account.

Thanks in advance.

[Steve · Accepted solution]

Try this as the Valid If for the Task column in Table2:

(
  SELECT(
    Table1[Task],
    AND(
      ([Building Name] = [_THISROW].[Building Name]),
      ([Stage] = [_THISROW].[Stage])
    )
  )
  - SELECT(
    Table2[Task],
    AND(
      ([Building Name] = [_THISROW].[Building Name]),
      ([Stage] = [_THISROW].[Stage]),
      ([_ROWNUMBER] <> [_THISROW].[_ROWNUMBER])
    )
  )
)

Note that the second SELECT() references Table2 where the first references Table1.

The first SELECT() gathers all the possible Task options for the given Building Name, Stage combination. The second SELECT() then removes the Task values that are already in use.

See also:

help.appsheet.com

List Subtraction

Subtract lists to produce another containing only the unique items of the first list that aren't also in the second.

[Kumarin_Rajoo]

Steve:

(
  SELECT(
    Table1[Task],
    AND(
      ([Building Name] = [_THISROW].[Building Name]),
      ([Stage] = [_THISROW].[Stage])
    )
  )
  - SELECT(
    Table2[Task],
    AND(
      ([Building Name] = [_THISROW].[Building Name]),
      ([Stage] = [_THISROW].[Stage]),
      ([_ROWNUMBER] <> [_THISROW].[_ROWNUMBER])
    )
  )
)

Great, you the man. Works exactly as I need. Thanks again.