Searching another SharePoint list from my edit form

[Jandal_Power]

Context -
My first app, has an EditForm that gets filled out by the end user. Uses the SumbitForm function to add the details into the SharePoint list (its data source "IFP - Purchase Order"). This information is used by an Admin for another system.

 
Data source 

What I am trying to achieve -
I want a field to be searchable by a word prompt, for example "Cable, Light, LED, Hardware, etc" and show the items available from the "Item" Column below. Maybe also the Category that Item belongs too, but not necessary if too tricky.

The challenge I can't seem to overcome - 

This data is in another SharePoint list. (Purchasing-Familes-Codex)
Even though I want the user to search for "Cables, Light, LED, etc" and see all the possibilities to choose from, the only real data that is required to be added to the original data source "IFP - Purchase Order" is the corresponding "SAP Code" from the Purchasing-Familes-Codex SP list.

What I have tried in the past was a lookup field that searched the Item column and pulled the corresponding SAP Code.
But the search was terrible, the word Cable only showed two options that begun with Cable rather than any result that contained the word "Cable".

Any help would be greatly appreciated as I am trying to make the app more user friendly and the data more accurate for the admin.

Extra Info - 
The app was created via the Auto option via SP list. I know it wasn't the best idea but it seemed like a good idea at the time.
I have since been trying to bend the app to my will, I have massive knowledge gaps being a novice.
I have since realized that people use filtered searches, variables, delegations, really cool and better ways of doing things that I haven't been able to achieve yet.

Regards,
Jandal_Power

[v-qiaqi-msft]

Hi @Jandal_Power,

Do you want to retrieve SAP material code in the form based on the key words you used to search from the Items column in Purchasing-Familes-Codex?

Actually, it is complex.😂

However, I want to confirm with you that the TextInput you used in the Form could only display one SAP material code generally. If you want to select SAP material code, you need to use a ComboBox, so, please tell me:

1). where and which control you search for "Cables, Light, LED, etc" 

2). you want to search for "Cables, Light, LED, etc", then retrieve the SAP Code(in form) based on the word, right?

Currently, only ComboBox for SAP material code(in form) could help you capture corresponding different SAP Code(Purchasing-Familes-Codex) based on the search word.

But you need to tell me where you want to seach this key word.

[Jandal_Power]

Hi Qi,

Do you want to retrieve SAP material code in the form based on the key words you used to search from the Items column in Purchasing-Familes-Codex? - Yes

However, I want to confirm with you that the TextInput you used in the Form could only display one SAP material code generally. - Yes If you want to select SAP material code, you need to use a ComboBox, so, please tell me:

1). where and which control you search for "Cables, Light, LED, etc"  - I can add a new ComboBox called "SAPCode" and add the corresponding field in SharePoint if that is the best way to do it?

2). you want to search for "Cables, Light, LED, etc", then retrieve the SAP Code(in form) based on the word, right? - Correct

Currently, only ComboBox for SAP material code(in form) could help you capture corresponding different SAP Code(Purchasing-Familes-Codex) based on the search word.

But you need to tell me where you want to search this key word. - I will create a ComboBox called SAPCode

Regards,
Jandal_Power

[Jandal_Power]

[v-qiaqi-msft]

Hi @Jandal_Power,

Thanks for your detailed info.

Okay, I get it, so we have one TextInput control(SAP material code) to input key word like "Cables, Light, LED, etc" and we have a ComboBox to select SAP Code.

Actually, I guess that you create this SAP Code column as a LookUp type, which retrieves SAP Code from Purchasing-Familes-Codex list. 

Based on above, here is a simple demo for your reference.

Set the Items property of the ComboBox('SAP Code') as below:

Filter(Choices('IFP - Purchase Order'.'SAP Code'), Value in Filter('Purchasing-Familes-Codex',
SAP_Material_Code_DataCardValue.Text in Item).'SAP code')

Note that I will enter key word in the textinput control from the SAP Material Code data card.

Or, you could ignore this field and choose to search within the searchtext of the SAP Code ComboBox, well at this situation, you just need to replace the SAP_Material_Code_DataCardValue.Text with DataCardValue20.SearchText.

Try and keep in touch.

[Jandal_Power]

Thank you, I have had some success.
I created the SAPCode column in the SharePoint data source (IFP - Purchase Order)
as a look up to the SAP code column in Purchasing-Families-Codex.

I went with your second option only using the ComboBox as the search, but with search turned "on", I can't see any results. With the search feature selected as off I can see a list of the SAP code's.

Filter(Choices('IFP - Purchase Order'.SAPCode), Value in Filter('Purchasing-Families-Codex',DataCardValue20.SearchText in Item).'SAP code')

Trying to figure out what I have done wrong. I was also trying to DisplayFields as Item and SAP code so the user can see what Item belongs to the SAP code and search by the item. When item is selected, the SAP code is written to the EditForm.

Regards,
Jandal_Power

[v-qiaqi-msft · Accepted solution]

Hi @Jandal_Power,

The issue you encountered is related to delegation.

For that you want to search key word based on Item column, I use the operator "in", which could filter records if the key word string is contained in the Item column.

To solve this issue, you need to use another TextInput to do the search thing so that ComboBox will filter based on the TextInput text.

Here is the testing result.