[email protected] - Monday, September 12, 2011 4:12 PM:
I am working with a dropdown list. The list gets its values from a list item. The list item has label and value pairs, which in this case are, for example, "Columbia" (a city name) on the label and "01" as the corresponding value. ARAS is taking that numeric value, "01", and somehow behind the scenes translating it back into a string value, in this case back into the string "Columbia", and saves that value (not the one from the label). This seems to be a built-in function of ARAS since I don't see any methods attached to the dropdown list. Is that true? What is the benefit of doing it that way? It seems very inefficient.
To provide some context about why I'm asking, I'm grabbing the user's selected value from the dropdown list via javascript and then doing some work with that value. Since I don't know how ARAS is finding the string value that corresponds to the numeric one I can't do that conversion myself, so at the moment I'm simply using the text from the label as my value. I'd rather just change the list to have the string values I want, but another user told me ARAS needs that numeric value for something. (They didn't know what, though.) If someone could shed some light on this I'd appreciate it.
-Jeremy B.
Brian - Tuesday, September 13, 2011 8:41 AM:
Hi Jeremy,
In a default situation Aras stores the List Value not the list Label in the actual database table.
You can find the list value/label pair by looking at the "List" item type and seeing how it is put together. The List item type has a Null Relationship called "Value". This has properties "value" and "label".
So you can retrieve the list Item with:
var lst = this.newItem("List","get");
lst.setProperty("name","Name of list");
lst = lst.apply();
Then you can retrieve all the "Value" items that are related to the List since you now have the "Source_id" of the Value items. From this you retrieve the actual Label or Value depending on which you have or want.
If Innovator "needs" the 01 for something then there must be some code written in one of the Client or Server events that uses this value. Since Innovator doesn't care what you put in your own lists as long as the value is less than 256 chars long.
How do you know in your scenario that the "Columbia" that is being saved is not from the Label?
Hope this helps.
Brian.
[email protected] - Tuesday, September 13, 2011 9:00 AM:
Hi, Brian:
Thanks for replying. The reason I think it is storing "Columbia" instead of "01" is that when I view the form's values in a grid, after saving (I hope I'm using the right terms for things) it shows up as Columbia. This dropdown list also has several options that don't have any values associated with them, it's just blank, and when I select those options and save I can only see a blank (a null) in the grid. If ARAS was saving the label value then the string from the label would be seen in the grid.
As I've been dealing with this I remembered something about how ARAS can store a number in the db but display some corresponding value instead. Does that ring a bell? I think the example I'm thinking of had to do with months. They were stored as numbers instead of strings, and that way they showed up in order in the grid. If you sort the numbers one through twelve they go in numerical order, but if you sort the strings January through December you get the wrong order, so they were stored as numbers but displayed as strings. Does that make sense? So they are stored as numbers, but when you look at them in a grid they show up as strings, January, February, etc. I'm wondering if something similar is going on here.
In any case, getting the actual list that feeds the dropdown won't help because the list has numbers for values, not place names. If it was up to me I'd just change the list to have strings as values, but there is another user here who insists that ARAS needs it to be numbers. He can't explain why, but he's certain of it. For now I'll just use the values from the labels, but does what I'm saying about ARAS displaying something different than what's stored in the db sound familiar at all?
Thanks
Jeremy
*Also, here are some real values from the list:
eric_h - Tuesday, September 13, 2011 6:33 PM:
Jeremy,
Maybe this will help a little. Lists are defined within the 'Lists' item type as found in the admin folder in the TOC (left-hand column.) I don't know exactly how the code works, but Aras dynamically generates a drop-down list whenever you apply a 'list' property somewhere based upon the list definition as stored in the database. Here is what matters: the three columns you list above are important:
Label: the name of the list item as it is displayed in the drop-down list. Also, this is the value that is displayed in an unlocked drop-down field or grid table. The value is determined by the value to label mapping. More later.
Value: this is the value that is stored in the database. You see, lists are not an inherent relation database data type. Just not possible. In your example above, if you picked Columbia then 02 would be stored in the DB. When you had a list property in a form or grid, that same 02 gets translated to Columbia again. ** Contrary to what you have been told, the value stored in the database is stored as a string. I am actually pretty sure it is stored as a 64 character NVARCHAR string. **
Sort Order: you can have the drop down list sort in any order you want. Label and Value do not really matter. The sequential order of Sort Order matters. This must be a number. It is stored as a integer in the database.
So when you have Label items that have no corresponding Value assigned, when the form/grid does a look up, it has nothing to match against. That is why you see no values returned under the circumstances you mention above. Furthermore, don't quote me on this, but I bet you anything if you later change the Values assigned to Labels, you will not get back the labels you had originally assigned. Aras will not dynamically also update or change the existing database values, and the mapping will no longer match. You might get something back though (not sure) - the original values as stored in the DB (as 64 character strings) that could not map to a corresponding Label, because that mapping no longer exists.
So if you want to have Labels and Values be the same thing, go ahead. Values are stored as strings (Sort Order is numeric.) And when the Labels get generated to display in a form field or grid, you'll get the same value. But there is still mapping happening, it is just the same label matches the value.
Let me know if you want any further clarification.
Eric
[email protected] - Thursday, September 15, 2011 1:38 PM:
Thanks, Eric! (I love your photo btw, I remember that guy.) That does help. I knew some kind of mapping was going on there. Any idea why? It seems really inefficient to store one value, do a lookup and display a different value. Why not just store a meaningful value in the first place? That's like if I wrote this post in French then took a French dictionary and translated it back into English so you could read it. Why not just write in English?
In any case, if it is just mapping to the label that solves my whole problem. I thought I was going to have to figure out what table ARAS was using to look up the string that corresponds to the number, then access that table and get the right value myself. It sounded like a big hassle for no apparent reason. However, I can access the label directly thru javascript so I'll just do that. Thanks for the thorough response.
-Jeremy
eric_h - Thursday, September 15, 2011 2:00 PM:
Hi Jeremy,
I needed some avatar I figured, and didn't want to put my own picture up (I guess I'm camera shy); somehow Bumble got the billing ;)
Let me see if I can help explain why the mapping matters. In some cases you don't care that the Label (as displayed) and Value (as stored in DB) are different. And in this case it doesn't make sense to have both. But there are times you need the Label (as displayed) and Value (as stored in DB) to be different.
What if for example you wanted a list of months to be displayed as the month name, but those had to be stored in the DB as the numerical representation in the DB say for calculation purposes (though they will still be stored as strings, these can be converted to numbers.) Or more appropriate maybe for calculations, you might have some component values displayed in the drop-down list (and shown in the search grid) as say [ 0.1uF, 0.68uF, 1.0uF, 10uF ], but you want to store in the DB as the values [ 0.1, 0.68, 1.0, 10 ] so you can apply to math formulas (again, after converting the string to a number) without having to truncate off the units symbol. Or maybe you want to display a list of countries, but store the value of their international telecom country code. Or whatever.
So as you can see, there are cases where the value as displayed (Label) is not desired to be the same as the value as stored in the DB (Value). And of course, if you want to order the drop-down list, you just assign numeric values to the sort order field to achieve that result (BTW, what I do when constructing lists is that when I am adding items at the end that fit somewhere in the middle, I assign a sort order value that fits it in where needed then I can click on the Sort Order column header to have it sort by those values - on a related note, it is a good ideas to increment the Sort Order by say like 10 or 100 or whatever so that if you add new list items, you can fit them in between two existing entries.)
Anyway, hope this helps.
Eric
