@SalesmanGil if you are using a modern SharePoint Online list then what you want to do can be achieved quite easily with a customised Power Apps form. And the result will look better than a standard SharePoint form. In my example I'm going to add a new airport and its information to my list, looking up the airport name from a list of over 3000 airports in Oceania, selecting it by typing the first few letters of the name. Edit: see the attached video which goes through the steps detailed below.
1. So first we have the lookup list with which only has the Title column containing the name of each airport. I've named this list AirportLU:
2. We also have our main list where we are saving the data. From the PowerApps menu in the main list select Customize forms.
3. The form will be created and several (though not always all) columns will have been added. With the form selected you can add in any missing columns.
4. From the rail on the far left select the data sources icon, enter SharePoint and select your site and the lookup list:
5. On my form I've added a label to show the display mode of the form. I've named it lblMode and the Text property has been set as follows:
If(SharePointForm1.Mode=FormMode.Edit,"Edit Airport", "New Airport")
6. Next add a text input control which is where you will enter the first few letters of the airport name (or client name in your case). I've named this inpAirport. We only want to show the text input control if it's a new airport so set the Visible property to:
If(lblMode.Text="Edit Airport",false, true)
7. Next, make the Title data card larger so it can contain a few of the airports being looked up. Inside it add a vertical gallery and for its data source select the lookup list.
- For the Items property enter Filter(AirportLU,StartsWith(Title, inpAirport.Text)) - so the lookup list will be filtered to show only the items where the Title column starts with the text entered in the inpAirport field.
Set the Visible property the same as for the inpAirport field as, again we don't want to display the lookup gallery if we are editing an item already in the main list:
If(lblMode.Text="Edit Airport",false, true) - If the item is being edited not only do we not want the inpAirport field and the gallery to be visible but we don't want the large amount of white space they have left behind, so set the Height property:
If(lblMode.Text="Edit Airport",155, 317) - We want to get the item that is selected from the gallery, so for the OnSelect property enter
Set(SelectedAirport,Title2.Text)
which will create a variable called Selected Airport from the Title of the selected item.
8. Click on the Title data field and for the Default property enter:
If(lblMode.Text="Edit Airport",Parent.Default, SelectedAirport)
so that if the lblMode text is "Edit Airport" it will use the default value from the parent card, which is the name of the item currently being edited. But if the lblMode text is "New Airport" it will add in the value saved in the SelectedAirport variable.
Save and publish your form. It will now be used instead of the default SharePoint form.
So when you create a new item in the list and start entering a few letters into the input field the gallery will be filtered to only show those items starting with the letters you typed.
It took far longer to write out the steps than to actually build it! Do come back with any questions or if you need further assistance with this.
Rob
Los Gallardos
Microsoft Power Automate Community Super User