multi select using lookup field

[Anonymous]

Hi, 

I am having trouble submitting a multi-select value.

All data is in CDS.

Here's my screen.

This is a screen to see the sales rep's target store.

All sales rep has several target store.

So, I would like to submit a multiple value (target store) to "sales_store".

"sales_store" is made with a lookup field form another entity.

"sales_store" is a combo box and the setting is following.

 

Can you please help on this?

Thanks and Regards, 

[EricRegnier]

Hi @Anonymous,

What type of field is store on your sales rep entity? It cannot be a lookup nor an optionset because it can only contain one value.

You can use Multi Select Option Set. The optionset item are integers and the selection stored in your parent entity (i.e. sales rep) is a string delimited by a comma. E.g. "123,456,789". This would required an integer/whole number equivalent on your store entity which you would then build your string delimited selection.

The other option is with a many-to-many (N-N) relationship between sales rep and store. You can leverage the out-of-the-box N-N relationship or with another custom junction entity with 2 lookups, one on sales rep and the other on store. The custom junctions is more flexibility as you can specific more properties of the relationship whereas out-of-the-box N-N you can't. There are also other limitations with out-of-the-box N-N but it's more of an advanced topic...

Hope this helps...

[v-jefferni · Accepted solution]

Hi @Anonymous ,

I assume that the sales rep entity is the data source of the gallery. You made the “sales_store” be a LookUp field which could only store a single value of another entity.

As you said all sales rep have several targer stores, my question is would some rep have a same target store? If no, the scenario should be a One-to-Many relationship.

This means one rep have multiple target stores and the LookUp field should be in the entity which may be named as “Target_Stores” and the lookup field could be named as “Rep_owner” for e.g..

On the other hand, if some reps have one or more same target stores, it becomes a N:N relationship.

But back to the beginning, the 1:N relationship could not help you achieve the goal.

If you only want to choose stores and display their names in the gallery, relationships are not necessary. Below is an easy solution.

Assuming we have two tables, one called “Target_stores”  as table1, another called “Sales_stores” as table2. Table 1 has columns “sales_name” and “sales_store” with multiline text. Table 2 has columns “store_name” and “address”.

Now we have a gallery and an edit form with data source table1. Replace the text box in the form’s “sales_store” card with a combo box, set the Items property of the combo box as “Sales_stores”.

What we want is in the gallery we can see all the sales stores assigned to the sales rep, so we set the Update property of the sales_store card of the form to:

Concat( ComboBox1.SelectedItems,store_name ,",")

This helps convert the table value to text, which need to be saved into the sales_store column of table1. If multiple stores be selected, names separated by comma. Since the field type of “sales_store” is multiline text, using Char(10) to replace the comma would be better and more clear to display store names in the gallery.

In the combo box, set the DefaultSelectItems to:

Filter(Sales_stores,store_name in Gallery1.Selected.sales_store)

This make the combo box display proper stores assigned to the sales rep selected in the gallery.

If there be any further plans of using the target stores in your scenario, creating a N:N relationship would be the way.

First, relate the two entities on the PowerApps portal, and don’t forget to refresh the data source of the entities in your App.

Second, set the OnSelect of the Save Botton to:

ForAll(

    Gallery1.Selected.Sales_stores,

    If(

        address in ComboBox1.SelectedItems.address,

        "",

        Unrelate(

            Gallery1.Selected.Sales_stores,

            ThisRecord

        )

    )

);

ForAll(

    ComboBox1.SelectedItems,

    If(

        address in Gallery1.Selected.Sales_stores.address,

        "",

        Relate(

            Gallery1.Selected.Sales_stores,

            ThisRecord

        )

    )

);

SubmitForm(Form1)

This is adding and deleting the relations between the seleted sales rep in the Gallery and the selected stores in the Combo Box.

And the Text property of the Label in the Gallery which you designed to display the names of rep’s  target stores:

Concat(ThisItem.Sales_stores,store_name,",  ")

Now we have a table “Gallery1.Selected.Sales_stores”, and display all fields of the table’s store_name column in the Gallery.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it more.