cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
seadude
Memorable Member
Memorable Member

Determine if string in Sharepoint column StartsWith string in collection

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?

1 ACCEPTED SOLUTION

Accepted Solutions

@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.

 

View solution in original post

9 REPLIES 9
WarrenBelz
Super User III
Super User III

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
Super User
Super User

@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
Memorable Member
Memorable Member

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.

seadude_0-1613966948878.png

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?

 

@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

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?

@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.

 

View solution in original post

@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
Memorable Member
Memorable Member

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:

seadude_0-1614065296580.png

 

Thanks @Eelman and @WarrenBelz !

 

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

@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!

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (53,485)