Programmatically "use custom columns" and "manage grid"

[neil.interactit]

Hi there,

In this post http://forums.jiwa.com.au/viewtopic.php?f=26&t=407 I have used UserDefinedString1, displayed as "Ledger Number", to capture GL account (with some code to make this a lookup field) ... all good.

In my environment I have "use custom columns" selected and have "managed grid" to turn on UserDefinedString1 and display it as "Ledger Number". Rather than needing to have the client issue instructions to each of their accounts staff to do likewise, I am hoping to be able to force this programmatically. Essentially to check and set if necessary. I have hunted around - I can see the XML in a table but rather than bodge this directly I am hoping there's business logic available?

Cheers,
Neil.

[neil.interactit]

Actually, I have found a possible lead to an approach in one of the demo plugins in the database ... I will have a play and if I work it out I will post the result.

[neil.interactit]

No, I struck out - I thought I was onto something but it has stumped me! Any chance of a sample demo?

[Mike.Sheen]

No, I struck out - I thought I was onto something but it has stumped me! Any chance of a sample demo?

A working sample will have to wait a while - but I can point you in the direction that I would try.

The grid has a method for the sheet (i.e.: Grid.ActiveSheet) named GenerateXMLDefinition - it returns a string representation of the XML document of the grid columns. Load that into a System.Xml.XmlDocument, parse it, set the value for the node you want, the call the ApplyXMLDefinition method of the sheet passing the XmlDocument as a string value.

1. Get the grid XML as a string, make a XmlDocument from it:

Code: Select all

Dim XMLDefinition As String = MyGrid.ActiveSheet.GenerateXMLDefinition()
XMLDocument = New System.Xml.XmlDocument
XMLDocument.LoadXml(XMLDefinition)

2. Find your node of interest and set the value (incomplete code here - you'll need to work out how to efficiently find the node you want and set the value):

Code: Select all

xmlElement As System.Xml.XmlElement = XMLDocument.SelectSingleNode("JiwaGridDefinition/ListOfColumns")
If Not (xmlElement Is Nothing) Then
    For Each columnXMLElement As System.Xml.XmlElement In xmlElement.ChildNodes
            ' ... work out which node you want,  change the value here columnXMLElement as a Name property - "Key" is the column key, "Caption" is the display value
            If Found Then
                ' Generate a string representation of the XML document, and exit processing
                XMLString = XMLDocument.OuterXml
                Exit For
            End If
    Next
End If


3. Apply the new XML:

Code: Select all

MyGrid.ActiveSheet.ApplyXMLDefinition(XMLString)

Mike

[neil.interactit]

Thanks Mike,

Exactly what I needed ... here's the working snippet ... this makes UserDefinedString1 visible as "Ledger Number" ...

Code: Select all

Imports System.Xml
Imports JiwaFinancials.Jiwa.JiwaApplication.Controls
Imports System.Linq
'...
    Public Sub Setup(ByVal JiwaForm As JiwaApplication.IJiwaForm, ByVal Plugin As JiwaApplication.Plugin.Plugin) Implements JiwaApplication.IJiwaFormPlugin.Setup
        If Not TypeOf (JiwaForm) Is JiwaPurchaseOrdersUI.PurchaseOrders Then Return
        Dim purchaseOrderForm = DirectCast(JiwaForm, JiwaPurchaseOrdersUI.PurchaseOrders)
        SetupLedgerNumberColumn(purchaseOrderForm.LinesJiwaGrid.ActiveSheet)
    End Sub

    Private Sub SetupLedgerNumberColumn(activeSheet As Sheet)
        Dim xmlString = activeSheet.GenerateXMLDefinition()
        Dim xmlDocument = New XmlDocument
        xmlDocument.LoadXml(xmlString)
        Dim listOfColumns As XmlElement = xmlDocument.SelectSingleNode("JiwaGridDefinition/ListOfColumns")
        If listOfColumns Is Nothing Then Return
        For Each column As XmlElement In From col As XmlElement In listOfColumns.ChildNodes Where col.SelectSingleNode("Key").FirstChild().Value = "UserDefinedString1"
            If column.SelectSingleNode("Caption").FirstChild().Value = "Ledger Number" And column.SelectSingleNode("Hidden").FirstChild().Value = "0" Then Return
            column.SelectSingleNode("Caption").FirstChild().Value = "Ledger Number"
            column.SelectSingleNode("Hidden").FirstChild().Value = "0"
            Exit For
        Next
        activeSheet.ApplyXMLDefinition(xmlDocument.OuterXml)
    End Sub

Note that I've used the extracted method call to cast the FarPoint.Win.Spread.SheetView to JiwaApplication.Controls.Sheet.

Interestingly, there is no need to programmatically turn on "use custom columns" as this code happens after that stage and overides the grid display.

Cheers,
Neil.