Accessing Lookup Tables via API

[czentman]

This data error is not because of null values. I know this because I deleted all data (that I was able to figure out with this lookups service). THen I went to the regular SPAdmin- lookup manager- and added 1 record with every single field filled in. Then I went into my lookup and attempted to edit. I put a break point on the data error function and it still goes into with showing the exception as "Formatted value of the cell has a wrong type". This is before I do any edits, just clicking on a cell, and the entire grid (1 record) has everything filled out, so it's not because of null values.

[czentman]

I went back to trying to use a plain datagrid so I can avoid those formatexception errors. I don't have errors but I have something weird that I cannot edit any data in my grid. I can add and remove rows - and even make those add/remove changes in the lookup table, but for some reason, the grid won't allow me to update data or enter any new data in a new row. Any ideas?

[John Morris]

When using the lookup table API, the returned DataTable does not contain simple data types (string, int, bool). Instead, each individual value is of a type called a LookupValue (from the SoftPro.OrderTracking.Common namespace).

This type is used to encapsulate the actual value *and* its other attributes, such as whether or not the value is actually a formula. The reason you can see the values in the grid is becuase the LookupValue.ToString() method is written to show the value it contains, as a string.

The DataGrid is not allowing you to edit because it doesn't know how to edit a LookupValue type. When using the DataGrid, you would need to provide your own custom DataGrid type editor in order to resolve the problem. This can be done by using the customization capabilities of the DataGrid control. Microsoft (MSDN) should have a white paper somewhere on how to accomplish this task.

[jcreyes]

How do we determine the correct value to assign to the IsFormula property of the LookupValue when we try to insert a new row in a given Lookup table?

[John Morris]

Lookup values can be formulas. In most cases, they are not. The answer to your question depends on your data. Formulas allow the application to 'calculate' the value that's lookuped up, instead of simply inserting a static value.

[jcreyes]

Thanks John! Another question - what is the best/quickest way to delete all records for a given Lookup table? I'm currently using the code snippet below:

Code: Select all

 Public Shared Sub ClearLookupTable(ByVal lookupSvc As Lookups, ByVal tableName As String)

            Dim table As DataTable = lookupSvc.GetTable(tableName)

            For Each drow As DataRow In table.Rows
                drow.Delete()
            Next

            lookupSvc.ApplyChanges(table)

End Sub

[John Morris]

That is the way to do it.

Keep in mind that if you have a lot of records, you may need to do this in a "chunked" fashion to help avoid timeout issues while talking to the server.

[czentman]

Do you have any examples for using the filter?

Also, when I show the Order Contact - Person table, I am viewing records per order, unline the canned one in SPAdmin-Managers-Lookups. Should I be using this filtering tool? What should I be filtering on? I want to get a distinct on the Person's table. For some reason, different values are appearing in the Title field including a field code. Why is that?

[John Morris]

Currently, the filter only supports a very simple syntax. We allow only one condition to be applied. Such as "Field=MyPattern*"

The wildcard tokens are the same can be used in the search screens.

[czentman]

Do you know why it's showing several records for one entry? How do get distinct?