DropDown Box problem....Showing value for NULL entries

[leetv]

Hi,
I've added an ID field to a grid (The Grid is bound to tbl_crs_lst as its query) and changed the type to DropDown. I've made it choices using the dynamic options.

dynamic data settings
data source: AlphaDAO
Saved Connection String: true
connection sting: conn
SQL: SELECT DISTINCT comp_name, ID_comp FROM tbl_comp ORDER BY comp_name, ID_comp
Display for no selection: --Select a company--

when i got to preview the grid, any record on the grid for Comp_ID that has a null value (in tbl_crs_lst) shows the first value of the SQL statement as its dropdown value. for example if row 1's Comp_ID is null it shows a value on the grid. the values that are populated in crs_lst are showing correctly.

what am i doing wrong? I'm hoping that it's something really simple and not a bug...!

Thanks
lee

[forskare]

Re: DropDown Box problem....Showing value for NULL entries

Hi,
I've added an ID field to a grid (The Grid is bound to tbl_crs_lst as its query) and changed the type to DropDown. I've made it choices using the dynamic options.

dynamic data settings
data source: AlphaDAO
Saved Connection String: true
connection sting: conn
SQL: SELECT DISTINCT comp_name, ID_comp FROM tbl_comp ORDER BY comp_name, ID_comp
Display for no selection: --Select a company--

when i got to preview the grid, any record on the grid for Comp_ID that has a null value (in tbl_crs_lst) shows the first value of the SQL statement as its dropdown value. for example if row 1's Comp_ID is null it shows a value on the grid. the values that are populated in crs_lst are showing correctly.

what am i doing wrong? I'm hoping that it's something really simple and not a bug...!

Thanks
lee

I take it the records with a null value in the Comp_Id are all the same value. I'm taking a guess but if it were mine, I'd look at the connection string. Assuming it's something simple, you are indirectly telling it to provide the value. Granted, you are not telling it to 'if comp_id is null, then tbl_crs_1st', but the way the connection string or your sql statement is set up, that's what is happening.

Sorry if this suggestion is 'wacky' but I've not been down this path before and if this were happening to me, that's where I would start. Hopefully, this is of some help.

[Steve Wood]

Re: DropDown Box problem....Showing value for NULL entries

I think all you have to do is go the dropdown properties and find the Not In List rule and toggle it to the Do Nothing setting.

[iRadiate]

Re: DropDown Box problem....Showing value for NULL entries

I was seeing the same thing with the drop-down list being populated with the first table value even though the settings were set to display NULL.

Playing around with the grid settings, I was able to get the drop-down to display an initial NULL for creating new records for a dynamic linked table:

dynamic data settings
data source: AlphaDAO
Saved Connection String: true
connection sting: named
SQL: SELECT DISTINCT LASTNAME FROM employees WHERE (DEPARTMENT IN ('Engineering', 'Machine Shop', 'I.T.') ) AND STATUS = 1 ORDER BY LASTNAME
Display for no selection: blank

Display Settings | Initial Value = blank or NULL
DropDownBox Properties | Not In List = Add To List

If Add To List is changed to Do Nothing then the dropdown list will be populated with the first value in the table. I think this is because the grid is being restricted from adding a NULL value to the dropdown?

Anyway, I am not an expert in Alpha, just my 2 cents worth of observation. Please feel free to comment.

[aburningflame]

Re: DropDown Box problem....Showing value for NULL entries

I have never gotten this to work and I have been using alpha with some big projects and I dont think any of the dropdown options for null values work. I think u can click choices and click the choices before (pre) and add this:

Select a company|0

If not, use this workaround:
Set the dropdown sql to this:

SELECT 'Select a company' as comp_name,0, 0 as ord
UNION
SELECT DISTINCT comp_name, ID_comp, 1 as ord FROM tbl_comp ORDER BY ord, comp_name, ID_comp

[chadbrown]

Re: DropDown Box problem....Showing value for NULL entries

I am thinking that you are using a dynamic dropdown set your filter to say

field1 is not null

But I may very well be missing something

[leetv]

Re: DropDown Box problem....Showing value for NULL entries

thanks to all your posts.

I finally figured out what i needed to do, and after i read all of yoru posts and then banged my head against the wall it all made sense!

I needed to build an SQL statement with many left/outer joins, and then use the drop down boxes/or even just text boxes to show the corresponding detail for the record. I know this is an elementary mistake, and probably because trying to write a web application for 24+ hours at a time didnt help.

so anyone in the future (including myself--when i forget and beyond the point of exhaustion). I the sql with outer joins and then giving the fields a lookup value (drop down box) referencing another table was my solution... the only trade off to this is that the grid is slower to load... (but not by much).


Thanks again. your posts helped me see where i was going wrong... :)
lee

[leetv]

Re: DropDown Box problem....Showing value for NULL entries

thanks to all your posts.

I finally figured out what i needed to do, and after i read all of yoru posts and then banged my head against the wall it all made sense!

I needed to build an SQL statement with many left/outer joins, and then use the drop down boxes/or even just text boxes to show the corresponding detail for the record. I know this is an elementary mistake, and probably because trying to write a web application for 24+ hours at a time didnt help.

so anyone in the future (including myself--when i forget and beyond the point of exhaustion). I the sql with outer joins and then giving the fields a lookup value (drop down box) referencing another table was my solution... the only trade off to this is that the grid is slower to load... (but not by much).


Thanks again. your posts helped me see where i was going wrong... :)
lee

[GGandhi]

Re: DropDown Box problem....Showing value for NULL entries

I have never gotten this to work and I have been using alpha with some big projects and I dont think any of the dropdown options for null values work. I think u can click choices and click the choices before (pre) and add this:

Select a company|0

If not, use this workaround:
Set the dropdown sql to this:

SELECT 'Select a company' as comp_name,0, 0 as ord
UNION
SELECT DISTINCT comp_name, ID_comp, 1 as ord FROM tbl_comp ORDER BY ord, comp_name, ID_comp

Hello Scott
I know this post is old and probably you solved this... but your thinking line was correct at least for me
i am using version 10.5/mysql back end. In the dynamic drop down i filled out all that is necessary and in the pre area i only placed single pipe "|" character and nothing
else. so when the drop down comes there is a blank line at the top and the rest in alphabetical order as i wanted. so i can select the blank line for null value or any other value that fits my need

thanks for reading

gandhi