This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

multi-relational field entry order

The selected values from a multi-relational and multi-selection fields are saved in the database in the alphabetical order. Is there a solution to be saved in the entry order?

The version of SBM I'm using is 11.7.1. The order in which the selected values in the multi-relational field are stored is the alfabetical one, from SBM 11.4 (please see D24348). My client want to see the selected values in a multi-relational field in the order of entry. It is very important because this field contains the name of packages to be deployed and the order is crucial. Maybe an appscript or a javascript can be used to resolve this. Any idea will be appreciated.

  • Suggested Answer

    Hi, at its lowest levels of storage in the database, I am uncertain if there is actually any real order (*). The user typically sees these rendered in an alphabetical order in the UI however. For a multi-relational field, you could use the Relational Grid widget to change the observed sort order, but it would have to be defined on attributes unique to the selected records - not the record having the multi-relational field.

    You may benefit from a Package Order table concept, where this new table can have an order attribute and each record be related to the original package table. This then could be coupled with the Relational Grid widget.

    Other that that, I think you would be looking at creative ways to save an ordered list in something like a memo field and rendering it with custom form and html/javascript technologies to show the values sorted and possibly to allow editing of the order.

    (*) There would be a table whose ids increment every time a new value is added to the field. But this is more about the order the field was updated over time, not a user-definable sorted value concept. And if multiple values were to be added in one update transition, there is probably no guarantee which is considered first.

  • This is exactly what I was thinking.  If, however, all items in the multi-relational field always get the same order then you can add an attribute to the related table that can be used to sort the selections.

    In case of a PackageOrder, as Garry suggests, it is more complicated.

  • Hi, thank you for your suggestion. I'll try to use a javascript and see how it works.

  • You will definitely need a separate table with order information.

    Here's what you can do, but it's a waste of time because SBM will out-smart you in the end .....

    In the SUBMIT handler on a form, grab the contents of a Multi-Select field with the JS library "GetMultiListValues" function.  This will return an array of TS_ID values of the selections from the Multi-Select field in the order specified by the user.  Yea!  Then convert the array to an SBM CSV string and save it into a "Shadow" text or memo field.

    When the Submit happens and the form returns control to the DLL, SBM will re-order the selections of the Multi-Select field into numerically increasing ID order (foo!)

    In a post-transition AppScript, read the shadow field containing the selections in the user-specified order and write this to the Multi-Select field.  Unfortunately, it looks like the AppScript function "SetFieldValue" re-orders these ID values back into numerically increasing ID order (foo!).  So all that work turns out to be a waste of time.  foo.

  • You are right. I've saved the values selected in the multi-relational field in the order specified by the user in a memo field. In the post-transition AppScript I used SetFieldValue but nothing, the values are saved in the alphabetical order. I tried with an external vbscript too. A vbscript that takes as argument the id of the item, updates the value in the database in the right order. Launched from outside of SBM, it works fine, but when I tried from SBM by using ret=Ext.NewTaskWait( BatchUpdateFile, Trim(SerenaId)), the result is the same: the value in the database is in alphabetical order. I don't think if it's something to be done here. The decision to save these values in the alphabetical order was made ( I don't understand why) and I don't think there is some configuration were to decide the order (entry order or alphabetical). At this point, I have to change my perspective. My client final goal is to have the deployment tasks created for the selected values in the multi-relational field (packages) created in the right order (entry order and not alphabetical one) . Until now I read values from the multi-relational field, but  now I try to change it and read from the memo field that contains the right order (treating it as an array).