I have a PHP page that will show the changes from the change log table. The problem is is shows Field No_ instaed of the actual Field Name. I’ve been looking around and I think it’s a virtual table that holds this. Can someone help me out? Here is my query:
SELECT ChangeLog.[Date and Time], ChangeLog.[User ID], Tables.Name, ChangeLog.[Field No_] , ChangeLog.[Old Value], ChangeLog.[New Value] FROM [CompanyName$Change Log Entry] AS ChangeLog INNER JOIN Object AS Tables ON ChangeLog.[Table No_] = Tables.ID WHERE (Tables.[Company Name] = ‘CompanyName’) AND (ChangeLog.[Date and Time] BETWEEN ‘04/23/09 00:00’ AND ‘04/23/09 23:59’) ORDER BY Tables.Name, ChangeLog.[Field No_], ChangeLog.[User ID]
You could probably us a join to pull the info together. Fields of interest would be Type, Company Name, ID and Name
EDIT: After actually looking at your SQL I found the NAME field was returning the object names with no issues. Only thing I’d suggest is another where clause to filter for type to be table on the Object table.
Objects table doesn’t extend to Field Names - info about Field Names (in different languages) is stored only in Table definition, being an encrypted BLOB, to which you don’t have access with SQL methods. So, SQL script substituting Field No`s with Field Names is impossible, unless you create a custom translation table for this purpose.
Actually, NAV refers to all objects by number internaly and don’t bother about their names - that’s why you can rename Fields and everything still works:)
Thanks for the response. I guess that makes sense. The problem is I wanted to have this PHP page email a CSV file of all the changes daily. How could I create a custom translation table? Can you give me some more details of what you mean?
Well, that depends… If this table is used only by your SQL script, it might be created bypassing NAV, in SQL Management Studio. It should contain at least 3 fields - TableNo, FieldNo, FieldName. The worst thing is, that the only way to populate it is manual entering of values, which is a HUGE job - but I suggest you are interested in some tables/fields only - the ones you have included in ChangeLog.
Creating a table directly by SQL MS means:
it will NOT be visible from NAV environment,
it will NOT be included in NAV backup.
Seems that you have enough knowledge of SQL to complete this task, but I must still warn you, that direct SQL manipulations other than SELECTing data from NAV tables can cause serious trouble and system crashes - so never INSERT/DELETE/UPDATE anything in NAV tables directly, bypassing NAV logics!
If you have access to TableDesigner - you can open the required table definition, Copy / Paste it to Excel (first select all rows clicking in upper-left corner), clean up unwanted colums from Excel - and how to move these data to SQL table I hope you know yourself [;)]
Yeah but this would still be table by table, right? I think I saw something about a dataport that might work. Even if I have it run every day or so that would be better than manually doing this.
DataPorts are for DATA export/import, but you need TableDefinition…
BTW, how much logging you have set up in ChangeLog? Be careful with this, and include in logging ONLY data you really want to check for changes - otherwise your database size will grow at light speed!
This is what I did to get around entering all that data. I ran the form Fields and clicked the top left corner to highlight everything. Then in SQL Server Studio I created a new table with these fields:
TableNo - int
No. int
TableName - nvarchar(50)
FieldName - nvarchar(50)
Type - nvarchar(50)
Class - nvarchar(50)
Then just clicked on new row and clicked ctrl-c to paste it in. Worked fine. I’ll just have to remember to update that table if we modify the column names. Thanks everyone for your help!
Smart move [Y] - I couldn’t manage to figure it out - first, forgot about form itself, and second - simply add all the columns to it and voila! you get what you need!