Find Customizations

Epicor ERP 9
1

I don’t think this can be done but I have someone requesting to know what fields have been used for customization. The previous team didn’t track anything so I still come across items i didn’t know were there.

2

Hi Kimberley,

I do not know of a way in 905702.
We’ve always used an Excel sheet to note Non-ud table use of UD fields and then another one for UD table use of UD fields. Thank God! :slight_smile:
FYI, however, in E10 (where we’re testing the upgraded version) all of the fields used (i.e., that have data in them on upgrade/conversion) can be queried from the ZDataField table tied to ZDataTable with criteria on dataTable tabletype=UD

Nancy

3

This might be a little tedious to create but… would a list all the columns that don’t have data help you out?

Here is a link for some SQL query examples…

4

Also not sure about E9, but the following SQL qry of the DB will give you all the Extended User Defined fields

SELECT      c.name  AS 'ColumnName' ,t.name AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       t.name LIKE '%_UD' AND c.name Like '%_c'
ORDER BY    TableName, ColumnName;

And to find tables with values in the the Userxxxn filed, first make a qry that makes a query

SELECT      'SELECT '''+ t.name+'.'+c.name+ ''' AS ''TableField'', COUNT('+c.name+') AS ''Count'' FROM Erp.'+ t.name + ' WHERE '+c.name+' IS NOT NULL;' AS qry
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       t.name NOT LIKE 'ES_%' AND t.name NOT LIKE 'IMES_%' AND (c.name Like 'UserChar%' OR c.name Like 'UserDate%' 
			OR c.name Like 'UserDate%' OR c.name Like 'UserDecimal%' 
			OR c.name Like 'UserInteger%' ) AND t.name NOT LIKE 'IM%'
ORDER BY    qry;

The ouput of that will be:

SELECT 'DemandHead.UserChar1' AS 'TableField', COUNT(UserChar1) AS 'Count' FROM Erp.DemandHead WHERE UserChar1 IS NOT NULL;
SELECT 'DemandHead.UserChar2' AS 'TableField', COUNT(UserChar2) AS 'Count' FROM Erp.DemandHead WHERE UserChar2 IS NOT NULL;
SELECT 'DemandHead.UserChar3' AS 'TableField', COUNT(UserChar3) AS 'Count' FROM Erp.DemandHead WHERE UserChar3 IS NOT NULL;
SELECT 'DemandHead.UserChar4' AS 'TableField', COUNT(UserChar4) AS 'Count' FROM Erp.DemandHead WHERE UserChar4 IS NOT NULL;
SELECT 'DemandHead.UserDate1' AS 'TableField', COUNT(UserDate1) AS 'Count' FROM Erp.DemandHead WHERE UserDate1 IS NOT NULL;
SELECT  ....

Then use those lines as query statements

I’m a hack when it comes to SQL, so I’m sure there’s a much “righter” way to do it.