when there is a NULL value in the field for an existing record, the drop down does not show any values
A null value in what field exactly?
Depending on what kind of ODBC connection that you have and exactly how you are adding new records, you may need to requery the ODBC datasource after new records have been added before they will appear in your value list.
Thank you for the response.
This is the kind of thing I've done for years in other DB contexts, but that does not seem to help with FMP. table 1 contains an ID field that is related to table 2, which has the value. I have created a value list that successfully display the value from table 2 for existing records, and allows the user to see all possible values. However, when entering a new record, or when the underlying record in table 1 has a NULL value, the drop down value list does not display the possible values. The expected behavior is that the user would be able to select a value from the value list, of course.
There have been no new records added to table 2 in this process, so I don't think it is a data refresh issue. It is as if the underlying query is written using an inner join, and returns nothing when there is no value in table 1. In Flex or vb.net I just populate the drop down with a query against table 2, but I do not seem able to do that here.
I appreciate your help, and know I must be making some low level mistake...
Please explain exactly how you have defined your value list.
Does the value list draw values from Table 1--local to fileMaker or from table 2-- from the ODBC data source?
It draws values from the table 2 via the ODBC connection. Table 1 is ODBC as well, fwiw. It works well as long as the value in the id field in table 1 is found in table 2. If it is not, then the drop down has no data at all when clicked.
Yes, but exactly what options have you specified for this value list in valuelist setup?
is the id field you mention field 1 (the primary field) specified for the value list?
Using the Include only related values option?
Hiding a field?
Sorting on a secondary field?
Is the field with a NULL value that you mention either the primary or secondary field specified for the value list?
I really appreciate your patience. I have attached a screen shot of the value list configuration. In this case dbo.Record_Table is table 2. I am sorting on the secondary field. The NULL value would occur in table 1 -- there are no nulls in table 2. I am including all values, and limiting the displayed return to the label field (record_title).
Well that rules out one possiblity, a relationship based issue, as you aren't using the "include".
The only possible issue that occurs to me to check is whether or not your "Null value" was a null in the Record_Title field or not. A Record_ID value with null in the Record_Title field will be omitted from the value list, but the other values should appear so that would not explain getting an empty value list.