What's the better approach on updating data using Multiselect listbox?
Application Type
Traditional Web

I have 2 tables, Team and Subteam (where a team can be part of another team). In Team add/edit screen, I can select multiple team using multiselect listbox, and the mapping would be save on the Subteam table. Here's the catch, during saving, I am deleting every mapping of the Team that I am updating and then re-mapping those who are selected in the listbox, which I think is a bad approach. Function-wise it works, but behind the scene I don't think it's the right way. Any idea which approach should I go with this?

I've attached sample oml to better understand


Rank: #459

Hi Kenneth1,

There are quite few approachs you can follow here. If you don't like your current one, you could go for instead deleting all, when saving do one advance query and delete from your table the subteams that  do not exist in your current selected list. And for the selected one's just call createOrUpdate that will handle both cases, instead of the Create has you have there on your OML.

Hi Kenneth,

Try to play around with the List server actions under System.

Or BuildSafe_InClauseIntegerlist in optimizing your code logic.

Thank you and regards,


Rank: #129

Hi Kenneth,

this isn't necessarily a bad approach, I think.  

Are you worried about performance ?

An alternative would be to 

  • not throw all away in advance
  • just start your loop
  • put a create or update in the true branch instead of a create
  • put a get followed by delete in the false branch

The only thing you achieve with this is that you don't delete and recreate the records that were before and still remain selected.  

I think for small tables, this is a trivial advantage.  This table has no attributes, no relationships with other parts of the datamodel, and how many teams will you have in your system, a few hundred tops ?  How often will their relationships among each other change ??  I wouldn't worry about database performance.  

Throwing everything out and recreating everything fresh is a valid approach, and sometimes simpler to build, test and maintain.  But that's to some extend a matter opinion.