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 or, begin using our web API Distributed ...

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

• 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 - How To - Add Data Connections

  How to Add a data connection? Step 1 : Login to SmartViews and select Designer tab and Click on "Add" a data connection Currently, Smartviews support the following data connection: Microsoft SQL server MS Access MS Excel Oracle ODBC Lillyworks ...

• SmartViews Scheduler - User Guide

  PREREQUISITES Supported Visual version are 7.0 – 11.x Database Platform: SQL Server & Oracle Microsoft .Net Framework 4.8 ADMINISTRATION LICENSING Request for a license key can be made via email. Send your request to CustomerCare@wm-synergy.com and ...