I had to search a SharePoint list from a PowerApp and, as you may be aware, the search function from PowerApps does not delegate to SharePoint, so you’ll run into the usual delegation errors once your list grows to more than 2000 items. This blog post is my experience working through Reza Dorrani’s video on this topic. You’ll need to watch his video for this blog post to make sense. See https://youtu.be/NJaHC5S9Cjo?si=GPvgRbvFol1r–uc. I set up my search query exactly as he did his, with one notable exception as follows…
If you have custom columns that you’d like returned with your search results, you’ll need to create a managed property and map it to the crawled property corresponding to each custom column. Reza talks about this, but, in his video, these columns are already set up out-of-the-box. In my case, I had two custom columns. They are:
- Ship To Customer Name
- Division
I’d like to be able to return values from these columns to my search results. To do so I need to map a managed property to the crawled property. You cannot just create your own new managed property, and have it work (I don’t know why, if you do, please leave a comment as I’d like to understand that). Fortunately, Microsoft makes some properties available for just such occasions. They all begin with the text “RefinableString.” If you go to https://your-tenant-name.sharepoint.com/sites/yoursiteurl/_layouts/15/listmanagedproperties.aspx?level=sitecol you should see a list of managed properties. Alternatively, you can get there by going to your site settings and clicking on “Search Schema.” You’ll need to find the crawled property you need. Once you do, you can edit one of the “RefinableString” properties. My result looks like what you see in this screen shot.
The code for my search button is copied below. Note how at the end, and in the initial query, I’m referencing the refinableString variables which should be returned. You should test this out at the Microsoft Graph explorer at https://developer.microsoft.com/en-us/graph/graph-explorer as Reza describes in the video.
Set(varRequestBody,"{
""requests"": [
{
""entityTypes"": [
""listItem""
],
""query"": {
""queryString"": """& searchText.Text &" path:\""https://your-sharepoint-tenant.sharepoint.com/sites/sm/Lists/yourlistname\""""
},
""fields"": [
""RefinableString00"",
""Title"",
""listItemId"",
""RefinableString01"",
]
}
]
}");
Set(varFile, "data:text/plain;base64," & With({
InputText:varRequestBody,
AsciiTable:AddColumns(Sequence(2^8,1),"char",Char(Value)),
B64ToBin:
Table(
{b64:"A",bin:"000000"},
{b64:"B",bin:"000001"},
{b64:"C",bin:"000010"},
{b64:"D",bin:"000011"},
{b64:"E",bin:"000100"},
{b64:"F",bin:"000101"},
{b64:"G",bin:"000110"},
{b64:"H",bin:"000111"},
{b64:"I",bin:"001000"},
{b64:"J",bin:"001001"},
{b64:"K",bin:"001010"},
{b64:"L",bin:"001011"},
{b64:"M",bin:"001100"},
{b64:"N",bin:"001101"},
{b64:"O",bin:"001110"},
{b64:"P",bin:"001111"},
{b64:"Q",bin:"010000"},
{b64:"R",bin:"010001"},
{b64:"S",bin:"010010"},
{b64:"T",bin:"010011"},
{b64:"U",bin:"010100"},
{b64:"V",bin:"010101"},
{b64:"W",bin:"010110"},
{b64:"X",bin:"010111"},
{b64:"Y",bin:"011000"},
{b64:"Z",bin:"011001"},
{b64:"a",bin:"011010"},
{b64:"b",bin:"011011"},
{b64:"c",bin:"011100"},
{b64:"d",bin:"011101"},
{b64:"e",bin:"011110"},
{b64:"f",bin:"011111"},
{b64:"g",bin:"100000"},
{b64:"h",bin:"100001"},
{b64:"i",bin:"100010"},
{b64:"j",bin:"100011"},
{b64:"k",bin:"100100"},
{b64:"l",bin:"100101"},
{b64:"m",bin:"100110"},
{b64:"n",bin:"100111"},
{b64:"o",bin:"101000"},
{b64:"p",bin:"101001"},
{b64:"q",bin:"101010"},
{b64:"r",bin:"101011"},
{b64:"s",bin:"101100"},
{b64:"t",bin:"101101"},
{b64:"u",bin:"101110"},
{b64:"v",bin:"101111"},
{b64:"w",bin:"110000"},
{b64:"x",bin:"110001"},
{b64:"y",bin:"110010"},
{b64:"z",bin:"110011"},
{b64:"0",bin:"110100"},
{b64:"1",bin:"110101"},
{b64:"2",bin:"110110"},
{b64:"3",bin:"110111"},
{b64:"4",bin:"111000"},
{b64:"5",bin:"111001"},
{b64:"6",bin:"111010"},
{b64:"7",bin:"111011"},
{b64:"8",bin:"111100"},
{b64:"9",bin:"111101"},
{b64:"+",bin:"111110"},
{b64:"/",bin:"111111"}
)},
With({
BinRep:
Concat(
AddColumns(ForAll(Split(InputText,""), {Result: ThisRecord.Value}),"dec",LookUp(AsciiTable,char=Result).Value),//Convert text to Ascii character code (decimal)
Concat(Sequence(8,8,-1),Text(If(And(Mod(dec,Power(2,Value))>=Power(2,Value-1),Mod(dec,Power(2,Value))<Power(2,Value)),1,0)))&"","")//Convert decimal to binary
},
With({b64string:Concat(
Sequence(
RoundUp(Len(BinRep)/6,0),0),
LookUp(
B64ToBin,
bin=Mid(BinRep&Left("000000",6-Mod(Len(BinRep),6)),6*Value+1,6) //Left("000000"....) is padding right with zero
).b64&"",
""
)},
b64string&Left("====",Mod(4-Mod(Len(b64string),4),4))//Convert binary to base64
)
)
));
Set(searchResults,Office365Groups.HttpRequest("https://graph.microsoft.com/v1.0/search/microsoft.graph.query","POST",varFile));
Set(results1,First(searchResults.value));
Set(hitsContainers,First(results1.hitsContainers));
Set(hits,hitsContainers.hits);
ClearCollect(colSearchResults,ForAll(hits,{summary:ThisRecord.resource.fields.refinableString00,itemID:ThisRecord.resource.fields.listItemId}));
Set(showSearchGallery,true);
Set(showGalleryApproved,false);The gallery I use to display these results has colSearchResults for its items property. The onSelect is as follows.
Set(varRecord,LookUp('NameOfYourPowerapp',ID=Value(ThisItem.itemID)));Navigate(scrView);ViewForm(nameofyourform);Set(viewMode,"View")
Learning SharePoint 