Nested If statements and Distinct

[MGeale]

Hey All,

I think I'm being a bit of an idiot here and the answer is staring at me straight in the face, but hoping someone could provide a bit of assistance. I have some code that parses a JSON string and the eventual output is a bunch of users who don't have anything assigned to them (when pressing button 1 and button 2). 

The following is in button 1:

Clear(Test);
        ForAll(
            colDepartments,
            With(
                {
                    wStringToFind: Char(34) & Left(
                        Substitute(
                            Title,
                            " ",
                            "_x0020_"
                        ),
                        32
                    ) & Char(34) & ":false"
                },
                ForAll(
                    wSplitJSON As wRecords,
                    If(
                        wStringToFind in wRecords.Value,
                        Collect(
                            Test,
                            {
                                a: wRecords,
                                b: Substitute(
                                    Last(
                                        ForAll(
                                            Split(
                                                Match(
                                                    Substitute(
                                                        wRecords.Value,
                                                        Char(34),
                                                        ""
                                                    ),
                                                    "(,Title:.+?,)"
                                                ).FullMatch,
                                                ":"
                                            ),
                                            {Result: ThisRecord.Value}
                                        ).Result
                                    ).Result,
                                    ",",
                                    ""
                                )
                            }
                        )
                    )
                )
            )
        )

The following in Button 2:

Clear(MyTest);
ForAll(
    Distinct(
        Test,
        b
    ) As uniqueUser,
    If(
        CountRows(
            Filter(
                Test,
                b = uniqueUser.Value
            )
        ) = CountRows(colDepartments),
        Collect(
            MyTest,
            {a: uniqueUser.Value}
        )
    )
)

I want to put the logic of Button 2, into Button 1 so its just a simple one click button.

If any one has any ideas, it would be grately appreicated. Let me know if you have any questions or if I can provide any further clarification.

Thanks in advance!

[poweractivate · Accepted solution]

@MGeale 

To consolidate both actions into one button click, you can incorporate the logic of Button 2 into the Button 1. 

This could involve adding the Button 2 logic into the end of the ForAll of the Button 1 logic.

Please note that this is a high-level guidance and the actual implementation might need some tweaking according to your full JSON structure and business logic. 

For now here is the combined formula you could try:

Clear(Test);
Clear(MyTest);

ForAll(
colDepartments,
With(
{
wStringToFind: Char(34) & Left(
Substitute(
Title,
" ",
"_x0020_"
),
32
) & Char(34) & ":false"
},
ForAll(
Distinct(
wSplitJSON,
Value
) As wRecords,
If(
wStringToFind in wRecords.Value,
Collect(
Test,
{
a: wRecords,
b: Substitute(
Last(
ForAll(
Split(
Match(
Substitute(
wRecords.Value,
Char(34),
""
),
"(,Title:.+?,)"
).FullMatch,
":"
),
{Result: ThisRecord.Value}
).Result
).Result,
",",
""
)
}
)
)
)
)
);

ForAll(
Distinct(
Test,
b
) As uniqueUser,
If(
CountRows(
Filter(
Test,
b = uniqueUser.Value
)
) = CountRows(colDepartments),
Collect(
MyTest,
{a: uniqueUser.Value}
)
)
);

This should first clear the 'Test' and 'MyTest' collections,

then run the ForAll from the Button 1 logic. 

And once that is complete, it should run the ForAll from the Button 2 logic.

Be sure to test this formula thoroughly to make sure it functions as you expect in your specific use case. 

If you encounter any issues, be sure to state what issues you encountered (if any) or see if it even worked for you like this.

See if it helps @MGeale 

[MGeale]

Oh wow @poweractivate, see I knew the answer was staring at me right in the face!

Stupid question, is there a way to do it without collecting Test? I've done everything in With blocks previously and would ideally like to keep it as a context variable.

Cheers 🙂

[developerAJ]

@MGeale 

Please help us understand what's the issue you are facing exactly because adding code of button2 in button1 is not an issue. you can combine any number of code in a single button

[poweractivate]

@MGeale 

Your question isn't stupid at all, it's a good one. Collection can often be avoided if the data can be processed directly. However, in Power Apps, using collections is just a common way to store and manipulate data. Collections are sort of like variables but instead can store multiple records and tables rather than just one.

That said, there' could be an alternative method to avoid using collections, but it sort of could be considered more complex and could potentially have performance issues in some cases, depending on the size of your data sets.

Instead of using collections, you can use the 'With' function to create context variables within the ForAll
Here's an example of how you might try it:

With(
{
Test: ForAll(
colDepartments,
With(
{
wStringToFind: Char(34) & Left(
Substitute(
Title,
" ",
"_x0020_"
),
32
) & Char(34) & ":false"
},
ForAll(
Distinct(
wSplitJSON,
Value
) As wRecords,
If(
wStringToFind in wRecords.Value,
{
a: wRecords,
b: Substitute(
Last(
ForAll(
Split(
Match(
Substitute(
wRecords.Value,
Char(34),
""
),
"(,Title:.+?,)"
).FullMatch,
":"
),
{Result: ThisRecord.Value}
).Result
).Result,
",",
""
)
}
)
)
)
)
},
// Next, we can try and use the results stored in the 'Test' variable
With(
{
MyTest: ForAll(
Distinct(
Test,
b
) As uniqueUser,
If(
CountRows(
Filter(
Test,
b = uniqueUser.Value
)
) = CountRows(colDepartments),
{a: uniqueUser.Value}
)
)
},
MyTest // Here should be the result that you want to achieve
)
)

This example assumes that Test is a table.

Depending on your specific data structures, the implementation can be different.

Make sure to test thoroughly to ensure it works as expected for your specific use case. 

See if this above may work in terms of avoiding the collections @MGeale 

[MGeale]

@poweractivate Ooo we're getting close! I can get the Test: scope working but whenever I try and populate MyTest from Test, it fails saying it can't find b. 

I know my naming structure sucks, but I'll fix it up once I get it working 😛

[poweractivate]

@MGeale 

Yes you are right, that could be because there is a second outer loop and that 'b' is not in scope there.

In that case you should try something else and see if the above could work as starting point.

[poweractivate]

@MGeale 

I thought through it from the beginning and I think you may want to restructure your SharePoint Lists to make this more optimal. See below.

First, let's tackle the simple case where we know the three departments, A, B and C, and we want to filter a list to only show users that have No in all three department columns.

In Power Apps, you can use the Filter function to accomplish this. The Filter function takes two arguments: the data source to filter and a formula to apply to each record of the data source.

Assuming your data source is called Users (mine is called List02) and the columns are named DepartmentA, DepartmentB, and DepartmentC,
and assuming the columns are Yes/No type columns,
the formula could look like this:

Filter(List02, DepartmentA = false && DepartmentB = false && DepartmentC = false)

This should return a filtered list of users where all the three department columns are marked as No.

However, this solution has a limitation. If you add more departments to the list in the future, you'll have to modify this formula to include the new departments.

For a more dynamic solution where you want to filter by users that have No in all department columns, regardless of how many departments there are, you would need to structure your data differently. Instead of having a column for each department, you might want to consider a structure where each department is a record in a related table.

For instance, you might have a Users table with columns like UserId, UserName, etc., and a separate Departments table with columns like UserId, DepartmentName, and Status (Yes/No). This way, when a new department is added, you simply add a new record to the Departments table, rather than a new column to the Users table.

So after you made this new related List in SharePoint,

then, to filter for users who have No in all department statuses, you could try this (but note, this may not be an ideal solution for large datasets over the record limit, max 2000 - however since you are using Collection too, yours is also not ideal, and your formulas are far more complex as well, and ForAll by itself used directly in SharePoint data source like that may also not be delegable either with or without delegation warning, and with or without a Collection, and whether it's in your version or mine)

ClearCollect(colResult, Users)
ForAll(
    Users,
    If(
        CountRows(
            Filter(
                Departments,
                UserId = Users[@UserId] && Status = "Yes"
            )
        ) > 0,
        Remove(colResult, ThisRecord)
    )
)

First, clear a collection to store your results.
ClearCollect(colResult, Users)

Then, run a ForAll over the Users, and for each user, check if there exists a department with the Status "Yes". If yes, remove this user from your result collection.

This solution filters the Users table by checking every Department record for each User and ensures that the Status of each Department is "No". If there's a department with a "Yes" status for a user, that user is not included in the final list.

Compared to your formulas, it's likely this version would drastically reduce the length and complexity of the formulas if you restructure the SharePoint Lists to have the related Departments List in the way I just described. 

Please note that this is a high-level overview, and the exact syntax and implementation will depend on your specific app and data source structure.

Also, note that this may not actually be an ideal solution anyway when using ForAll directly on the Data Source, as it may not be ideal if you have say more than 2000 users and the default is 500, you need to explicitly raise that record limit to 2000 in the app under the settings, and you can't go higher than 2000). but for now to keep it simple we might assume it can work for the moment for the sake of example with the record limit.

I thought through from the beginning and may suggest this restructure. If you are ultimately using a collection and ForAll, the Collection will be limited to the record row limit - so might as well use a simpler solution if possible and avoid all the complex ForAll in your version, etc. Also even without a Collection, using the data source directly with ForAll may also be limited to the record row limit, with or without any yellow triangle warning, and even without a Collection, but just because ForAll is being used directly on the SharePoint data source in entirety this by itself may be causing the number of maximum iterated records of ForAll to be no more than the record limit. That is because no function can actually go over the record limit.

When you use Galleries there is something else going on there, because internally the Gallery is fetching it in chunks, so sometimes using the Items property of a Gallery can also help, but doing this does not really bypass any limits, it's just the Gallery is never actually getting everything all at once to begin with. Actually if you were to CountRows on a Gallery, it should be a very small number - this is intentional and by design.

If it's not possible to restructure, then may have to keep your version, in that case, check if the above helps as an idea for your version, but for your version it might be different than this though.

[poweractivate]

@MGeale 

I thought about it some more, and considering best practices, I feel like I should not really recommend the practice you are using.

I may suggest again even a better way now than the previous post I made,
and give more detail what I would recommend instead.

First here are a few reasons:

1. Complexity: The method you use is overtly complex in terms of formulas

2. Scalability: There are problems both with record limit and with max departments, where my approach may be better for both.

Record limit means because you have to use ForAll you can't go over the record limit.

Max departments means you keep having to add columns in the schema for new departments. This is not recommended as you may no be able to go over 8000 bytes total of columns in SharePoint List.
At a certain point the method you use is not scalable to more departments, and it may actually be fewer columns / departments than you expect, to the point that your way may actually not be too usable at a much sooner point than even the record limit. With record limit there may be ways to get around it. But we can't get around adding more SharePoint columns. Maybe the only way would be to add more Lists and duplicate all users again to continue or extend the list of departments they are in, but this is problematic and complex to maintain and you'd also have to definitely change your app each time you added this new list, something you may be trying to avoid.

Also, the idea of putting each department in columns, even if unlimited columns were possible in SharePoint, could be a problematic database design for this scenario and should be avoided if possible. The reason is because the schema is changed for each new department, i.e. columns must be added each time, changing or altering the schema each time, when an alternative way of arranging the Lists would not require adding any columns or altering the schema for every new department, and would just be better and more maintainable in general.

And here is the actual new way:

Instead of using columns, use rows.
The columns of this new list are simple. User and Department.

If a user is in more than one department, simply add another row with user and department.

In terms of suggesting a solution both to you and the community,
I really feel like the workaround you use is:
- too complex in an unnecessary way,
- uses complex nested ForAll's and other complex operations when these can be avoided
- uses a data structure that may not be scalable in SharePoint to more departments past a certain point due to needing to add too many more columns.

- uses a data structure that requires adding columns for each department when this may be avoided entirely instead.

For the reasons of all these points, covered by this simpler way of setting up the data and ultimately far simpler formula, which has better performance too by the way, I would suggest not using your way,
and instead checking if my approach might work better for your scenario.

To avoid record row limit scaling issue I would suggest not even using ForAll.
I would suggest using a carefully crafted Filter on this same List, in the Items property of a Gallery.
That way, even if there are lots of records of Users who are not in any department, they all can be shown in the Gallery,
then you can scroll through the Gallery if there are lots of Users fitting that criteria.

If you have to use ForAll, which in your case you may have to, you can try the following high level approach:

A. Let's say you have at most 2000 departments for sake of simplicity and you raised the record limit to 2000. I doubt you can have anywhere near 2000 columns by the way in your list, which poses an issue in your method.
Let's say you even also have at most 2000 users for sake of simplicity. If you are not sure how to raise this limit by the way right now, replace 2000 with 500, which is the default. It may be actually best to start with 500 in your mind if you see 500 as being the highest you can ever possibly go as well.

B. You can repurpose your list that has yes/no, remove all the columns for departments, and keep it as a plain user list, and simply use it directly in a ForAll. To keep this example simple, we'll say you can't have more than 2000 records here or so (or even pick 500 at first). It's possible to go over actually in terms of the number of users, however, that would require a small additional layer of complexity to get the additional records. For example, if you have between 2000 and 4000 users, let's say 3000, we may want to do this: You may want to iterate a counter, get the SharePoint id's and keep track of it. When the loop ends, you want to check if there is a range of possible next id's such as using a Timer somehow - if this range exists, the loop is attempted again in a batch of 2000, starting with the next id found, until at the end of each batch loop no further possible id's are found. It needs to be a range of ids as it's possible users are deleted, etc. so the SharePoint ids may have breaks in them, for example if record #2000 is reached, record #2001 may not exist, but record #2002 might. This is a set of advanced techniques, which may scale well past 2000 even into thousands of records, and I want to keep this example simple for now. There may also be other techniques which avoid even using the batching the ForAll multiple times, for better performance, but again, to keep it simple for now. However just adding this batching would still be preferred to the multiple ForAll's and substitutes and putting all the departments as columns, which you may not be able to do anymore in SharePoint past a certain point or a certain number of departments.

With the approach you have, there's many more problems to solve, some which may not be possible to solve like the point where you can't add any more columns for departments anymore - compared with this method.

C. ForAll on all the users, then check this new list to see if they are there at all. If no match is found, they are in no department, so do a Collect on the one user in the iteration in a new Collection. If this Collection goes over 2000 total it's fine as you push only 1 entry per iteration, not something that's more than 2000 (in this example, the total entries in this Collection cannot be greater than the number of users through, but if it did, such as if adopting advanced techniques for batching on users, it may be possible for the total records in Collection to exceed 2000 records. The record limit in terms of Collection usually comes into play when you try to push them all at once more than the record limit in a single call to ClearCollect or Collect, but it may work if there are no more than X records per call of the function, where X is the record limit. Also, usually it's not good to use ForAll as a loop and not use the output of ForAll, however for both simplicity, and because sometimes it's necessary to do it this way for workaround purposes, we'll say we can be open to possibly doing that for now if we're careful). Ideally we would not ever have a Collection doing this and would actually somehow be able to fetch this list of users who are not in any departments in a more direct way. There are multiple possible approaches to consider both in setting up the data and in some implementation details which may make it possible to never even have to use ForAll - but for the sake of simplicity, I offer you this alternative solution to yours which at least addresses the issue of adding columns for new departments, this one is a scalability issue as it is the most likely to both be the most complex to maintain, and to stop working the soonest once a certain number of departments is reached. The columns as departments is the most obvious bottleneck for both complexity, schema alteration on update, and max number of departments, so I suggest you try and rearrange the data in way I suggested above which may simplify what you are trying to do.

See if it helps @MGeale