Answered

Searching for a record in another list (not gallery) display result on form.

(0) Share

Report

Posted on by golfnutt82

1,362

Hello and good morning,
I have a form that contains a search function where a user starts to type the first characters in the text search box.
The dropdown box (lookup) shows the one or many responses to what was entered in the text search box.
The user selects the choice and the fields on the right display the related content.

The issue is that their could potentially be 3000 records to filter so the above isnt realistic.
The form above updates the list "Access Requests" that has a lookup.
I have a connection to the "Roles" list where the disabled data on the right of the form is displaying.
Whether on this form or maybe a new form with a text search box that can do a mid string function, can I pass that ID to the Lookup dropdown box to display the record when a button is pressed?

Categories:

Building Power Apps

All responses (33)

Answers (6)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@golfnutt82

So you are mentioning that the issue is that the scenario is not realistic. Can you expand more about what you are trying to do and about the mid string function?
golfnutt82 1,362 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Randy,
I would like the ability to search for a record in the Search Text Box using a midstring function(or something else), click on the result or click on a button, and pass that value/ID to the main control "Role Name" field versus trying to look through the hundreds and in the case could be thousands of lookup values, which would then update the whole form.

In our current case, I have a text box where I can start typing the first few letters of the role.
This then filters the role name dropdown with one or many choices based on user entry.
The user wants the ability to search or filter for one record not 10 - 500 records.
Sometimes the user doesnt know what the first three or more letters of the Role Name is.
They may know the middle part of the role name.
In the "Roles" list (this is connected) I have a text field called "Role"
Excel has a Midstring function but I am not sure if that will work.
It would be nice to work with what I have currently but I am flexible.

Thanks for your help
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@golfnutt82

Well, the scenario you mention is possible (although I am not seeing that you would need a separate button or anything like that). However, the introduction of the Mid function is going to cause issues.

Currently you are searching by StartsWith. This is a delegable function. So, if your records are more than the limit (2000), then this is really the only delegable potential. IF you will not have more than that, then you can utilize the Search function or IN operator (both non-delegable).

Recalling from prior posts that you are using SharePoint...then the above is all possible.
golfnutt82 1,362 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Randy,
Ok, yes its all SharePoint.
I am aware of the 2000 record limitation so I will need to communicate that to the people in charge.
If I dont need to use a button then fine and if I can utilize what I already have and use a search function then ok.
Can you send me a link in how to achieve what you propose? Or give me an example and I alter what I have.
Thanks again
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@golfnutt82

Not sure what kind of link you are looking for. If it's for the functions, those are all in the function docs.

I am assuming your Items property of the dropdown is currently (sorry don't recall if we covered this before): Filter(yourList, StartsWith(columnName, yourTextInput.Text))

If no more than 2000 records will ever exist, then delegation warnings can be ignored on the following formula example to replace your Items property:

Filter(yourList, yourTextInput.Text in columnName)
golfnutt82 1,362 on at

Like
a
(0)

Report
Copy link

Link copied!

Any link that will give me information concerning your proposal.
I will look at incorporating what you've givin me.

Thank you!
golfnutt82 1,362 on at

Like
a
(0)

Report
Copy link

Link copied!

This is what I am using in the Items property for the Role Name lookup field.
Filter(Roles,StartsWith(Role,Text_Search1_1.Text))
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@golfnutt82

Well...what I am suggesting comes from my head, so I don't have a direct link to that in any particular way 😉

So yes, as I stated in the last reply, you would change that formula to:

Filter(Roles, Text_Search1_1.Text in Role)

This would not be delegable, so this is going to depend on the amount of records you have in Roles.
golfnutt82 1,362 on at

Like
a
(0)

Report
Copy link

Link copied!

Yes, this is what I have now and its inadaquate to what I need.
You mentioned a "Search" function but I am not able to get that to work with my current scenario.

It looks like that function has to be tied to the form datasource, which is "Access Requests" and not "Roles"
As you know, the lookup dropdown box is filtered using Filter(Roles,StartsWith(Role,Text_Search1_1.Text)).
Can I not modify the filter to use Search or can I modify the existing Lookup dropdown so it does the search?
Thanks again Randy.
Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
@golfnutt82

No, Search is usable (although it is the same results as the In operator).

So you have this now from what you said that is not adequate:

Filter(Roles,StartsWith(Role,Text_Search1_1.Text))

That function uses the StartsWith and will not search in the Role for anything in it.

This function I last provided WILL search in the Role text...anywhere:

Filter(Roles, Text_Search1_1.Text in Role)

So, if you have a Role with a value of "Super Man" and the person entered "Man" in the text search...it would find that record.

You can do it with the Search function as well:

Search(Roles, Text_Search1_1.Text, "Role")

This will produce the same results as above.