Within the one SharePoint List called 'Specialist Database - NEW', I have a mixture of Practices and Specialists (and other details that are irrelevant). The fields used for these are: 'Practice' and 'HCP' respectively.
For each Practice I also have a field to show which Specialists work in that Practice and vice versa I have a field for Specialists to show which Practices they work in. I realise this is a bit circuitous but it is necessary due to the 1 to many relationship each have with the other. These additional fields have been created using the Lookup Column type in SharePoint using the Practice or HCP columns as the source data. Note also that multiple entries are allowed in the lookup columns.
In the generic SharePoint list data entry form (either New Item or Edit), as you type, SharePoint searches and brings back results showing any relevant string - ie if you type Derm, it would bring back 'Dermatologist Professionals' and 'Skin Dermatology Specialists' as the string "derm" is somewhere in the result.
The issue is I have created a form on Power Apps as I want to make certain fields visible when entering data for Practices vs Specialists and in this form, the search function in this lookup field only works by searching for the data entered at the start of the results. In the example before, when entering "Derm" the only result that would come back would be 'Dermatologist Professionals' and 'Skin Dermatology Specialists' would not appear.
I have looked for other solutions on the community pages and it seems that one can use the Search function to do this, but I am just not having any luck with constant errors... so any help or guidance on this would be greatly appreciated.
To make things easier I will provide some screenshots for one of the cases - A Specialist (HCP) entry that I am trying to enter / search for the Practice they work in.
The source data field (ie all the practices are entered into this field) is called Practice_DataCard1:
The Practice lookup field (ie string is entered to find the corresponding practices are entered into this field) is called Practice(Lookup)_DataCard1:
The current function associated with the lookup field is:
Choices([@'Specialist Database - NEW'].'Practice (Lookup)')
As mentioned I was trying to replace the function by using the Search function in the form of:
Search(Choices([@'Specialist Database - NEW'].'Practice (Lookup)') ,DataCardValue9.SearchText,"Value")
But this does not work and gives errors.
Any support or guidance would be greatly appreciated as I have spent a lot of time trying to make this work so the entry searches and brings back results where the string is in any part of the result.
Thank you very much!
