Determine if string in Sharepoint column StartsWith string in collection

[seadude]

So, I'm working at a delegation work around and rather than specifying each potential StartsWith() function like so:

Set(varLookback, Today() - 30);

ClearCollect(
    colActiveRecords, 
    Filter(PROJECTS_DEV,
        And(
            Created > varLookback,
            Or(
                StartsWith(status, "Funded"),
                StartsWith(status, "New"),
                StartsWith(status, "Cancelled"),
                StartsWith(status, "On-hold")
            )
        )
    )
)

 ...I'm wondering if there is a way to do something more dynamic. I can get a list of all potential statuses but can't seem to figure out how to dynamically assign StartsWith to each value. 

ClearCollect(colStatuses, Distinct(PROJECTS_DEV, status)); //<-----GET LIST OF ALL STATUSES

Set(varLookback, Today() - 30);

ClearCollect(
    colActiveRecords, 
    Filter(PROJECTS_DEV,
        And(
            Created > varLookback,
            Or(
                StartsWith(status, "Funded"),  //<------BUT HOW TO MAKE DYNAMIC HERE?
                StartsWith(status, "New"),
                StartsWith(status, "Cancelled"),
                StartsWith(status, "On-hold")
            )
        )
    )
)

Its something like this... but this is incorrect:

ClearCollect(
    colActiveRecords, 
    Filter(PROJECTS_DEV,
        And(
            Created > varLookback,
            ForAll(colStatuses,
                StartsWith(status, Result)
            )
        )
    )
)

Is it possible?

[WarrenBelz]

Hi @seadude ,

Two options, but I don't think either will suit - if you are just looking for a match, you could do this

With(
   {wLookback, Today() - 30)},
   ClearCollect(
      colActiveRecords, 
      Filter(
         PROJECTS_DEV,
         Created > wLookback &&
         (
            status = "Funded" ||
            status = "New" ||
            status = "Cancelled" ||
            status = "On-hold"
         )
      )
   )
)

You can also be dynamic, but you would be back with your Delegation issue

With(
   {
      wStatus: 
      [
         "Funded",
         "New",
         "Cancelled",
         "On-hold"
      ],
      wLookback, Today() - 30
   },
   ClearCollect(
      colActiveRecords,
      Filter(
         PROJECTS_DEV,
         Created > wLookback &&
         Status in wStatus
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Eelman]

@seadude 

Another option could be to re-write your last statement like this

Clear(colActiveRecords);
ForAll(
    colStatuses As Status,
    Collect(
        colActiveRecords,
        Filter(PROJECTS_DEV,
        And(
            Created > varLookback,
            status = Status.Result
        )
    )
)

** Note I've used '=' instead of StartsWith here because I wasn't sure of your use case. If data in status is like "Funded by the Wiggles" or "Funded by Seadude", etc then you can adjust this to suit. I've assumed status = "Funded", as an example.

There are some delegation issues here though:

- Firstly, the Collect() inside the ForAll() will return up to 2000 records for each 'status', so if there are more than that per status then you'll run into issues

- Secondly, your use of Distinct() to get the statuses will only look at the first 2000 records of your data source and return the Distinct values. A better option maybe to set this column as a Choice column then get the distinct values via:

ClearCollect(colStatuses, Choices(PROJECTS_DEV.status))

This may not be your use case though (see above Note)?

Just another option to ponder.

[seadude]

Thanks to both of you for the great suggestions. 

Here's where I went first: 

- @Eelman ,while it is a great idea to make "status" a Choice column in Sharepoint and use the Distinct function on it (much better than my original thought), I like to keep all picklists/choices/etc. in one place (PowerApps).

- So I sprinkled @WarrenBelz 's With() statement into your idea.

With(
    {
        varStatus:
        [
            "New",
            "Funded",
            "Cancelled",
            "On-hold"
        ]
    },
    ForAll(
        varStatus,
        Collect(
            colActiveRecords,
            Filter(PROJECTS_DEV,
                And(
                    Created > varLookback,
                    status = varStatus.Value
                )
            )
        )
    )
)

 This still has delegation message.

Though I am very curious if it will return up to 2k **of each status** like you mentioned @Eelman . I don't have a data set large enough to test this. 

Can you confirm from your previous experience?

[Eelman]

@seadude 

Yes, I can confirm this. I hadn't tested it prior but have just done so now.

I have a List with a Single Line of Text column where 1 value has 2005 items and another has 1039 items. Running the code on my collection the CountRows tallied 3039 ie 2000 (not 2005) + 1039

[Eelman]

Actually, it should be possible to use this method here to put your ForAll inside another ForAll and get all your records, if that is your requirement?

[Eelman · Accepted solution]

@seadude 

So, using that link I provided I re-built the formula to get all your records, like so:

Clear(colActiveRecords);
With(
    {
        varStatus: [
            "New",
            "Funded",
            "Cancelled",
            "On-hold"
        ],
        
        wID: First(
            Sort(
                PROJECTS_DEV,
                IDNumber,
                Descending
            )
        ).IDNumber
    },
        
        Set(
        vNum2000s,
        Switch( Mod(wID,2000),
            0, Round(wID / 2000,0),
            RoundDown(wID / 2000,0) + 1
        )
    );
    ClearCollect(
        colSeq,
        AddColumns(
            RenameColumns(
                Sequence(vNum2000s,0,2000),
                "Value",
                "Start"
            ),
            "End", Start + 2000
        )
    );
    
    ForAll(varStatus As Status,
            ForAll(colSeq As StartEnd,
                Collect(colActiveRecords,
                    Filter(PROJECTS_DEV,
                        IDNumber > StartEnd.Start && IDNumber <= StartEnd.End &&
                        Created > varLookback && status = Status.Value
                    )
                )
            )
    )
);

Notes:

- this solution relies on your list having a column named IDNumber which is of Type Number. I usually mirror the List ID with this column but it can be any sequential number pattern. This can be retro fitted into a large-ish list using Flow if required?

- you get a delegation warning inside the With() using the First() function but this can be ignored because you are sorting largest to smallest then only getting the first IDNumber

@WarrenBelz maybe able to work his magic and compress this down into a better With() format but I've tested it and it works for me 🙂 

Let me know if you need me to explain any of the moving parts further.

[WarrenBelz]

@seadude ,

The syntax for this is here, however @Eelman's code will do the same thing with a collection you could just clear at the end.

[seadude]

Right on! This is was a great discussion and thank you for turning me onto this method. I've been PowerApping for over 3 years and just now getting into With() statements. Quite a bit more elegant than other methods I've used in the past! (https://powerusers.microsoft.com/t5/Building-Power-Apps/Pulling-in-large-ish-SQL-tables/td-p/243777). 

The Monitor trace was fun to unpack too:

Thanks @Eelman and @WarrenBelz !

(Great blog too! Glad you linked out to it.)

[Eelman]

@seadude 

Thanks for sharing that trace, it looks quite interesting. I've never thought to check through that overly much before but I may do it more now 🙂 

I also need to get better at using With() but I'm sure @WarrenBelz  will keep me on that path!