Hello everyone,
I have 2 tables
HOME is Parent (key= [CPR])
INFOPREX is child
I want to get the maximum value in each โLOCALIZACAOโ.
My formula is (there are more conditions but letโs go to the main one):
IFS(USERSETTINGS('Numero de Meses Anรกlise')=6, MAX( LIST(ANY(SELECT([Related INFOPREXs][V_0],[LOCALIZACAO]='Loja 1')), ANY(SELECT([Related INFOPREXs][V_1],[LOCALIZACAO]='Loja 1')), ANY(SELECT([Related INFOPREXs][V_2],[LOCALIZACAO]='Loja 1')), ANY(SELECT([Related INFOPREXs][V_3],[LOCALIZACAO]='Loja 1')), ANY(SELECT([Related INFOPREXs][V_4],[LOCALIZACAO]='Loja 1')), ANY(SELECT([Related INFOPREXs][V_5],[LOCALIZACAO]='Loja 1')) ) ) )
The problem is that the values displayed after each synchronization are different.
Iโd appreciate some help figuring out what Iโm doing wrong.
Thanks
Solved! Go to Solution.
I cannot explain why youโre seeing different numbers each time the app syncs. That is very odd behavior.
My interpretation of the expression sample you provided is that you are attempting to find the highest V_ column valueโwhere each V_ column corresponds to a 1-month periodโfor the Loja 1 location over a number of months determined by the Numero de Meses Anรกlise user setting (6 in the sample). Based on my interpretation, I propose an alternative approach:
Add a virtual column (Iโll call it MAX_V) to the INFOPREX table to compute the maximum of the first so-many V_ column valuesโas determined by the Numero de Meses Anรกlise user settingโfor its row:
MAX(
TOP(
LIST([V_0], [V_1], ..., [V_15]),
USERSETTINGS('Numero de Meses Anรกlise')
)
)
Replace [V_0], [V_1], ..., [V_15]
with the complete list of V_ column references for the row.
TOP(..., USERSETTINGS('Numero de Meses Anรกlise'))
reduces the list of all V_ column values to only the first so-many determined by the user setting.
MAX(...)
then finds the highest of the first so-many of rowโs V_ column values.
Replace your IFS(USERSETTINGS(...)=6, MAX(SELECT(...)))
expression with the following:
MAX(SELECT([Related INFOPREXs][MAX_V], ('Loja 1' = [LOCALIZACAO]))
Note how Iโve expressed ('Loja 1' = [LOCALIZACAO])
. The is-equal-to operator (=
) has the non-intuitive behavior that it will evaluate as TRUE if the left-side value is blank, regardless of the right-side value. If [LOCALIZACAO]
on the left side is ever blank, you might get unexpected results. Putting a value that is guaranteed to be non-blank on the left side avoids this behavior.
With these expressions, you wonโt need separate expressions to handle each different value of the Numero de Meses Anรกlise user setting.
If you arenโt interested in trying this alternative, we can instead try to troubleshoot the odd behavior your app is experiencing.
You cannot filter Related INFOPREX like that. Replace
by INFOPREX (the name of the child table)
Thanks @Bellave_Jayaram for the reply but with this change always returns 0.
In your suggestion the expression does not take into account the key of the row for which I want the expression. I probably need an โANDโ statement, or am i wrong?
I still have some difficulties turning Portuguese into AppShettโese.
I will keep trying.
EDIT: In the image there are only zeros, but in other rows there are values, which I want to find the maximum value for each Loja(1,2 or 3) of the INFOPREX table, in each row of the HOME table.
Your use of [Related INFOPREXs]
(e.g., [Related INFOPREXs][V1]
) is valid.
I cannot explain why youโre seeing different numbers each time the app syncs. That is very odd behavior.
My interpretation of the expression sample you provided is that you are attempting to find the highest V_ column valueโwhere each V_ column corresponds to a 1-month periodโfor the Loja 1 location over a number of months determined by the Numero de Meses Anรกlise user setting (6 in the sample). Based on my interpretation, I propose an alternative approach:
Add a virtual column (Iโll call it MAX_V) to the INFOPREX table to compute the maximum of the first so-many V_ column valuesโas determined by the Numero de Meses Anรกlise user settingโfor its row:
MAX(
TOP(
LIST([V_0], [V_1], ..., [V_15]),
USERSETTINGS('Numero de Meses Anรกlise')
)
)
Replace [V_0], [V_1], ..., [V_15]
with the complete list of V_ column references for the row.
TOP(..., USERSETTINGS('Numero de Meses Anรกlise'))
reduces the list of all V_ column values to only the first so-many determined by the user setting.
MAX(...)
then finds the highest of the first so-many of rowโs V_ column values.
Replace your IFS(USERSETTINGS(...)=6, MAX(SELECT(...)))
expression with the following:
MAX(SELECT([Related INFOPREXs][MAX_V], ('Loja 1' = [LOCALIZACAO]))
Note how Iโve expressed ('Loja 1' = [LOCALIZACAO])
. The is-equal-to operator (=
) has the non-intuitive behavior that it will evaluate as TRUE if the left-side value is blank, regardless of the right-side value. If [LOCALIZACAO]
on the left side is ever blank, you might get unexpected results. Putting a value that is guaranteed to be non-blank on the left side avoids this behavior.
With these expressions, you wonโt need separate expressions to handle each different value of the Numero de Meses Anรกlise user setting.
If you arenโt interested in trying this alternative, we can instead try to troubleshoot the odd behavior your app is experiencing.
Thank you very much @Steve you are the Man.
I was trying to avoid putting VCโs in the INFOPREX table because itโs a table with 52300 rows x 31 columns. I thought it would have a lot of impact on synchronization, but it didnโt.
I learned a lot from your answer. Thank you very much.
Good Morning @Steve.
The behavior I mentioned yesterday about the formulas returning different values โโon each sync reappeared after I added some more VCโs based on your solution.
What seems to me to happen is that AppSheet does not calculate all rows. When I go into detail view and activate an action that is not related to calculations but sets a value in the table, AppSheet does the calculation.
Is there any way to force AppSheet to do all calculations before displaying views?
After several attempts and experiments, I realized that replacing:
(That for me was the best solution), with:
MAX(SELECT(INFOPREX[MAX_V],AND( [CPR]=[_THISROW].[CPR],('Loja 1' = [LOCALIZACAO]))))
as well as all other VCโs where I used [Related INFOPREXs], the app works well.
So there seems to be a sync issue when we use [Related TABLEs] with a lot of data. I do not know if that is, but my change solved the problem.
Thankโs by all help.
User | Count |
---|---|
36 | |
33 | |
27 | |
23 | |
18 |