Issue saving selected items in combo box on an existing table record

[alexleyva]

I have these two tables:

I created a power app using a combo box in my form as seen here:

I am able to retrieve all of my combo box items using this in “Items”

SortByColumns( ShowColumns('Projects.LitigationType', "Name", "ID"), "ID")

I am also able to save on a new record all of the selected items using this in “Update” in the datacard:

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

My problem is retrieving an existing record with the default selected text and re-saving the record.  I have this as in the “defaultselectedtext”.

RenameColumns(Split(ThisItem.Litigation, ","), "Result", "Value")

Here is what happens when I create a new record:

1. Start with blank database: 
2. Add new record: 
3. Display new record: 
4. Select existing record for editing:  
5. Add to existing list & save record: 
6. This is the result: 

Can someone help me figure this out?

[RandyHayes]

@alexleyva 

Change your DefaultSelectedItems property to:

RenameColumns(
    Filter(
        Split(ThisItem.Litigation, ","),
        !IsBlank(Result)
    ),
    "Result", 
    "Value"
)

A combobox will "select" items and yet "not really select them" when you provide a blank record to it.

The Split will result in one trailing record that is blank...so, the above formula will filter it out.

I hope this is helpful for you.

[alexleyva]

Close but no cigar.  With this option, it removes the leading commas but that is not what I want.  When I edit an existing record, I want to keep what was previously selected plus anything new. My old method or your new method, existing data is omitted. If a recall an existing record, not make any changes  and re-save, the field will be completely blank.  Here are some scenarios of what I expect to happen:

1. Edit an existing record with 2 items (defaults with 2 items), add a 3rd one, result is 3 items (2 old and 1 new)
2. Edit an existing record with 2 items, (defaults with 2 items), remove one defaulted item, result is 2 items (1 old and 1 new)
3. Edit an existing record with 3 items, (defaults with 3 items), remove 2 items, result is 1 item (remaining old item)

What is happening currently with your fix:

1. Edit an existing record with 2 items (defaults with 2 items), add a 3rd one, result is 1 items (1 new)
2. Edit an existing record with 2 items, (defaults with 2 items), remove one defaulted item, result is 1 items (1 new)
3. Edit an existing record with 3 items, (defaults with 3 items), remove 2 items, result is no items

[RandyHayes · Accepted solution]

@alexleyva 

Yes, what you are mentioning is a classic scenario where the items are NOT really getting selected by the DefaultSelectedItems (DSI) property properly.

Whenever you see this:

It means that the DSI is not selecting the records properly.

But actually, didn't notice this before, your DSI will never match!  In your Items property you have records with a Name and ID column.  In your DSI you have records with a Value column.

So these will never match!  

If there is no need for the ID from the Items, I would get rid of it.  If you need the ID from some reason in the items records, then you will need to do a table that matches.

So your DSI would become:

ForAll(
    Filter(
        Split(ThisItem.Litigation, ","),
        !IsBlank(Result)
    ),
    With(LookUp('Projects.LitigationType', Name=Result),
        {Name: Result,
         ID: ID
        }       
    )
)

This is not the most efficient but it will give you what you need.

I would suggest rethinking the need for the ID as, if you get rid of that, then you only need Name columns to match, and you can look up the ID perhaps in another location when it is really needed.

[alexleyva]

I changed my DSI as follows:

ForAll( Filter( Split(ThisItem.Litigation, ","), !IsBlank(Result) ), With(LookUp('Projects.LitigationType', Name=Result), {Name: Result} ) )

I removed the ID from it. 

I also change my "Items" as follows:

SortByColumns(ShowColumns('Projects.LitigationType',"Name"),"Name")

It appears to be working now.

[RandyHayes · Accepted solution]

@alexleyva 

If you changed the Items property as you have, then you actually don't need any lookup in the DSI property (it will slow down your app).

DSI should be:

RenameColumns(
    Filter( Split(ThisItem.Litigation, ","), !IsBlank(Result) ),
    "Result", "Name"
)