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

• WM-Synergy SBO - Release Notes

  Unless otherwise noted all Versions should work with a prior release of Visual ERP. The .net framework version will be discontinued. Please begin your transition over to the .net standard version of sbo objects. .Net Standard 2.0 Compatibility Chart: ...

• WM-Synergy Business Objects SDK Information

  WM-Synergy Business Objects WM-Synergy Business Objects, SBO, is a comprehensive transaction library built on the both the .net standard and .net framework platforms. This library allows the developer to quickly read/write/update transactional data ...

• RAM Connector - User Guide

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

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

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