Two columns from DV to populate a combobox

[lalford09]

Hi, I was hoping someone could help.

I am building an app and there is a variable called var_ParentID that is used to find specific items from a DV table in the ID column. 

I have a form that I have added a combo box to, and I need this combo box to do a few different things.

1. My Dataverse  table has 3 columns that are important. If the ID column = var_ParentID, then pull through all the items from Col1 and Col2  into the combo box. However the items can be separated by a semi colon in data verse, and I need the items from Col1 and Col2 to pull through as individuals into the combo box to allow the user to de-select/re-select as and when is needed. When the form is New, it needs to pull through all the items found, and then when the form is submitted, it will populate Col3 with the remaining selected values from the combobox. When the user then loads that item back up by selecting it from the gallery, the combo box will be populated with the items in Col3 instead of the concat of Col1 and Col2.

At all times however, they need to be able to add/remove items from Col1 and Col2 into this combo box, so I'm thinking a Split or Concat may be the way forward, but I can't quite get it right. 

Hopefully this makes sense, but any questions, let me know!

[FLMike]

Hello @lalford09 

Let me see if I can help you.

Scenario New:

Question: If its new, where do you get the data to populate the combobox since Col1 and Col2 of course are blank.

You stated: When the form is New, it needs to pull through all the items found,

But there wouldn't be anything to find, there isn't any data in Col1 or Col2 because the row is empty. So where would we get it for New

Scenario Saving New:

Assuming we figure out what to put in the combobox, and we want it all to go into Col3 and assuming it will use a semi column delimiter (you didn't say)

And let's assume the ComboBox is called - MyComboBox (yes its Ingenius I know lol)

// to Concat them do this
Left(Concat(MyComboBox.SelectedItems, Value & ";"), Len(Concat(MyComboBox.SelectedItems, Value & ";") -1)

And that above value is what you patch for that Col3.

NOTE1: I am removing the last ; as its at the end and will cause a blank value coming back into for Edit mode

NOTE2: that in the NEW scenario, NOTHING ever went into the Col1 and Col2 because you said it goes into Col3.

Scenario Reload(Edit):

Since you said its a form. I will use a form to demonstrate

Same ComboBox called MyComboBox (yeah sue me lol)

First you need to unlock it, because its going to want to just load what is in Col3 as a string

Then you can change the items

Set MyComboBox Items to 

Split(ThisItem.Col3,";")

NOTE: I think this is flawed. If you only load what is in Col3 they can only REMOVE things, not add them back. So how do you account for that fact that there could be 50 options, but you are only going to show 3 since thats all they saved. They will never be able to add the other 47, unless you add a button or something to the form, so they can "add" the stuff you cant see.

Also,

Personally I don't ever see how Col1 and Col2 are every used. For new records they are blank. For reloading records you are pulling from Col3. Even with existing records, you would expect them

Sorry I am off my rocker or your explanation is lacking hehe. Could be both 😉

Ok I have answered them all just as you wanted them so I'd be grateful if you marked it as Resolved and maybe even a thumbs up.

As you try to implement it and if you decide oh crap, I did ask for the wrong stuff, then feel free to come back, but as of now its what you asked for 🙂

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[lalford09]

@FLMike Ah sorry, I should have been more clear, when the form is New, the two combo box items for Col1 and Col2 are populated from a different DV table that will have already been populated, which is how there will always be items pulling through!

Does this change anything?

[FLMike]

@lalford09 

Yes and Possibly and its more of a headache. (just in general). As this is really turning into a lot of time (please understand I try to answer hundreds of questions) and we dont get paid, so when people leave stuff out the back and forth can be killer of time, plus most people leave without ever giving credit to the answers by marking answers Resolved etc,

So I will pose these and then answer so please make sure you aren't missing anything as you answer.

Questions:

Are the Other DV Table values also concatenated, so when you put the data in Col 1 and Col 2 does it also need to be split?

For new stuff it's not so much of a problem really. If you have to split the data from the other table, just like I showed you for Col3 its not a big deal.

However, when you go to Edit, this is when you have other things to think of.

1). If I am loading a record and you have Col1 and you have Col2 and you have Col3 in the gallery, on a row.

Now the person wants to Edit it.

They click the gallery and go to the Edit form.

--Are Col1 and Col2 fully populated (always), or when you add something from Col1 or Col2, to Col3, do you expect that item to be removed from the ComboBox, thereby needing you to update Col1 or Col2's concatenated values.

--Lets say that yes, you want to do that, because you dont want the person trying to add something from Col1 more than once

--Fantastic. Nope, super headache. Because you removed it from Col1 and stuck it in Col3. Now the person says, ICK OMG total mistake, remove from Col3... Umm ok.. but which Col1 or Col2, do I put it back in? How do I even know.

Do I know how I would solve it sure, but that's not why I am here to solve everything lol.

Question is, do you really know what you need help with. I gave answers on how to do what you asked. But I think you may need design help, I could be wrong.

But your overall answer is

1. For new you need to Split the other tables data JUST like I showed you for Col3 and load it into the items of col1 and col2

2. when someone loads an existing record you need to remove whatever is in Col3 from COl1 or Col2 so it cannot be added again and then saved. 

You didn't ask for help on removing stuff or how to do it, so ill simply say you need to make sure you Unlock Col1 and Col2 so you can Split the stuff coming in, and you need code to concat them in your Patch statement, as again, SubmitForm wont do that.

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[lalford09]

@FLMike Yes I do appreciate the help and don't worry, I always mark as resolved and give a thump up!

I will answer your questions, then go back and try to build it in and come back if the solution worked:

Are the Other DV Table values also concatenated, so when you put the data in Col 1 and Col 2 does it also need to be split? - Yes they are also Concatenated so would need to be split.

1). If I am loading a record and you have Col1 and you have Col2 and you have Col3 in the gallery, on a row.

Now the person wants to Edit it.

They click the gallery and go to the Edit form.

--Are Col1 and Col2 fully populated (always), or when you add something from Col1 or Col2, to Col3, do you expect that item to be removed from the ComboBox, thereby needing you to update Col1 or Col2's concatenated values.

--Lets say that yes, you want to do that, because you dont want the person trying to add something from Col1 more than once

--Fantastic. Nope, super headache. Because you removed it from Col1 and stuck it in Col3. Now the person says, ICK OMG total mistake, remove from Col3... Umm ok.. but which Col1 or Col2, do I put it back in? How do I even know. - For this, I get what you are saying, however when an item is selected for Col3, I don't want it to be removed from the column it was selected from as that is an entire table on its own that is used at other parts of this app. 

I know this is quite a difficult ask that I need, and I'm not sure if it is even possible so I can understand the frustrations.

[lalford09 · Accepted solution]

@FLMike 

I found a way to do it, I use this on the combo box item

Split(Concatenate(LookUp(Table, ID= varParentID).col1, ";", LookUp(Table, ID= varParentID).col2),";")

and then on the Update

Concat(MyComboBox .SelectedItems, Value, ";")