7 Replies Latest reply on Dec 14, 2012 8:21 AM by philmodjunk

    OBDC Value list

    LarryLarson

      Title

      OBDC Value list

      Post

            

                I have a working ODBC connection in a filemaker 12 DB. I have created a value list to look up data from a related table. I also have it configured to get its values from this list. It works fine with existing records. However, when a new record is added, or when there is a NULL value in the field for an existing record, the drop down does not show any values when clicked. I rarely work with FMP, but I have googled this for hours and have no idea why...

                 

            

        • 1. Re: OBDC Value list
          philmodjunk

               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.

          • 2. Re: OBDC Value list
            LarryLarson

                 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...

                  

                  

            • 3. Re: OBDC Value list
              philmodjunk

                   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?

              • 4. Re: OBDC Value list
                LarryLarson

                     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.

                      

                     Thanks again.

                      

                     Larry

                • 5. Re: OBDC Value list
                  philmodjunk

                       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?

                  • 6. Re: OBDC Value list
                    LarryLarson

                         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).

                         L

                          

                    • 7. Re: OBDC Value list
                      philmodjunk

                           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.