I know there have been other threads about this topic, but they either confuse me or don't work in my case.
I have a table in excel where there are 10 rows for every month, and I have set up a grouping system. User can for ex. set up group 1 for January, February, March and then set up group 2 to contain every other month. In this case the app needs to assign 30 rows of January, February and March to group 1 and the remaining 90 to group 2. There can be a maximum of 12 groups.
The problem arises when I need to get those numbers assigned to the Table. I have a Month and Index column. Index should have the number of the group the specfic month is assigned to but simply isn't changing.
Here are the commands I tried and assigned to a button:
ForAll(Filter(Table, Month = "January"), Patch(Table, LookUp(Table, Month = "January"), {Index: Value(variable)})) Collect(Jan, Filter(Table, Month = "January")); UpdateIf(Jan, true, {Index: Value(variable)}); Patch(Table,Jan)
variable here is set by the user when they select a group to modify. That way the button knows which group should be edited.
These commands are a part of a bigger command chain, which looks something like this:
If(Toggle1.Value = true && Checkbox1.= true, ForAll(Filter(Table, Month = "January"), Patch(Table, LookUp(Table, Month = "January"), {Index: Value(variable)})), Set(dummy, false)); If(Toggle1.Value = true && Checkbox2.= true, ForAll(Filter(Table, Month = "February"), Patch(Table, LookUp(Table, Month = "February"), {Index: Value(variable)})), Set(dummy, false)); ...
Toggle1 checks if the group should be active, Checkbox1 checks if the selected month should be a part of this group and dummy is just there to skip the else value. I don't want the app to do anything if the conditions aren't met. There would be 12 commands corresponding to every month.
I still don't fully understand how ForAll and Collect work. It certainly doesn't help that in ForAll I need to reference a table three times and the first one for some reason has to be different and Collect creates an empty collection even through I made sure that the condition was written correctly.
So, how do I put these group numbers to assigned months?
Solved! Go to Solution.
Hi @Macjan ,
Based on the ForAll formula you provided, I think there is something wrong with it. Based on the needs that you mentioned, I think it is not necessary to achieve your needs using ForAll function, the UpdateIf function is enough.
Please consider modify your formula as below (the formulas assigned to a button):
UpdateIf( /* <-- Modify the Index column of all avaiable table records whose Month value is "January" with variable value*/
Table,
Month = "January",
{
Index: Value(variable)
}
)
Please modify your second part formula as below:
If( /* <-- Only Single One If function could achieve your needs */
Toggle1.Value = true && Checkbox1.= true,
Update(Table, Month = "January", {Index: Value(variable)}), Toggle1.Value = true && Checkbox2.= true,
Update(Table, Month = "February", {Index: Value(variable)}),
....
....,
Set(dummy, false)
)
More details about the ForAll function, If function in PowerApps, please check the following article:
Best regards,
Hi @Macjan ,
Based on the ForAll formula you provided, I think there is something wrong with it. Based on the needs that you mentioned, I think it is not necessary to achieve your needs using ForAll function, the UpdateIf function is enough.
Please consider modify your formula as below (the formulas assigned to a button):
UpdateIf( /* <-- Modify the Index column of all avaiable table records whose Month value is "January" with variable value*/
Table,
Month = "January",
{
Index: Value(variable)
}
)
Please modify your second part formula as below:
If( /* <-- Only Single One If function could achieve your needs */
Toggle1.Value = true && Checkbox1.= true,
Update(Table, Month = "January", {Index: Value(variable)}), Toggle1.Value = true && Checkbox2.= true,
Update(Table, Month = "February", {Index: Value(variable)}),
....
....,
Set(dummy, false)
)
More details about the ForAll function, If function in PowerApps, please check the following article:
Best regards,
As far as I remember I tried the UpdateIf command as well, but the table wasn't updating. However I decided to recreate the same table in new excel file and now everything works like a charm.
Thank you for the advice as well as for the method of slightly compressing the If command.
User | Count |
---|---|
140 | |
140 | |
76 | |
73 | |
71 |
User | Count |
---|---|
230 | |
174 | |
73 | |
69 | |
61 |