Automatic insertion of field descriptions for standard tables
Forums
Hello everybody,
I will keep it short:
General:
I have to provide files (that have to follow a specific format) to the tax authorities on a regular basis.
One condition is that they need to have field descriptions in my language (German). Oftentimes I have English field descriptions, so I would have to manually translate them one by one. Of course I know I could use the history and create a super huge macro with all possible field names and the appropriate field descriptions. However, I would like to use a dictionary as the source for my "new" field descriptions" and then run a macro that uses this dictionary for renaming the field descriptions.
I see two possibilities:
a) an Excel file that contains the dictionary
b) an IMD file that contains the dictionary
a would probably be easier, but I wanted to know:
1. is it possible to do a lookup, e.g. If I have a table with the field "TEST" and 10 Records ( record1Value, record2Value etc.)
How can I get the value of the 5th record?
2. If 1 is possible: How can I retrieve a value that comes from another field than TEST, e.g. TEST2, TEST3 and is in the 5th record?
3. How would you rename thousands and thousands of field descriptions efficiently?
Thanks a ton!
Dirk from Berlin, Germany
Thank you very much, Brian!In
Thank you very much, Brian!In the meantime I have discovered recordsets, too. I will proceed implementing the loop you have suggested and let you know if it worked out for me.
As a next step I will have to export a list of the fields and field descriptions to a word document, which will then be send to the tax authorities to explain the data set /data base tables. Might I ask if you already have a script at hand, that would allow to save the table structure in an index.xml file or index.dtd?Thank you for your help!
Hi Dirk,
Hi Dirk,
This is an interesting challenge you have presented.
I think it can be down through a macro but not necessarily through a huge macro. I agree creating a look-up dictionary is probably the way to go. You could create it in Excel but for ease of use it should be imported into IDEA so the script can more easily access it. So here are my thoughts on your questions:
1. Yes you can. In IDEAScript you can read each record and each cell. You need to use the RecordSet object to get the information in a record and then the Record object to get the contents of an individual cell. In the RecordSet object there is a function call GetAt that will point to a record, such as in your case record 5. The example code would be something like this:
Set db = client.OpenDatabase("My Database.IMD")
Set rs = db.RecordSet
rs.GetAt(5)
2. To get the information from a cell in the record you just use the column name or location in the file. So continuing on with the above example you would use:
Set rec = rs.ActiveRecord 'in this example it would be record 5
rec.GetCharValue("Test1") 'or Test2 or Test3
If you want to loop through all of the columns you could use rec.GetCharValueAt(3) which would get the information in the third field. Doing this you can loop through all of your fields.
3. For renaming the field descriptions, assuming that you have a data dictionary, I would create a script that loops through each field. I would extract the field information:
Set db = Client.OpenDatabase("MyDatabase.IMD")
Set table = db.TableDef
count = table.Count 'get the number of fields in the database
for i = 1 to count
Set Field = table.GetFieldAt(i) 'loops through each field
sDescription = Field.Description 'get the current fields description
sDescription = LookUpNewFieldsDescription(sDescription) 'call a function that does the lookup and returns with the translation
call ChangeFieldDescription()
Set Field = nothing
next i
So in the above you would get the current description, look it up the translation and then save the the new description.
Let me know if you need any help creating the script. Shouldn't be a big problem. If you need help example files would be nice.
Good luck.
Brian