cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rockmond
Helper III
Helper III

Filter for Text String with Space Not Working

Hello,

 

I've got a maddening situation. I have a SharePoint list that is populated with data from Project Online. One of the fields is 'ProjectTier', and this has three possible entries, 'Tier 1', 'Tier 2' or 'Tier 3'. Within a PowerApp I have 3 buttons that filter the full list of projects by Tier, i.e. the Filter is an 'If' statement that includes ProjectTier="Tier 1", ProjectTier="Tier 2" and ProjectTier="Tier 3". The buttons set a variable that determines what part of the if statement is operational. The filtering works for Tier 1 projects but not for Tier 2 or 3. I have implemented a workaround by calculating a new column, 'altTier', in the SharePoint list that removes the space. Now it works, but I don't like it because I think the calculated column slows down the app and might cause delegation issues.

 

Doing some diagnostic work I toyed with the Filter, changing it to one line, no 'If' statement: StartsWith(ProjectTier, "Tier ". I actually started with 'T' and kept adding letters and space to see what happens. The SP list has 944 projects. When I filter for 'Starts with 'T' I get all 944. I get 944 for all filters through 'Tier', but when I add space, 'Tier ', I only get the number of projects that are Tier 1. 

 

I think what is happening is that Project is putting some code in as the space for Tier 2 and Tier 3. Don't know why or why it doesn't do it for Tier 1, but I believe that is the case because quite some time back I ran into the same problem with reading Project data into Power BI. I can't remember what the fix was, but I know it had to do with 'invisible' code that was the space.

 

Can anyone help me fix this?

1 ACCEPTED SOLUTION

Accepted Solutions

Okay, here is an update. I read Project data from PWA into PowerBI. Created a bar chart based on ProjectTier. All three categories appeared. But, when I created a filter and put in just Tier 1 I got only Tier 1, great. But when I put in Tier 2 or Tier 3 I got nothing. I went into the Power Query data and copied a 'Tier 2' and a 'Tier 3' from the Table to Notepad. Then I opened the PowerApp and copy and pasted from Notepad into the Filter code for the gallery. Wah lah, it works! Not sure what the problem is but I least I have a fix. I will, however, try your diagnostics to see if I can uncover what is going on under the hood. Thanks for all your efforts!

View solution in original post

10 REPLIES 10
Liam_Chapman
Resolver II
Resolver II

Hi @rockmond ,

I created a choice field in SP called ProjectTier, with the choices defined as Tier 1, Tier 2, Tier 3.

I am not sure if this is what you are trying to achieve, but I have got the gallery to filter the items in the gallery based on which button is selected.

The OnSelect of the button looks like below:

Liam_Chapman_0-1608652055516.png
And then the gallery Items is like below:

Liam_Chapman_1-1608652094192.png


Not sure if that helps?

 

poweractivate
Community Champion
Community Champion

@rockmond 

 

If you are right it may be using another ASCII character instead of space for some reason and it may be somehow related with Project Online populating the SharePoint List column values like you were saying.

 

You could attempt to find out what ASCII character code number it is, and use Char(WhateverCharCodeNumber) from Power Apps Canvas App, instead of space in the Filter. To find out the ASCII character you could attempt to press F12 when looking at the SharePoint List and looking at the Elements tab, right clicking on a column value and seeing if the character shows up in the HTML, or perhaps attempt some other way(s) to just see what character it is putting there. Fun fact is that space is Char(32) for example. So in your case, for some reason, it is probably Char(SomeOtherNumberThan32). Once you find out the code, make sure it is pretty consistent across all the values, etc. and see if you can just use that as Char(SomeCode) somewhat  as consistently as possible for Tier II and III. 

 

In case you are unsure how to find out which ASCII character code it is, you can reply here in case as well. Another way you can try is using the SharePoint API directly and seeing the raw text, the character might show there. In case you are unsure or really stuck on this, please reply and maybe we can give you the more specific example of a test URL to use that might return SharePoint List items in the raw form.

 

See if above helps @rockmond 

Thanks for the reply, Liam,

What you have outlined in your response is basically what worked in the workaround, creating a new column that is the Tiers without the space, so that, and the fact that your reply has the same basic functionality, reinforces my view that it is more than likely there is something odd going on in how that space is being conveyed from Project through SP to PowerApps.

Thanks for your quick response. Yes, I agree with you, and will try to find the code using the techniques you suggest. If I am unable to do that, and I am not super confident, I will get back to you. I appreciate your offer.

WarrenBelz
Super User
Super User

Hi @rockmond ,

Thanks for the PM -

@Liam_Chapman is correct on this - one small thing I do (which is more window dressing) at each button on simply put. 

UpdateContext({varTier:Self.Text})

then the gallery Items

Filter(
   YourSPList,
   ProjectTier=varTier
)

I tend also to use Context Variables is all the "action" is on the same screen.

 

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.

Thanks Warren,

Yes, I had used a similar approach to Liam's in a workaround, that workaround being a calculated column in SP, altTier, that was the same as ProjectTier with the exception that the space is removed. This works fine, but it is extra stuff and since it is a calculated column delegation gets messed up. So I am pretty sure it is not my coding but is something in the actual record field for the space that I can't see.

 

My filtering uses global variables. What are the benefits of using context variables? I suppose it makes my list of global variables smaller, and probably improves app performance as it doesn't need to store the variable all the time. Is that right? Any other benefits? I'm also interested in the varTier:Self.Text functionality. I'll have to try that out. Thanks.

I am petty much at sea when it comes to how to use a Sharepoint API. I tried the F12 method you suggested but couldn't find anything about specific columns. What am I doing wrong?

Okay, I created a flow that pulls data directly from Project Web App (PWA), just like I currently do for my SP list that feeds the PowerApp. When I filter for ProjectTier eq 'Tier 1' I get all the Tier 1 projects. When I change to ProjectTier eq 'Tier 2' I get nothing. Same with 'Tier 3'. When I simply filter for ProjectTier ne null, I get all the projects and I can't see any difference in the output for Tier 1 as compared to Tier 2 or 3!?!?

@rockmond 

 

Try to open this URL in your browser:

 

https://{your_base_url}/sites/{your_sharepoint_site_name}/_api/web/lists/getbytitle('{your_list_title}')

 



See if any of the raw text there gives any hint.

 

In case it is still being interpreted as a regular space on the API return, this still might not help. However, see in case if this one helps and reveals the Char Code. If you find it, use a an ASCII table  or an ASCII lookup tool 

 

to figure out what code it is. The correct code would be the decimal column if using the ASCII table. For example, space is decimal code 32, or Char(32) in a Power Apps Canvas App if explicitly referred to that way.

 

Once you find the right Char Code:

 

Then in Canvas App, Filter with 

 

"Tier" & Char(YourCharCodeHere)

 

 

Check in case above helps @rockmond 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (3,025)