Sage 100

I have an orphan who has added quite a few UDF fields to their CI_Item table. Is there a quick way that I can print out the field names for the structure of CI_Item? They must have 100 different UDF fields each holding specific characteristics of a part. I'd rather not have to jot them all down individually and don't see an easy way to print what the data dictionary looks like with all the UDF fields. Thoughts?

Push them to Excel and do a copy transpose paste

I only need the field names - I didn't setup the fields so I'm not vouching for what is or isn't in them.

Are you just looking for the UDFs or those WITH the standard CI_Item fields? If just the UDFS, print a UDF listing

From UDF maintenance, you can click the print button to get the list. Or do an Excel query on CM_UDF (? or something like it)

Just copy the header line in your excel spreadsheet. You can then paste/transpose this in another spreadsheet and it will put all the field names in a column

The people asking are web developers who work with the end user. Ideally, I'd like to give them a data dictionary listing of all the fields - including UDF - and they could make their own determination by looking at the field names whether the 100 or so UDF are holding relevant data. I'm trying to not be in a position of guaranteeing relevant data is in fields I had no part in establishing.

How about Custom Office, Reports, Customizer Detail Report. Check the boxes for Print Panel Items, Print Custom Panels Only and Print Custom Items Only, then choose the module and Company.

The Custom Office thought is good .. but damn those reports are (a) lengthy and (b) ugly. I was hoping for just one listing of fields with minimal white space and/or pages.

Tried pulling into Excel 2013 and I get a message that I can't pull in over 255 columns.

Kind of the same as custom office though....

Import the table in Access and run database documenter. It should give the dictionary details. I forgot about the 255 column limitation.

I don't see an easy way to get a table structure printout when the customer has gone crazy on adding UDF fields

Run a ""select * from..."" query using Launch ODBCView and you should be able to bypass the column limit.

Wayne, if you send me the client's PROVIDEX.DDE, and PROVIDEX.DDF files, I have an MD tool that will extract the attributes of each field to a .CSV file.

How come just printing the UDF Report won't suffice?

+1 on @MarcosDeLuna's recommendation.

@MarcosDeLuna @AlnoorCassim - is there a way to get a field list with data type via BOI? So a BOI app could get new UDFs as they are added without recoding?

@BrettZimmerman Printing the UDF report does work but there are a couple issues. (1) It doesn't show the complete Sage table structure (in this case CI_Item), only the UDFs, (2) There is no way in Sage to restrict printing to JUST the UDFs in the CI_Item. It appears that you print all or no UDF definitions. This doesn't work well for a customer request to know what fields are available in CI_Item. Does it get them there? Yeah, but not nice and I'll guarantee with 90% certainly if I send two PDF filed (regular table structure, UDF structure) they will just call and ask ""what is this? "". In this specific instance, I am dealing with a 3rd party web developer who wants to know whether certain fields of data are available in CI_Item. This customer (or one of the myriads of come-and-go Sage consultants they've used) has added 379 UDF fields attached to CI_Item over the past several years. There's no practical way I'm going through every field name and match to the web developer request and state whether data they want is included in the UDF. So, what I'm hoping is that for a middle ground I could give them a nice concise CI_Item file layout which includes just the field names for both the standard and the UDF fields. I think that arguably falls within the bounds of my support. Analyzing what someone added over the course of many years in the form of UDF fields and giving an opinion on whether a myriad (probably 100) web developer fields have a corresponding UDF is beyond the scope of support and I would have to separately quote a fee for that. Sorry so long but I hope it sheds light on the request.

Wayne, another option is this. There is a file called \MAS_SYSTEM\CM_UDF.M4T. Though it's a system file, you can do a an Excel query using the SOTAMAS90 ODBC driver. It will have all the attributes of each UDF field for each table.

@MarcosDeLuna Thanks - I'll try that. Tonight when everyone is out I'll grab those providex files as well. I can't copy them while the system is up and running.

@MarcosDeLuna attaching to CM_UDF.M4T gave me what I needed. Thank you and everyone for the feedback on this!

@PhilMcIntosh - @DavidSpeckII posted a DataDictionaryParser a few months ago in response to a @DanBurleson question. See if you can see search for it. It might have what you need.

Fwiw, @RobertWhite above also suggested an Excel query against the cm_udf.m4t file (which sounded like a good idea), but I got the sense that wasn't feasible as the thread continued; until @WayneSchulz said it worked.