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 - User Guide

  Prerequisites: Windows Server 2016-2022 Windows 10-11 SQL Server 2012R2-SQL 2019 SQL Server 2017 minimum Cumulative Update (CU) is 31 (14.0.3456.2) Visual 7x-10 .NET 4.6.2 if outgoing ports are blocked at the organization’s firewall, the following ...

• Launchpad Scheduler - User Guide

  Launchpad Scheduler Guide In this guide the steps for creating a Basic Scheduled Report job as well as advanced Configuration Creating a New Scheduled Job Click on the New Job icon Task Give the Task Description a Name Click Save Schedule Click on ...

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

• SmartViews Data Connection not showing up in specific report

  If a data connection is successful in Test Connection but doesn't show up as an accessible connection in the drop-down list for a report, the issue may be with user security permissions. In the designer, go to the Security tab and see if the ...

• Delete a Packlist with SBO

  public bool Delete_Packlist(string packlist_id) { DatabaseConnection _Database; bool result = true; try { // Add DB connection specific code here. using (IDbConnection conn = _Database.Connection) { conn.Open(); SHIPPER s = new ...