SOLVED

Filtering Gallery based on SP list field and wildcard

%3CLINGO-SUB%20id%3D%22lingo-sub-1052702%22%20slang%3D%22en-US%22%3EFiltering%20Gallery%20based%20on%20SP%20list%20field%20and%20wildcard%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1052702%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20my%20issue.%20I%20have%20an%20SP%20list%20for%20recording%20hazards%20on%20our%20factory%20floor.%20Each%20item%20in%20the%20SP%20list%20has%20a%20unique%20number%20i.e%3C%2FP%3E%3CP%3EHazard%201%26nbsp%3B%3C%2FP%3E%3CP%3EHazard%202%26nbsp%3B%3C%2FP%3E%3CP%3Eetc%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlong%20side%20this%20we%20have%20a%20document%20library%20collecting%20PDFs%20and%20pictures.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20gallery%20for%20the%20doc%20lib%20which%20I'd%20like%20to%20filter%20based%20on%20the%20SP%20list%20hazard%20number.%20The%20doc%20lib%20has%20files%20named%20by%20the%20hazard%20number%20and%20todays%20date%20i.e%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHazard%201_06_12_2019.pdf%3C%2FP%3E%3CP%3EHazard%201_05_05_2019.pdf%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20filter%20this%20using%20one%20of%20the%20following%20formulas%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELookUp(Apps%2CName%20%3D%20Gallery4.Selected.Title)%3C%2FP%3E%3CP%3EOr%26nbsp%3BFilter(Apps%2CStartsWith(Name%2CGallery4.Selected.Title))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20problem%20is%20if%20I%20use%20the%20StartsWith%20command%20then%20when%20I%20get%20to%20Hazard%2011%20the%20gallery%20still%20shows%26nbsp%3B%3C%2FP%3E%3CP%3Edocuments%20for%20Hazard%201%20and%20Hazard%2011%20and%20they%20both%20start%20with%20Hazard%201***%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20use%20the%20Lookup%20command%20it%20doesn't%20find%20the%20files%20as%20the%20Hazard%20files%20have%20the%20date%20at%20the%20end.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20achieve%20what%20I'm%20after%3F%20Hope%20I've%20made%20sense%2C%20it%20sounded%20good%20in%20my%20head%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EGavin%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1052702%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EPowerApps%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1054475%22%20slang%3D%22en-US%22%3ERe%3A%20Filtering%20Gallery%20based%20on%20SP%20list%20field%20and%20wildcard%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1054475%22%20slang%3D%22en-US%22%3EYou%20don't%20have%20any%20kind%20of%20Lookup%20columns%20on%20the%20SharePoint%20list%20to%20help%20keep%20these%20Item's%20together%20with%20a%20key%20pair%3F%20If%20you%20had%20a%20lookup%20column%20in%20the%20doc%20lib%2C%20that%20referenced%20the%20hazards%2C%20then%20you%20could%20look%20up%20the%20hazard%20name%2C%20then%20it%20would%20be%20much%20easier%20to%20just%20match%20there%2C%20then%20have%20a%20separate%20column%20for%20the%20date%2C%20or%20does%20everyone%20not%20want%20to%20put%20in%20metadata%20and%20just%20upload%20files%20based%20on%20name%3F%20%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20could%20also%20use%20Flows%20to%20set%20metadata%20for%20you.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EAnyway%2C%20to%20match%20those%20by%20name%20as%20is%20you%20could%20try%20the%20following.%20(Don't%20know%20if%20you%20would%20have%20delegation%20issues%20or%20not%2C%20but%20worth%20a%20test)%20%3CBR%20%2F%3E%3CBR%20%2F%3EFilter(Apps%2CStartsWith(Name%20%3D%20Gallery4.Selected.Title%20%26amp%3B%20%22_%22)%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20what%20I'm%20doing%20here%2C%20assuming%20all%20filenames%20have%20an%20underscore%20after%20the%20hazard%2C%20is%20temporarily%20for%20the%20filter%20appending%20an%20underscore%20to%20the%20selected%20Hazard%2C%20this%20way%20when%20it%20goes%20to%20match%2C%20StartsWith%20Hazard%201_06_etc%20would%20match%20Hazard%201_%20and%20not%20Hazard%2011_%20etc.%20%3CBR%20%2F%3E%3CBR%20%2F%3EDon't%20have%20a%20test%20setup%20to%20test%2C%20but%20off%20top%20of%20my%20head%20that%20should%20technically%20work%2C%20just%20dont'%20know%20about%20delegation.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1055144%22%20slang%3D%22en-US%22%3ERe%3A%20Filtering%20Gallery%20based%20on%20SP%20list%20field%20and%20wildcard%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1055144%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F869%22%20target%3D%22_blank%22%3E%40Chris%20Webb%3C%2FA%3E%26nbsp%3B%2C%20thanks%20for%20your%20reply.%20I%20hadn't%20thought%20about%20the%20lookup%20column.%20Sometimes%20it%20just%20takes%20someone%20else's%20point%20of%20view%20to%20change%20things.%20I've%20now%20added%20some%20meta%20data%20and%20have%20a%20linked%20column.%20It%20now%20works%20perfectly%20and%20no%20delegations%20issues.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGav%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi All,

 

Here is my issue. I have an SP list for recording hazards on our factory floor. Each item in the SP list has a unique number i.e

Hazard 1 

Hazard 2 

etc

 

Along side this we have a document library collecting PDFs and pictures. 

 

I have a gallery for the doc lib which I'd like to filter based on the SP list hazard number. The doc lib has files named by the hazard number and todays date i.e

 

Hazard 1_06_12_2019.pdf

Hazard 1_05_05_2019.pdf

 

I can filter this using one of the following formulas

 

LookUp(Apps,Name = Gallery4.Selected.Title)

Or Filter(Apps,StartsWith(Name,Gallery4.Selected.Title))

 

My problem is if I use the StartsWith command then when I get to Hazard 11 the gallery still shows 

documents for Hazard 1 and Hazard 11 and they both start with Hazard 1***

 

If I use the Lookup command it doesn't find the files as the Hazard files have the date at the end.

 

Is there a way to achieve what I'm after? Hope I've made sense, it sounded good in my head :)

 

Thanks

Gavin

2 Replies
Highlighted
Best Response confirmed by Gavin Squires (Occasional Contributor)
Solution
You don't have any kind of Lookup columns on the SharePoint list to help keep these Item's together with a key pair? If you had a lookup column in the doc lib, that referenced the hazards, then you could look up the hazard name, then it would be much easier to just match there, then have a separate column for the date, or does everyone not want to put in metadata and just upload files based on name?

You could also use Flows to set metadata for you.


Anyway, to match those by name as is you could try the following. (Don't know if you would have delegation issues or not, but worth a test)

Filter(Apps,StartsWith(Name = Gallery4.Selected.Title & "_")

So what I'm doing here, assuming all filenames have an underscore after the hazard, is temporarily for the filter appending an underscore to the selected Hazard, this way when it goes to match, StartsWith Hazard 1_06_etc would match Hazard 1_ and not Hazard 11_ etc.

Don't have a test setup to test, but off top of my head that should technically work, just dont' know about delegation.

Highlighted

Hi @Chris Webb , thanks for your reply. I hadn't thought about the lookup column. Sometimes it just takes someone else's point of view to change things. I've now added some meta data and have a linked column. It now works perfectly and no delegations issues.

 

Thanks for your help :)

 

Gav