[Solved] Update discriptions of records in tables

[dreamquartz]

Hello All,

Looking @ tables, there are 3 headers: "Field Name", "Field Type", and "Description" visible in my case.
I am wondering how to Add/Update information under the header "Description".

Kind Regards,

Dream

[UnklDonald418]

All the Base Guide ( https://documentation.libreoffice.org/a ... eGuide.pdf ) has to say is

Description could be anything. This column can also be left empty. The description serves
only to explain the field content for people who want to view the table definition later.

After further investigation I found that Description is stored in the Base front End, specifically Content.xml inside the .odb file.
When using an Embedded Base database simply open the table in the Edit mode and you can add/edit the contents of the Description column of the Table using the design GUI.
When using a JDBC connection (aka Split database) the table design GUI can only be used to add new fields, so anything in the Description column must be added at the time the field is added. Once saved it can no longer be edited using the GUI.
I suppose if you are brave/foolhardy, you could edit Content.xml but any mistakes could render the database inoperative.

[F3K Total]

Hello,
for Split DB it can be done by code.
An Example:
Tablename = Persons
Column #1 = SN
Column #2 = FN

To add a description to the columns execute once this code:

Code: Select all

sub SET_HelpText
    oController = thisDatabasedocument.currentcontroller
    if not oController.isconnected then oController.connect
    oConnection = oController.activeConnection
    oTables = oConnection.Tables
    oTables.getbyname("Persons").Columns.getbyname("SN").HelpText = "This Column holds surnames"
    oTables.getbyname("Persons").Columns.getbyname("FN").HelpText = "This Column holds firstnames"
end sub

Then save and close the .odb file.
On reopen you should find the desciptions in the tables definition.

HT.png (6.86 KiB) Viewed 11823 times

EDIT 2024-12-22: Modified the code
R

[F3K Total]

Hello,
find here macros which open two dialogs in sequence to enter columns descriptions into split HSQL-DB's, others not testet.
For that start the macro "S_Create_Dialog_Table_Selection".

1. Select the table to enter columns descriptions

   

   TS.png (4.14 KiB) Viewed 11608 times

2. Enter columns descriptions

   

   CS.png (6.71 KiB) Viewed 11608 times

Code: Select all

global odlgTables
global oTables

Sub S_Create_Dialog_Table_Selection
    Dim oWindow As Object
    Dim oMod As Object
    Dim i As Integer
    oController = thisDatabasedocument.currentcontroller
    if not oController.isconnected then oController.connect
    oConnection = oController.activeConnection
    oTables = oConnection.Tables
    nTables = Ubound(oTables.ElementNames) + 1
    ndlgHeight = 12*(nTables) + 20    
    
    REM ***** Initialisierung der Eigenschaften des Dialogs
    odlgTablesModel = CreateUnoService("com.sun.star.awt.UnoControlDialogModel")
    
    With odlgTablesModel
       .setPropertyValue("PositionX", 320)
       .setPropertyValue("PositionY", 111 )
       '.setPropertyValue("FontName", Font)
       .setPropertyValue("Width", 120)
       .setPropertyValue("Height", ndlgHeight+3)
       .setPropertyValue("Title", "SelectTable")
       .setPropertyValue("Name", "DLGSelectTable")
       '.setPropertyValue("DesktopAsParent", True )
    End With

    odlgTables = CreateUnoService("com.sun.star.awt.UnoControlDialog")
    REM ********** Schaltflaechen erzeugen	
    for i = 0 to Ubound(oTables.ElementNames)
        oMod = odlgTablesModel.createInstance("com.sun.star.awt.UnoControlButtonModel")
        With oMod
            .setPropertyValue("Label", oTables.ElementNames(i))
            .setPropertyValue("Name", "CmdTable"+i)
            .setPropertyValue("PositionX", 10)
            .setPropertyValue("PositionY", 12*(i+1))
            .setPropertyValue("Height", 12)
            .setPropertyValue("Width", 100)
            '.setPropertyValue("Tag", s_buttons(3,i))
            .setPropertyValue("FontHeight",9)
            .setPropertyValue("FocusOnClick",false)
            .setPropertyValue("Tabstop",true)
        End With
        odlgTablesModel.insertByName("CmdTable"+i, oMod)
    next i 
 
    odlgTables.setModel(odlgTablesModel) 
    REM ********** ActionListener erzeugen und Schaltflaechen zuordnen
    ocmd_ActionListener = createUnoListener("cmd_ActionListener_", "com.sun.star.awt.XActionListener")
    for i = 0 to Ubound(oTables.ElementNames)
       oControl = odlgTables.getControl("CmdTable"+i)
       oControl.model.Align = 0
       oControl.addActionListener(ocmd_ActionListener)
    next i
 
    REM ********** Mittels des Modells den Dialog anzeigen
    oWindow = CreateUnoService("com.sun.star.awt.Toolkit")
    odlgTables.createPeer(oWindow, null)
    Dim oWindowsListener as Object
    oTopWindowsListener = CreateUnoListener( "Top_Win_", "com.sun.star.awt.XTopWindowListener" )
    odlgTables.addTopWindowListener(oTopWindowsListener)
    odlgTables.setVisible(True)
End Sub

sub Set_columns_Description(sTableName)
    Dim oWindow As Object
    Dim oMod As Object
    Dim i As Integer

    oTable = oTables.getbyName(sTableName)
    nColumns = Ubound(oTable.Columns.ElementNames) + 1
    nTextFieldHeight = 104
    ndlgHeight = 14*(nColumns+4)    
    
    REM ***** Initialisierung der Eigenschaften des Dialogs
    odlgColumnsModel = CreateUnoService("com.sun.star.awt.UnoControlDialogModel")
    
    With odlgColumnsModel
       .setPropertyValue("PositionX", 320)
       .setPropertyValue("PositionY", 111 )
       '.setPropertyValue("FontName", Font)
       .setPropertyValue("Width", 400)
       .setPropertyValue("Height", ndlgHeight)
       .setPropertyValue("Title", "enter Columns Descriptions")
       .setPropertyValue("Name", "DLGDescriptions")
       '.setPropertyValue("DesktopAsParent", True )
    End With

    odlgColumns = CreateUnoService("com.sun.star.awt.UnoControlDialog")
    REM ********** Schaltflaechen erzeugen	
    for i = 0 to Ubound(oTable.Columns.ElementNames)
    
    REM ********** Textlabel erzeugen
    oMod = odlgColumnsModel.createInstance("com.sun.star.awt.UnoControlFixedTextModel")
    With oMod
       .setPropertyValue("Label", oTable.Columns.ElementNames(i))
       .setPropertyValue("Name", "lblColumn"+i)
       .setPropertyValue("PositionX", 10)
       .setPropertyValue("PositionY", 14*(i+1))
       .setPropertyValue("FontHeight",9)
       .setPropertyValue("Height",12)
       .setPropertyValue("Width", 60)
    End With
        odlgColumnsModel.insertByName("lblColumn"+i, oMod)

    REM ********** Textfeld erzeugen
    oMod = odlgColumnsModel.createInstance("com.sun.star.awt.UnoControlEditModel")
    With oMod
       .setPropertyValue("Name", "txtColumnDescription"+i)
       .setPropertyValue("PositionX", 80)
       .setPropertyValue("PositionY", 14*(i+1))
       .setPropertyValue("Height",12)
       .setPropertyValue("Width", 310)
       .setPropertyValue("Border",2)
       .setPropertyValue("VerticalAlign",0)'            com.sun.star.style.VerticalAlignment
       .setPropertyValue("MultiLine",TRUE)
       .setPropertyValue("FontHeight",10)
       .setPropertyValue("FontName","Courier New")
       .setPropertyValue("Text",oTable.Columns(i).HelpText)
    End With
    odlgColumnsModel.insertByName("txtColumnDescription"+i, oMod)
    next i 

       oMod = odlgColumnsModel.createInstance("com.sun.star.awt.UnoControlButtonModel")
        With oMod
            .setPropertyValue("Label", "OK")
            .setPropertyValue("Name", "CmdOK")
            .setPropertyValue("PositionX", 10)
            .setPropertyValue("PositionY", 14*(i+2))
            .setPropertyValue("Height", 12)
            .setPropertyValue("Width", 380)
            '.setPropertyValue("Tag", s_buttons(3,i))
            .setPropertyValue("FontHeight",9)
            .setPropertyValue("FocusOnClick",false)
            .setPropertyValue("Tabstop",true)
            .setPropertyValue("PushButtonType",com.sun.star.awt.PushButtonType.OK)
        End With
        odlgColumnsModel.insertByName("CmdColumn"+i, oMod)
    odlgColumns.setModel(odlgColumnsModel) 
    REM ********** Mittels des Modells den Dialog anzeigen
    oWindow = CreateUnoService("com.sun.star.awt.Toolkit")
    odlgColumns.createPeer(oWindow, null)
    Dim oWindowsListener as Object
    oTopWindowsListener = CreateUnoListener( "Top_Win_", "com.sun.star.awt.XTopWindowListener" )
    odlgColumns.addTopWindowListener(oTopWindowsListener)
    odlgColumns.setVisible(True)
    if  odlgColumns.execute = 1 then
        for i = 0 to Ubound(oTable.Columns.ElementNames)
        oTable.Columns(i).HelpText =  odlgColumns.GetControl("txtColumnDescription"+i).Text
        next i
    endif
    Thisdatabasedocument.store
    msgbox ("Done, Columns descriptions where registered in Table """ & oTable.name &""" ✔ " & chr(13) & "The .odb file was saved!",64,"Descriptions were registered")
end sub

Sub cmd_ActionListener_actionPerformed(oEv)
    odlgTables.setVisible(False)
    Set_columns_Description(oEv.Source.model.Label)
end sub

Sub cmd_ActionListener_disposing(oEv)
End Sub   

'Diese Routinen werden von dem XTopWindowListener benötigt
Sub Top_Win_windowClosing( oEvent ) 'Dialog schließen
    oEvent.source.setVisible(False)
    'odlgTables.dispose
End Sub

Sub Top_Win_disposing( )
End Sub
Sub Top_Win_windowOpened   ( oEvent )
End sub
Sub Top_Win_windowClosed   ( oEvent )
End sub 
Sub Top_Win_windowMinimized   ( oEvent )
End sub
Sub Top_Win_windowNormalized   ( oEvent )
End sub
Sub Top_Win_windowActivated   ( oEvent )
Top_Win_windowDeactivated = false
End sub
function Top_Win_windowDeactivated ( oEvent ) as boolean
Top_Win_windowDeactivated = true
End function

Merry Chrismas

[dreamquartz]

That works exactly as I wanted it.
Thank you so much

Dream

Merry Christmas and a Happy New Year

[dreamquartz]

Using it for a while and works great. Thanks aigain.
I would like to request an update @ this point.
We manage large databases as well with many tables.
However the monitor screens we use are to small to show all tables. Is there a way to add a feature to scroll large lists?

Thanks,

Dream

[robleyd]

From the Wiki:

Horizontal/Vertical Scroll Bar

[F3K Total]

Try this:

Code: Select all

global odlgTables
global oTables
global nHeightButtons

Sub S_Create_Dialog_Table_Selection
    Dim oWindow As Object
    Dim oMod As Object
    Dim i As Integer
    oController = thisDatabasedocument.currentcontroller
    if not oController.isconnected then oController.connect
    oConnection = oController.activeConnection
    oTables = oConnection.Tables
    nTables = Ubound(oTables.ElementNames) + 1
    nHeightButtons = 12 * (nTables)
    ndlgHeight = nHeightButtons + 24

    if nHeightButtons > 200 then
        bScrollbar = true
        ndlgHeight = 200 + 20
    endif
   
    REM ***** Initialisierung der Eigenschaften des Dialogs
    odlgTablesModel = CreateUnoService("com.sun.star.awt.UnoControlDialogModel")
    
    With odlgTablesModel
       .setPropertyValue("PositionX", 320)
       .setPropertyValue("PositionY", 111 )
       '.setPropertyValue("FontName", Font)
       .setPropertyValue("Width", 130)
       .setPropertyValue("Height", ndlgHeight+10)
       .setPropertyValue("Title", "SelectTable")
       .setPropertyValue("Name", "DLGSelectTable")
       '.setPropertyValue("DesktopAsParent", True )
    End With

    odlgTables = CreateUnoService("com.sun.star.awt.UnoControlDialog")

    if bScrollbar Then
    REM ********** SCROLLBAR erzeugen
    oMod = odlgTablesModel.createInstance("com.sun.star.awt.UnoControlScrollBarModel") 
    With oMod
       .setPropertyValue("Orientation",1)
       .setPropertyValue("ScrollValueMax", 100)
       .setPropertyValue("BlockIncrement", 10)
       .setPropertyValue("LineIncrement", 5)
       .setPropertyValue("VisibleSize", 10)
       .setPropertyValue("Height", ndlgHeight)
       .setPropertyValue("Width", 10) 
       .setPropertyValue("PositionX", 120)
       .setPropertyValue("PositionY", 0)      
    End With
        odlgTablesModel.insertByName("Scrollbar", oMod)
    endif

    REM ********** Schaltflaechen erzeugen	
    for i = 0 to Ubound(oTables.ElementNames)
        oMod = odlgTablesModel.createInstance("com.sun.star.awt.UnoControlButtonModel")
        With oMod
            .setPropertyValue("Label", oTables.ElementNames(i))
            .setPropertyValue("Name", "CmdTable"+i)
            .setPropertyValue("PositionX", 10)
            .setPropertyValue("PositionY", 12*i)
            .setPropertyValue("Height", 12)
            .setPropertyValue("Width", 100)
            '.setPropertyValue("Tag", s_buttons(3,i))
            .setPropertyValue("FontHeight",9)
            .setPropertyValue("FocusOnClick",false)
            .setPropertyValue("Tabstop",true)
        End With
        odlgTablesModel.insertByName("CmdTable"+i, oMod)
    next i 
 
    odlgTables.setModel(odlgTablesModel) 
    if bScrollbar Then    
    REM ********Scrollbarlisterner
    oAdjustmentListener = createUnoListener("AdjustmentListener_", "com.sun.star.awt.XAdjustmentListener")
    oScrollbarControl = odlgTables.getControl("Scrollbar")
    oScrollbarControl.addAdjustmentListener(oAdjustmentListener)
    endif
    REM ********** ActionListener erzeugen und Schaltflaechen zuordnen
    ocmd_ActionListener = createUnoListener("cmd_ActionListener_", "com.sun.star.awt.XActionListener")
    for i = 0 to Ubound(oTables.ElementNames)
       oControl = odlgTables.getControl("CmdTable"+i)
       oControl.model.Align = 0
       oControl.addActionListener(ocmd_ActionListener)
    next i
 
    REM ********** Mittels des Modells den Dialog anzeigen
    oWindow = CreateUnoService("com.sun.star.awt.Toolkit")
    odlgTables.createPeer(oWindow, null)
    Dim oWindowsListener as Object
    oTopWindowsListener = CreateUnoListener( "Top_Win_", "com.sun.star.awt.XTopWindowListener" )
    odlgTables.addTopWindowListener(oTopWindowsListener)
    odlgTables.setVisible(True)
End Sub

sub Set_columns_Description(sTableName)
    Dim oWindow As Object
    Dim oMod As Object
    Dim i As Integer

    oTable = oTables.getbyName(sTableName)
    nColumns = Ubound(oTable.Columns.ElementNames) + 1
    nTextFieldHeight = 104
    ndlgHeight = 14*(nColumns+4)    
    
    REM ***** Initialisierung der Eigenschaften des Dialogs
    odlgColumnsModel = CreateUnoService("com.sun.star.awt.UnoControlDialogModel")
    
    With odlgColumnsModel
       .setPropertyValue("PositionX", 320)
       .setPropertyValue("PositionY", 111 )
       '.setPropertyValue("FontName", Font)
       .setPropertyValue("Width", 400)
       .setPropertyValue("Height", ndlgHeight)
       .setPropertyValue("Title", "enter Columns Descriptions of table: " & sTableName)
       .setPropertyValue("Name", "DLGDescriptions")
       '.setPropertyValue("DesktopAsParent", True )
    End With
    
    odlgColumns = CreateUnoService("com.sun.star.awt.UnoControlDialog")   
    
    REM ********** Schaltflaechen erzeugen	
    for i = 0 to Ubound(oTable.Columns.ElementNames)
    
    REM ********** Textlabel erzeugen
    oMod = odlgColumnsModel.createInstance("com.sun.star.awt.UnoControlFixedTextModel")
    With oMod
       .setPropertyValue("Label", oTable.Columns.ElementNames(i))
       .setPropertyValue("Name", "lblColumn"+i)
       .setPropertyValue("PositionX", 10)
       .setPropertyValue("PositionY", 14*(i+1))
       .setPropertyValue("FontHeight",9)
       .setPropertyValue("Height",12)
       .setPropertyValue("Width", 60)
    End With
        odlgColumnsModel.insertByName("lblColumn"+i, oMod)

    REM ********** Textfeld erzeugen
    oMod = odlgColumnsModel.createInstance("com.sun.star.awt.UnoControlEditModel")
    With oMod
       .setPropertyValue("Name", "txtColumnDescription"+i)
       .setPropertyValue("PositionX", 80)
       .setPropertyValue("PositionY", 14*(i+1))
       .setPropertyValue("Height",12)
       .setPropertyValue("Width", 310)
       .setPropertyValue("Border",2)
       .setPropertyValue("VerticalAlign",0)'            com.sun.star.style.VerticalAlignment
       .setPropertyValue("MultiLine",TRUE)
       .setPropertyValue("FontHeight",10)
       .setPropertyValue("FontName","Courier New")
       .setPropertyValue("Text",oTable.Columns(i).HelpText)
    End With
    odlgColumnsModel.insertByName("txtColumnDescription"+i, oMod)
    next i 

       oMod = odlgColumnsModel.createInstance("com.sun.star.awt.UnoControlButtonModel")
        With oMod
            .setPropertyValue("Label", "OK")
            .setPropertyValue("Name", "CmdOK")
            .setPropertyValue("PositionX", 10)
            .setPropertyValue("PositionY", 14*(i+2))
            .setPropertyValue("Height", 12)
            .setPropertyValue("Width", 380)
            '.setPropertyValue("Tag", s_buttons(3,i))
            .setPropertyValue("FontHeight",9)
            .setPropertyValue("FocusOnClick",false)
            .setPropertyValue("Tabstop",true)
            .setPropertyValue("PushButtonType",com.sun.star.awt.PushButtonType.OK)
        End With
        odlgColumnsModel.insertByName("CmdColumn"+i, oMod)
    odlgColumns.setModel(odlgColumnsModel) 
    REM ********** Mittels des Modells den Dialog anzeigen
    oWindow = CreateUnoService("com.sun.star.awt.Toolkit")
    odlgColumns.createPeer(oWindow, null)
    Dim oWindowsListener as Object
    oTopWindowsListener = CreateUnoListener( "Top_Win_", "com.sun.star.awt.XTopWindowListener" )
    odlgColumns.addTopWindowListener(oTopWindowsListener)
    odlgColumns.setVisible(True)
    if  odlgColumns.execute = 1 then
        for i = 0 to Ubound(oTable.Columns.ElementNames)
        oTable.Columns(i).HelpText =  odlgColumns.GetControl("txtColumnDescription"+i).Text
        next i
    endif
    Thisdatabasedocument.store
    msgbox ("Done, Columns descriptions where registered in Table """ & oTable.name &""" ✔ " & chr(13) & "The .odb file was saved!",64,"Descriptions were registered")
end sub

Sub AdjustmentListener_adjustmentValueChanged(event)
    nScrollbarValue = event.Value
    nDelta = ((nHeightButtons - 200)/100) * nScrollbarValue 'max Hoehe 200
    for i = 0 to Ubound(oTables.ElementNames)
        oMod = odlgTables.Model.getbyName("CmdTable"+i)
        oMod.PositionY = (12* i) - nDelta
    next i
End Sub

Sub AdjustmentListener_disposing
End Sub

Sub cmd_ActionListener_actionPerformed(oEv)
    odlgTables.setVisible(False)
    Set_columns_Description(oEv.Source.model.Label)
end sub

Sub cmd_ActionListener_disposing(oEv)
End Sub   

'Diese Routinen werden von dem XTopWindowListener benötigt
Sub Top_Win_windowClosing( oEvent ) 'Dialog schließen
    oEvent.source.setVisible(False)
    'odlgTables.dispose
End Sub

Sub Top_Win_disposing( )
End Sub
Sub Top_Win_windowOpened   ( oEvent )
End sub
Sub Top_Win_windowClosed   ( oEvent )
End sub 
Sub Top_Win_windowMinimized   ( oEvent )
End sub
Sub Top_Win_windowNormalized   ( oEvent )
End sub
Sub Top_Win_windowActivated   ( oEvent )
Top_Win_windowDeactivated = false
End sub
function Top_Win_windowDeactivated ( oEvent ) as boolean
Top_Win_windowDeactivated = true
End function

[Rizwan Akhtar]

You can usually add or update details in the 'Description' column by editing the table structure and entering the text you want for each field.