Retrieving Customizable User Defined Field (CUDF) List Values with SBO

The UFD List data is stored in the USER_DEF_FIELDS table. It looks like this:

 

You can retrieve it with a direct SQL query, but you need to know the UDF’s ID for the field in question. Here is an example:

SELECT PROGRAM_ID, ID, DOCUMENT_ID, LINE_NO, DEL_LINE_NO, DATA_TYPE, STRING_VAL

FROM USER_DEF_FIELDS

WHERE PROGRAM_ID = 'STATIC_DATA'

AND ID = 'UDF-0000094'

AND DOCUMENT_ID IS NULL

AND LINE_NO IS NOT NULL

AND DEL_LINE_NO IS NULL

AND DATA_TYPE = '10050'

ORDER BY LINE_NO ASC

 

The bigger picture is:

 

Rows 1-4 – the list values (DATA_TYPE = 10050)

Row 5 – the list name (DATA_TYPE = 1005)

Rows 6 and higher are Document instances of the List UDF.

In SBO you would do this where “conn” is the SBO DatabaseConnection object:

 

The “using” statements clean up the “IDbCommand” and “IDataReader” objects to avoid resource leaks:

                List<string> list = new List<string>();

                using (IDbCommand cmd = conn.CreateNewCommand)

                {

                    cmd.CommandType = CommandType.Text;

                    cmd.CommandText = "SELECT STRING_VAL " +

                                        "FROM USER_DEF_FIELDS " + 

                                        "WHERE PROGRAM_ID = 'STATIC_DATA' " + 

                                        "AND ID = 'UDF-0000094' " +

                                        "AND DOCUMENT_ID IS NULL " +

                                        "AND LINE_NO IS NOT NULL " +

                                        "AND DEL_LINE_NO IS NULL " +

                                        "AND DATA_TYPE = '10050' " +

                                        "ORDER BY LINE_NO ASC";

                    using (IDataReader reader = cmd.ExecuteReader())

                    {

                        while (reader.Read())

                        {

                            list.Add(reader.GetValue(0).GetType().Name == "DBNull" ? null : reader.GetString(0));

                        }

                    }

                }

         

• Related Articles

• SmartViews Scheduler User Guide

  PREREQUISITES Supported Visual version are 7.0 – 10.x Database Platform: SQL Server & Oracle Microsoft .Net Framework 4.8 ADMINISTRATION INSTALLING Use the following link to download the setup file. Once downloaded follow these steps to install. Run ...

• RAM Connector - User Guide

  Prerequisites Supported Visual version are 7.0.0 – 10 Database Platform: SQL Server & Oracle .Net Framework 4.6.2 Installation Use the following Link to download the setup file. Once downloaded follow these steps to install Install Run ...

• Synergy uniPoint Connector - User Guide

  Installation You can find the installation document for Synergy uniPoint Connector here. How to Use Overview Synergy uniPoint Connector can be used in the following Visual windows: Customer Maintenance Customer Order Entry Part Maintenance Vendor ...

• SmartViews - User Guide

  Prerequisites: Windows Server 2012R2-2022 Windows 8-11 SQL Server 2012R2-SQL 2019 SQL Server 2017 minimum Cumulative Update (CU) is 31 (14.0.3456.2) Visual 7x- .NET 4.6.2 Visual 11 - .NET 4.8 64 bit architecture if outgoing ports are blocked at the ...

• MacroServer - Field / Form Level Security

  Create a macro that will prevent users from changing specific field level Part Master Data Create OnSaveMacro under Part Maintenance Login to Macro editor on macroserver Create Macro Script with Logic Configure Permnissions Under Visual Program ...