Sharepoint list form with combobox choices based on other sharepoint list, allowing new entries in the same combobox field

[Maik7]

Hi everyone, 

I´m working on a customized form for a sharepoint list.

 I´m trying to realize the following structure:

The target list hast a column (may be text or choice) with 'reasons' (free text) that users have to put in. They should use a combobox showing all reasons that have formerly been submitted. I set this up with a 2nd sharepoint list (source list) containing the reasons (may be choice or text). 

If a user starts typing in the combobox, the reasons start to filter and she can choose an existing reason if a fitting one is already in the source list. If not, I want her to finish typing a new reason in the same combobox field and save this search term as a new reason in the source list.

This topic is closely related to Update-SharePoint-Choice-Colum-with-Text-Input. Still, I would like to avoid having two fields (avoid using "other" when none of the existing choices fits and then typing a new reason in a 2nd field).

 @RandyHayes maybe you have an idea for this, too?

Thank you for your help 🙌

[v-jefferni · Accepted solution]

Hi @Maik7 ,

In this case, using Text columns in both lists is preferred. I assume you have the other list where listing all the reasons in Title column. In customize form, the combo box Items property can set to the other list's Title column: 

List2.Title

and then in Update property of the Data Card, use search text if nothing is selected in the combo box:

If(IsBlank(ReasonComboBox.Selected), ReasonComboBox.SearchText, ReasonComboBox.SelectedText.Value)

after the submitform function, add a new row to the other list:

Patch(List2, Defaults(List2), {Title: ReasonComboBox.SearchText});SubmitForm(SharePointForm1);ResetForm(SharePointForm1)

Best regards,

[Maik7]

Hi Jeffer,

thank you very much for your suggestions 👍 it works fine when adding new items to the first list. Only thing is, it seems I have to use the old version of the combobox, as in the newer version the search term disappears as soon as I leave the field. 

Anyhow, if I try to edit the item (open the form via build in edit button of the SP List), the value for 'reason' is not displayed in the combobox (it shows the InputTextPlaceholder). Any idea how to fix this issue?

Form Item is set to 

If(var_FormType="New"; Blank(); SharePointIntegration.Selected)

var_FormType is set on_new/on_edit/on_view. 
Datacard Default is set to ThisItem.Reason

Combobox DefaultSelectedItems is [Parent.Default]

BRs Maik

[v-jefferni]

Hi @Maik7 ,

Since it's the other list as the Items of the combo box, in edit mode it needs to match parent.default with one of the records from the other list. So, in DefaultSelectedItems property, you need below formula if this Reason column is required:

LookUp(List2, Title = ThisItem.Reason).Title

if it's not required, it would be much simpler:

{Title: ThisItem.Reason}

Best regards,

[Maik7]

Hi @v-jefferni , 

thx again, it works fine with a few adjustments: It seems I need to omit the ".Title" at the end. So I´m only putting in: 

LookUp(List2, Title = ThisItem.Reason)

Also, for the update property I had to use 

If(IsBlank(ReasonComboBox.Selected), ReasonComboBox.SearchText, ReasonComboBox.Selected.Reason)

And the patch function you suggested in your earlier answer seems to clear the searchterm, so I had to store the term in a variable, submit the form and then patch the 2nd list. 

One more question: is there a way to make the solution from your 2nd answer (showing selected values in edit mode) work with a field that allows multiple selection? I tried to concat all selected options into a single string and store this one, which is working fine. Problem is when editing, I cannot change the selection. I´d like to have the original selection selected on edit and then I`d like to change it (I´m using a modern comcobox here, and no new values are allowed in this column). 

BRs Maik

[v-jefferni]

Hi @Maik7 ,

You can try below formula:

Filter(List2, Title in Split(ThisItem.Reason,","))

if list2 has more than 500 items, to avoid delegation issue, use below formula:

ForAll(Split(ThisItem.Reason,","), LookUp(List2, Title = ThisRecord.Value))

Best regards,

[Maik7]

Hi @v-jefferni , 
works well with again a small adjustment: I had to omit the "ThisRecord" part. So this code worked for me: 

ForAll(Split(ThisItem.Reason;","); LookUp(LIst2; Title = Value))

Thx for all your support 👍