Difficulty Filtering and Preventing Duplicate Entries

[Danny_Reyes]

Hello everyone!

I am facing an issue with an application I am developing in AppSheet. In my application, I need to display a list of [ID Nivel] for users to select one and record an expected result for a specific [Período meta]. However, I want to ensure that the list of [ID Nivel] only displays available levels and prevents duplicate records for the same [Período meta] and [ID Categoría].

To achieve this, I have been experimenting with different formulas in the [ID Nivel] field, which is an Enum field displaying the list of available levels. My objectives are as follows:

1. Display all available level [ID Nivel] for the selected category [ID Categoría] registered in IND_nivelesEvaluación.
2. Prevent levels that have already been registered in IND_metasPeriodo_Nivel for the same [Período meta] and [ID Categoría] from being displayed.
3. Allow the editing of existing records and the registration of new expected results.

The expression I am currently using in the [ID Nivel] field is as follows:

SELECT(
  IND_nivelesEvaluación[ID Nivel],
  AND(
    [ID Categoría] = [_THISROW].[ID Categoría],
    OR(
      ISNOTBLANK([_THISROW].[ID Nivel]), 
      [_THISROW].[Id registro] = [_THISROW-1].[Id registro],
      NOT(
        IN(
          [_THISROW].[ID Nivel], 
          SELECT(
            IND_metasPeriodo_Nivel[ID Nivel], 
            AND(
              [Período meta] = [_THISROW].[Período meta], 
              [ID Categoría] = [_THISROW].[ID Categoría]
            )
          )
        )
      )
    )
  )
)

While this expression allows the editing of previously saved records, it still has an issue: it continues to display the list of levels [ID Nivel] regardless of whether they have already been registered for the same category and meta-period. This allows selecting a level that already exists in the IND_metasPeriodo_Nivel table for the same [Período meta] and [ID Categoría].

I would greatly appreciate it if someone in the community could provide guidance or an appropriate formula to help me address this issue. Your assistance will be much appreciated.

Thank you!