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!

[Suvrutt_Gurjar]

Could you mention where are you using this expression and table column screenshot? 

Based on understanding so far, you may wish to try a valid_if  expression  as follows in the [ID Nivel] column.

IND_nivelesEvaluación[ID Nivel] -
SELECT(IND_nivelesEvaluación[ID Nivel]
              AND(
                        [Período meta] = [_THISROW].[Período meta],
                        [ID Categoría] = [_THISROW].[ID Categoría],
                        [Key column] <>[_THISROW].[Key Column]
                      )

                )

Please substitute [Key Column] with the name of the key column pf the table where the [ID Nivel] column resides. I believe you are having  two  [ID Nivel] columns. One in IND_nivelesEvaluación table and the other in other table where you are using the above valid_if. So in the expression, please use the the other table's key and not key of IND_nivelesEvaluación.

Edit: Made some change in description.

[Danny_Reyes]

I appreciate your assistance. To provide context, the application's purpose is to monitor and evaluate a set of indicators, which are further divided into categories ("ID Categorías") (which may or may not have levels). Goals are planned per period ("Periodo meta") for each indicator, and the progress is later assessed and recorded. In other words, an indicator has a fixed number of categories ("ID Categorías") registered during the initial phase. Then, each category has a fixed number of levels ("ID Nivel") also registered at the beginning of the planning process. Subsequently, for each period ("Periodo meta") for each indicator, the category ("ID Categoría") is planned, and if applicable, the level ("ID Nivel").

Now, I would like to clarify the following points:

1. The expression is being used in the "IND_metasPeriodo_Nivel" table, in the "ID Nivel" column. In this table, periodic goals ("Periodo meta") are recorded for each level ("ID Nivel") corresponding to each category ("ID Categoría").

1. Some categories ("ID Categoría") have a group of levels ("ID Nivel") for which goals need to be planned and recorded. These levels ("ID Nivel") are registered in the "IND_nivelesEvaluación" table. They are referenced whenever we need to plan a goal for a specific period ("Periodo meta").

2. The component you proposed, "[Key column] <> [_THISROW].[Key Column]," will always be FALSE because the "IND_nivelesEvaluación" table is a table where the list of "ID Nivel" values is stored, meaning it's a reference table. On the other hand, the "IND_metasPeriodo_Nivel" table is where the goals for the level are registered. Therefore, the [Key column] values of both tables are not comparable.

In this context, the goal is to ensure that the "ID Nivel" values are displayed in the "IND_metasPeriodo_Nivel" table only if they meet the following conditions:

1. They belong to the selected category ("ID Categoría").
2. They are not registered for the same period ("Periodo meta") in the "IND_metasPeriodo_Nivel" table.

The current solution provided does not address these conditions correctly. Could you please provide further guidance or an alternative solution to achieve this specific behavior?

[Suvrutt_Gurjar]

Thank you for the details.

Since the expression is in "IND_metasPeriodo_Nivel" table, please try the key column name of that table in the expression.

So something like below. Both sides the key column is from "IND_metasPeriodo_Nivel" table.

IND_nivelesEvaluación[ID Nivel] -
SELECT(IND_nivelesEvaluación[ID Nivel]
              AND(
                        [Período meta] = [_THISROW].[Período meta],
                        [ID Categoría] = [_THISROW].[ID Categoría],
                        [Key column of ND_metasPeriodo_Nivel table] <>[_THISROW].[Key Column of ND_metasPeriodo_Nivel table]
                      )

                )

[Danny_Reyes]

The table IND_nivelesEvaluación does not have [Período meta] nor [Key column of ND_metasPeriodo_Nivel table]. As I mentioned before IND_nivelesEvaluación table is a reference one that only storage a list of levels [ID Nivel] for the categories [ID Categoría] that applies.

Here you have the  IND_nivelesEvaluación table structure:

On the other hand, IND_metasPeriodo_Nivel table where I am trying to generate the levels list (ID Nivel) following the above-mentioned criteria whic are:

1. Display all available level [ID Nivel] for the selected category [ID Categoría] registered in IND_nivelesEvaluación.
2. Exclude from the levels list and not display levels [ID Nivel] that have already been registered before in IND_metasPeriodo_Nivel for the same [Período meta] and [ID Categoría].
3. Enable the modification of existing records within IND_metasPeriodo_Nivel for a specific [ID Nivel]' and [Período meta], as well as the registration of new records comply with the previous criterion (1 and 2).
   
   

Here you have the  IND_metasPeriodo_Nivel  table structure:

Bellow you can see the reference to IND_nivelesEvaluación table:

[Suvrutt_Gurjar · Accepted solution]

Oh okay. I believe the details are good now . Could you please try below?

SELECT(IND_nivelesEvaluación[ID Nivel],  [ID Categoría] = [_THISROW].[ID Categoría]) -
SELECT(IND_metasPeriodo_Nivel[ID Nivel]
              AND(
                        [Período meta] = [_THISROW].[Período meta],
                        [ID Categoría] = [_THISROW].[ID Categoría],
                        [Id Registro] <>[_THISROW].[ID Registro]

                     )

              )

If this does not work, I suggest that we will resume tomorrow, because I am sorry that I need to move away from my work desk now.

[Suvrutt_Gurjar]

@Danny_Reyes ,

Were you able to solve or test it?

[Danny_Reyes]

@Suvrutt_Gurjar it is working super fine. 
Thanks a lot for your assistance.

[Suvrutt_Gurjar]

You are welcome. Good to know it works the way you wish. Thank you for the update.