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:
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!
Solved! Go to 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.
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.
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:
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").
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").
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:
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?
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]
)
)
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:
Here you have the IND_metasPeriodo_Nivel table structure:
Bellow you can see the reference to IND_nivelesEvaluaciรณn table:
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 it is working super fine.
Thanks a lot for your assistance.
You are welcome. Good to know it works the way you wish. Thank you for the update.
User | Count |
---|---|
35 | |
31 | |
30 | |
18 | |
17 |