Cannot get a multi-select to work with a lookup from a sharpoint list

[CameronGo]

I've tried so many variations on this now that I''m totally lost.  I just want to add a column into ListB that references my the Title column from ListA and allows me to select multiple values to be stored with the record in ListB when it is created or updated.  (I also want to filter the Titles from ListA, which is why I'm using PowerApps rather than native Lookup fields.)

The field in ListB I've created, is a Choice field so that I can store the multiple values.  I have my filter working, thanks to an assist from earlier today.  But after I'd gotten my form working the way I wanted, I went to test it and it was only saving a single record that was selected, not all of them.

Since then I've tried using this to get all of the selected values, but it won't let me use this as-is b/c it says the property expects records and this rule returns table values

ListBox2.SelectedItems.Title

I also tried variations using the Choice function for the items, but many variations of something like this always tells me Choices has some invalid arguments and says Name isn't valid:

Choices(Services.Title)

I also tried using the concat function, though I don't think that will work for me since I'm guessing that when you come back in to edit the same record, you won't have the individual selections any longer, b/c they will have been concatenated into a single text field.  Nontheless, this concat statement always stores the selected records with just a set of double quotes:

Concat(ListBox1.SelectedItems, Title & ",")

I thought I was so close, but this is still throwing me for a loop.

[RandyHayes]

@CameronGo 

Are you still stuck on this issue?

Was curious why you would have chosen a Choice column for your values rather than the lookup which you are trying to do in the first place.

I understand you want to filter the lookup for the user and then store the multiple selected items in the column. But you certainly still want the lookup type column in your list and have it allow multiple items.

Let me know that and also if you're still stuck and I can provide some assistance. 

[CameronGo]

Thanks for the offer of help @RandyHayes !  

I don't know that I specifically care that the field type is "Choice" per say, but the default Lookup field type doesn't let me filter the source list the way I need to, which is why I started with something else that lets me select multiple values.

The use case is to be able to select a list of available Services (from a filtered Services list) to be included a User Access Request form. Once the UAR is submitted, it is updated a couple of times as it goes through different stages of workflow and the list of Services could be modified.  I saw some solutions using CONCAT into a TEXT field, but that seems like it would be clunky if the list of Services may be updated at points during the workflow.

I'm hoping I'm just missing something fundamental and the solution is something simple.

[RandyHayes]

@CameronGo 

So the reason I started to get a better idea on your column was all about what you might be hinting at - a changing list of available Services.

In this case, I would recommend using a seperate list as that will give you more flexibility over the alteration of Services over time.  If that is relatively static in nature and will not have changes, then at least consider a Site Column to keep the two places connected.

With a Choices column, yes you can use the Choices function, but you can also filter that as needed.  And, of course, with a seperate lookup list, you can filter that however you like.

Concat is not going to be effective for you and will cause you more issues in making it work.

So, as we look at your situation a little more - let me know these things:

1) Where are you now on your column definitions?  Do you have lookup or choice fields?

2) When you stated "it is updated a couple of times as it goes through different stages of workflow and the list of Services could be modified."

Are you stating the the Services list could be modified, or that the services associated with the UAR could be modified?

Another option to consider in this case, rather than making a column hold multiple values, is to create a ancillary list that would serve as a cross-reference (one-to-many) relationship for the UAR to the Service.  Just throwing that out there as an option that I use often for this scenario.

[CameronGo]

Let me see if I can answer your questions properly:

The list of items in Services can/will change over time; services are added as "Active", or marked as "Retired" as an example, but this is hardly a daily occurence once it is setup and running.

The UAR list, on the other hand, will have new records added as a regular course of business.  When a new record is added it may be added with a list of 10+ services selected, but then it could be edited to add 2+ more, or remove a few before it is approved.  Also, the record will be edited to add approvals, which is why I wouldn't want the "list of selected services" to have to be reselected each time a UAR record is edited.

Here are the two list definitions, the "Services" list is the actual list, the "Test List 2" is where I am testing these behaviors before I actually modify the UAR list.  You will also notice on the Services list I created 2 calculated fields, which were created as a partial work to this lookup issue I'm trying to solve with PowerApps.

Services listTest List 2

[RandyHayes]

@CameronGo 

So, for your consideration, is the following scenario:

UAR List:
   Title - Text
   <any other columns you want that are specific to UAR (singular)>
 
Services List:
   Title - text
   Active - Yes/No
   Description - Text
   <any other columns you want that are specific to Service (singular)>
 
UAR_Services List:
   UAR_ID - Numeric (or could be done with lookup)
   ServiceID - Numeric (or could be done with lookup)
   Status - Choice
   Approval - Choice (or whatever else would work).

In this scenario you would have one record for your UAR.  Each UAR could have 0 to many UAR_Service records.  Each UAR_Service record references one UAR and one Service.

Formulas to consider in this scenario:

   A list of all the UAR_Service items (dynamic to each UAR):

     Filter(UAR_Services, ID=UARID)

   A list of all the services associated with a UAR (would give you service records - should be considered static):

     Filter(Services, ID in (Filter(UAR_Services, UAR_ID = UARID)))

Think that one through a bit and see if it helps with your scenario.  This will be much simpler than trying to deal with back and forth mult-select lookups and choices.  And it will give you more flexibility.

It's just a thought though...the other way is still an option, just need more work.

[CameronGo]

@RandyHayesthanks, I think I see the idea, which is the same sort of structure I would generally use in an RDS, but I'm not at all sure how to bring together such a structure into a single SharePoint form.  In the proposed solution below, is that even possible?

Ulimtately I don't think it matters to me in particular how the lists and relationships are defined, but I do think all of the changes and interactions need to be done in a single form.

It is odd to me; however, that I can create a Lookup field on my form to reference the Services, which works just fine, but I just can't filter that list.  The Items on my test Lookup field just reference itself??  it seems like the most ideal solution would be to be able to modify this Items selection with filters rather than re-inventing the Lookup behavior.  Is that not possible?
Opened test lookup field in PowerApps

[RandyHayes]

@CameronGo 

Yes, this is exactly what you do...you would customize the DataCard in this case.  Unlock it and then set a filter formula on it to gather your items from the other list - or lists.

The only real difference you will experience is that with the One-to-many "UAR_Services" list, you will most likely not have someone interact with it (i.e. no EditForm for it).  So, you will simply put in a Patch function in your submit or, better yet...

1) Have a Gallery of all the Services associated with the UAR  (Items = Filter(UAR_Services, ID=UARID))

2) Provide a button/icon for adding new services.  Many variety of ways to do this, but at the end of the selection of the service - Patch(UAR_Services, Defaults(UAR_Services), {UAR_ID: currentUARid, ServiceID: selectedService.ID})

3) Provide a delete button/icon in each row of your Gallery : OnSelect : Remove(UAR_Services, ThisItem)

This would give you a list of all services and provide methods to add or remove them as needed.

See if that sparks some thought.

This is a bit of pseudo formula for it:

SubmitForm(UAR_EditForm);

[CameronGo · Accepted solution]

@RandyHayes  - I think I found a solution that I'm most happy with.  I'm still testing to make sure I'm not missing something, but basically I started by creating a Lookup field that references my Services list.  This functionally works perfectly except it doesn't allow me to filter the Services list the way I want to.  From there, I modified my Items formular on that lookup field to add a filter referencing my Services data source.  The resulting formula looks like this:

Filter(Choices('Test List 3'.'Test_x0020_Lookup'),Value in Filter([@Services], Status.Value = "Active", "Corporate" in qble.Value, Or(Service_x0020_Type.Value = "Core",Service_x0020_Type.Value="Enhancing")).Title)

I honestly already have forgotten where I got some of the Filter components above (like what is qble and Service_x0020_Type???)

As far as I can tell; however, that seems to work and behave exactly like I was hoping.  Do you see any reason not to use this approach?

[RandyHayes]

@CameronGo 

I would continue on any approach that you consider to be relevant to your app.

My only concern would ride around the concepts that I outlines in previous posts.  I would still consider that a separate list to track services selected to a UAR would be the most simple and easy to mainatin down the road.

There is no doubt that you can put together formulas as needed to filter, choose, lookup and more around a particular set of information, but sometime you look at all the work you have to do to get there, and you discover that there are simpler ways just by refactoring your data source a little.

Your challenges currently will be with populating the ComboBox properly, Selecting the items that have already been chosen on existing records, recording it back properly to the datasource and dealing with the multi-select properties of the combobox.  All absolutely obtainable...but perhaps more effort than you want?  This is, as the designer of your app, your consideration to decide.

So, keep at it and feel free to post back whenever you get stuck.