We are experiencing an issue with a valuelist returning the ID of the database record for a null field rather than simply returning null.
We have a database table that looks something like this:
HW_ID | PART_NO
1 | XYZ
2
3
4 | zzz
5
On one of our sub-forms, we have a drop down list that allows the user to select the HW_ID field. Also on this sub-form we pull in the PART_NO field from the same table too.
When a record is selected that has a populated PART_NO field, the PART_NO is displayed as expected.
When a record is selected with a null PART_NO field, the PART_NO shows the HW_ID instead of null.
The configuration for this part no value list is as follows:
Valuelist Name: populated with a vl name
Table Values: populated with the table reference
Definition: Only 1st out of 3 used, “Show in field/list” (checked), part_no field is selected in listbox, “Return in dataprovider” (checked)
“Fallback ValueList”: -none-
“Allow empty value”: checked
“Sorting Definition”: part_no asc
Does anyone have any ideas how to adjust the configuration to stop the part_no field showing the id when part_no field is null?
As I see it you have numeric ID’s and alphanumeric part numbers. So a quick fix could be to test if the result is numeric or alphanumeric. If it is numeric replace it with null. Another option could be to describe the part number as -none-.
If you have “return in dataprovider” unchecked for HW-ID and checked for PART-NO, then I would expect that you should get null if you select a HW-ID that has no PART-NO associated with it, unless there is some default behavior in Servoy that I’m not aware of (which is quite possible).
Beyond that, I’d take a look in the actual data fields that you’re testing with to make sure that they match what you are expecting to be there. I’ve been burned by that myself. And then, I think you’re looking at some sort of workaround as the others have suggested.
I’d be interested in seeing what one of the Servoy folks has to say about the behavior that you’re seeing. If you’re telling it to return PART-NO in the dataprovider (and not HW-ID), then I"m wondering why it’s returning HW-ID instead in some cases.
i think this is because we think we can’t map to a display value or something and then we just show the id.
this happens for example a lot when the valuelist is based on a relation with a foreign key id → id to name
then when we cant resolve the relation because the id → id relation is just not there we see a null and fallback to the name
I guess in your situation it also does that. We just don’t expect that the display names are null when there are id’s
In your case it is for a user also very strange to see that list. If sees a list with 5 values, 2 of them are just empty/the same
So it just picks one? and that id is then set? That doesn’t make to much sense to me then it is just random what users can pick both id’s are just the same
Out of curiousity I reproduced the problem you describe but I see different behaviour in Servoy 6.1. With a table I always get the part number returned regardless of the value being null or spaces. Also when two records have empty partnumbers Servoy shows only one. If you do see two empty rows it’s because of the checkmark for allowing empty values. Looks like this has been solved in a later version than 5.2.13?
Thanks for your time to look at this issue and for all of your comments. If this behaviour is fixed in the latest version then that is very positive and something to look forward to. Unfortunately we will be stuck on our current version of Servoy (5.2.13) for a while.
Meantime, we need to do this in the way described above and still need a way to prevent these IDs being returned. Any suggestions for a fix are still most welcome and are appreciated.