Joining two collections with distinct

[ComputerHabit2]

This seems to be very hard to accomplish... I can't seem to understand what is wanted.

I have two collections.  I'd like to join them together into one collection.  I'd like to remove the distinct items.  

HOW?

I'd say more but I'm so confused as to what to say because I've been staring at this documentation way to long and no freaking answers.  Ugh... 

[RandyHayes]

@ComputerHabit2 

Can you explain what you mean by removing distinct items?

Also a simple example of what you have for records and what you expect as a result.

[ComputerHabit2]

I left yesterday in a huff.  To much staring at the same thing and not getting it.

I have a text field I'm storing a semicolon delimited string in.  I'm using  a combo box with it.  My issue is that when I modify the combo box items it is doesn't keep the existing info in the combo box and only saves the added item.  

After working on it I'm sure it has something to do with using SelectedItems in the Update Field of the DataCard.
Update = Concat(cmbRelated.SelectedItems, 'Number', ";")

If I hit save multiple times it will empty the field.  I assume  because I've not selected another item.  This leads me to think I need to take the existing information in the field and combine it with the selected items.  

Also when I'm adding to the field it allows me to add duplicates.  I'd like to either filter out duplicates from the drop down because it was already selected or at least filter the duplicates out after saving.

What I've been trying to do is take the data from the existing field and make it a collection.  Then add the selected items to the collection.  Then filter out those items.  Then save back to the field.

I just can't seem to figure out how to do this.  I'm not sure which functions to use as they all seem to fail for me in one way or another.  So I'm just confused.  

[RandyHayes]

@ComputerHabit2 

So, you need no collections for any of this and it will only overcomplicate what you are trying to do.

My understanding is that you have a Form for your datasource records.  In those records you have a Text column.  That Text column contains semicolon separated values. 

Your Items property of the combobox has some formula (I need to know what the Items property formula is) that contains the values seen in the combobox. 

You want the values that are in the semicolon separated text to be selected when you view an existing record and you want the next values (if any) to be reflected in the text column when you write the record to the list.

Is the above accurate?

If so, then your Items property I need to know as in order for selections to be made on a combobox, the DefaultSelectedItems property needs to contain valid records that match the record schema of the Items property.

[ComputerHabit2]

I think you helped me earlier in my app with how to park the  items into my text column.  🙂  Sorry if I miss something I'm still trying to figure out what I need to say.

Yes I have a text column that is storing semicolon separated items in it.  I'm using a combo box to allow multiple items to be selected.

Form = frmEditRequest
Onvisible = ClearCollect(
colAppaID,
ShowColumns('New APPA List',"appaNumber"),
ShowColumns('Revision APPA List',"appaNumber"),
ShowColumns('Transfer APPA List',"appaNumber")

);

DataCard = related appa_datacard1
Update = Concat(cmbRelatedAppa.SelectedItems, 'APPA Number', ";")

Combobox = cmbRelatedAppa
Items = colAppaID.appaNumber
DefaultSelectedItems = RenameColumns(Filter(Split(Parent.Default, ";"), !IsBlank(Result)), "Result", "Value")

[ComputerHabit2]

I tried using the OnChange of the ComboBox to set a variable that was then used in the Datacards Update.  
I had tried using Concat with both the SelectedItems and the items in the current table.  I just ended up duplicating all items on save repeatedly.  

UpdateContext({    
varAppaID: With(
   {
      varAppaID: Concatenate(requestsDT.Selected.'Related Appa',
      Concat(
         cmbRelatedAppa.SelectedItems, 
         'APPA Number' & 
         "; "
      ))
   },
   Left(
      varAppaID,
      Len(varAppaID)-2
   )
)
})

[RandyHayes · Accepted solution]

@ComputerHabit2 

There is no need for all of the actions that you have going on.  

All you need is to First:

Set the Items property of your Combobox to:

Ungroup(
    Table({Items: 'New APPA List'.appaNumber},
          {Items: 'Revision APPA List'.appaNumber},
          {Items: 'Tranfer APPA List'.appaNumber}
    ),
    "Items"
)

Then set the DefaultSelectedItems property of your combobox to:

RenameColumns(
   Filter(Split(Parent.Default, ";"), 
       !IsBlank(Result)
   ), 
   "Result", "appaNumber"
)

(NOTE: The difference before is that you were never matching a record.  You were using (by renaming the column from Result to Value) a Value column to match the Items...but the Items property has records with only an appaNumber.  So the above corrects that)

Your Update property is correct (except you were referencing a non-existent column - which means you would have had nothing):

Concat(cmbRelatedAppa.SelectedItems, appaNumber, ";")

There is no need for the OnChange of the Combobox.  In fact, you don't want to use the OnChange action on comboboxes as they will execute even when setting the default items selected.

That is it!  It should give you what you need.

[ComputerHabit2]

Thank you very much.  This is very helpful in my studies and worked very well.  

I still need to work on how to filter out all the items that are duplicates or better yet I guess filter out the available items in the combo box.

[RandyHayes]

@ComputerHabit2 

Yes...you had started out your post kind of leaning in that way, but I wasn't sure what you needed to filter out.  So, if you can provide some more detail on that, then I can give you some guidance there as well.

[ComputerHabit2]

Thanks for your reply.

In the combo box when I edit an existing item the choices available contain the existing id's.  This allows adding the same entry twice.  I was hoping to filter out the items already selected to avoid users adding twice.