StrataFrame Forum » StrataFrame Application Framework - V1 » WinForms (How do I?) » Show lookup description data in a grid or listview

Show lookup description data in a grid or listview

Edhy Rijo — Tue, 13 Nov 2007 11:39:51 GMT

Hi,

I am working on a form with a grid or listview which will show data from a related table as a lookup and I would like to show a descritive fields instead of the Foreing Key field value, how can I do this?

In the picture attached, the FK_Appliances in the Grid or Appliance in the List View are the ones I want to show the descriptive field instead of the FK value.

Also, since I am learning SF, I found that the creation of a ListView is a bit difficult than using a GridView for read only data, is there a way to show the grid with empty columns like in the ListView in order to cover the gray area of the grid?

Thanks!

RE: Show lookup description data in a grid or listview

StrataFrame Team — Tue, 13 Nov 2007 11:39:51 GMT

No problem. You'll end up using those custom properties more than you think ;)

RE: Show lookup description data in a grid or listview

Edhy Rijo — Thu, 08 Nov 2007 21:39:13 GMT

Hi Trent,

After a second thought, I decided to try Tim's suggestion to create a Custom Property and after reading Tim's great sample and with the SF help file, I could not believe how easy it was to have 2 Custom Field Properties to handle this situation and not have to add a single line of code at the form level, which keep the things simple.

For the sake of new SF users, here is the code for one of my Custom Field Properties:

[quote][codesnippet]

''' <summary>

''' This property will return a formatted Building Address

''' </summary>

''' <remarks></remarks>

<Browsable(False), _

BusinessFieldDisplayInEditor(), _

Description("CustomerBuildingAddress"), _

DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)> _

Public ReadOnly Property [CustomerBuildingAddress]() As System.String

Get

Dim loBusinessObject As New BuildingsBO

loBusinessObject.FillByPrimaryKey(Me.FK_Buildings)

If loBusinessObject.Count = 1 Then

Return String.Format("{0}, {1} {2} {3}", loBusinessObject.Street, loBusinessObject.City, loBusinessObject.State, loBusinessObject.Zip)

Else

Return String.Empty

End If

' Release the Business Object

loBusinessObject.Dispose()

End Get

End Property

[/codesnippet][/quote]

Once again, big thanks to Tim and Trent for the lecture on how to accomplish this task. :hehe:

RE: Show lookup description data in a grid or listview

Trent L. Taylor — Thu, 08 Nov 2007 17:51:34 GMT

It sounds like you do not have the column setup as PopulatedThroughEvent. Verify that and let me know.

RE: Show lookup description data in a grid or listview

Edhy Rijo — Thu, 08 Nov 2007 15:13:49 GMT

Hi,

Sorry about that, it was a copy/paste problem, here is the code::cool:

[quote][codesnippet]

Private Sub ServiceCallsBO1_BrowseDialog_RowPopulating(ByVal e As MicroFour.StrataFrame.UI.Windows.Forms.RowPopulatingEventArgs) Handles ServiceCallsBO1_BrowseDialog.RowPopulating

'-- Get the Appliance Name using the FK_Appliances field value.

With CType(e.BusinessObject, ATR_BO_Library.ServiceCallsAppliancesBO)

Dim loAppliancesBO As New ATR_BO_Library.AppliancesBO

e.Values(1).DisplayValue = loAppliancesBO.GetApplianceName(.FK_Appliances)

'-- Clean Up the new Business Object

loAppliancesBO.Dispose()

End With

End Sub

[/codesnippet][/quote]

RE: Show lookup description data in a grid or listview

Trent L. Taylor — Thu, 08 Nov 2007 14:52:02 GMT

Look real close at your code ;)

With CType(e.BusinessObject, ATR_BO_Library.ServiceCallsAppliancesBO)

Dim loBO As New ATR_BO_Library.AppliancesBO

e.Values(1).DisplayValue = loBO.GetApplianceName(.FK_Appliances)

End With

Here is a tip...it isn't the Browse Dialog :D

RE: Show lookup description data in a grid or listview

Edhy Rijo — Thu, 08 Nov 2007 14:44:02 GMT

Hi Trent,

In a Browser Dialog, I need also to show a descriptive field name and tried using the same code in the ServiceCallsBO1_BrowseDialog_RowPopulating, but it generates this error:

[quote][codesnippet]InvalidCastException
Unable to cast object of type 'ATR_BO_Library.ServiceCallsBO' to type 'ATR_BO_Library.ServiceCallsAppliancesBO'.

Source : ATR System

Stack Trace:
   at ATR_System.frmServiceCalls.ServiceCallsBO1_BrowseDialog_RowPopulating(RowPopulatingEventArgs e) in E:\Visual Studio 2005\StrataFrame Projects\ATR Systems\ATR System with not security\ATR System\Forms\frmServiceCalls.vb:line 235
   at MicroFour.StrataFrame.UI.Windows.Forms.BrowseDialogwindow.CreateListViewItem()
   at MicroFour.StrataFrame.UI.Windows.Forms.BrowseDialogwindow.LoadBrowseResults()
   at MicroFour.StrataFrame.UI.Windows.Forms.BrowseDialogwindow.ExecuteSearch()
   at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
   at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ToolStrip.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativewindow.WndProc(Message& m)
   at System.Windows.Forms.Nativewindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
[/codesnippet][/quote]

Here is the code in ServiceCallsBO1_BrowseDialog_RowPopulating:

[quote][codesnippet]

With CType(e.BusinessObject, ATR_BO_Library.ServiceCallsAppliancesBO)

Dim loBO As New ATR_BO_Library.AppliancesBO

e.Values(1).DisplayValue = loBO.GetApplianceName(.FK_Appliances)

End With

[/codesnippet][/quote]

What is wrong with this approach in the Browser Dialog?

RE: Show lookup description data in a grid or listview

Edhy Rijo — Wed, 07 Nov 2007 18:46:59 GMT

[quote][b]Trent L. Taylor (11/07/2007)[/b][hr]

I don't know if you knew this, but you can create a parameter and provide the value all in one statement:

[codesnippet]command.Parameters.AddWithValue("@myParm",MyValue).SqlDbType = SqlDbType.VarChar[/codesnippet]

It is a handy little trick :D[/quote]

Hi Trent,

Thanks again, for checking the code. And please do not assume what I would know for know, since I am learning VB.NET, SF, SQL2005 and more at the same time, so there are many things I don't know or fully understand yet. ;)

I replaced the two command lines with the single one and it is much cleaner. I also kind a like the Scalar methods now that I know they exist :D

RE: Show lookup description data in a grid or listview

Trent L. Taylor — Wed, 07 Nov 2007 17:36:36 GMT

It looks good. It depends on what I am trying to accomplish, but I use a lot of scalar methods for this type of thing since there is little overhead. However, if there could be a lot of records coming back, then it is usually a good idea to make sure it is included in the query somehow. But you code looks good :)

I don't know if you knew this, but you can create a parameter and provide the value all in one statement:

[codesnippet]command.Parameters.AddWithValue("@myParm",MyValue).SqlDbType = SqlDbType.VarChar[/codesnippet]

It is a handy little trick :D

RE: Show lookup description data in a grid or listview

Edhy Rijo — Wed, 07 Nov 2007 15:26:01 GMT

Hi,

For testing purpose in order to see which method will fit better to me, I started with an Scalar method as follow:

[quote][codesnippet]

''' <summary>

''' Get the Appliance Name using the FK_Appliances field value.

''' </summary>

''' <param name="e"></param>

''' <remarks></remarks>

Private Sub lvAppliances_RowPopulating(ByVal e As MicroFour.StrataFrame.UI.Windows.Forms.RowPopulatingEventArgs) Handles lvAppliances.RowPopulating

With CType(e.BusinessObject, ATR_BO_Library.ServiceCallsAppliancesBO)

Dim loBO As New ATR_BO_Library.AppliancesBO

e.Values(1).DisplayValue = loBO.GetApplianceName(.FK_Appliances)

End With

End Sub[/codesnippet][/quote]

Some things are different from Trent's post, like e.Column() which does not exist, so I tested e.Values() and that worked just fine! and in the CType() I needed to make reference to a new strong type BO class ATR_BO_Library.ServiceCallsAppliancesBO which is not the way in Trent's post.

So far, I think that an Scalar method will make sense for me because the code will be in the BO class, so I can reuse it all the time, and it was pretty simple to implement.

Here is the Scalar method code:

[quote][codesnippet]

''' <summary>

''' Get the correct Appliance Name via the PK_Appliances field

''' </summary>

''' <param name="AppliancePrimaryKey"></param>

''' <returns>

''' The Appliance Name as string

''' </returns>

''' <remarks></remarks>

Public Function GetApplianceName(ByVal AppliancePrimaryKey As Integer) As String

'-- Establish Locals

Dim loCommand As New SqlCommand()

'-- Build the command

loCommand.CommandText = "SELECT ApplianceName FROM Appliances WHERE PK_Appliances = @Appliances_PK"

'-- Create and set the parameters

loCommand.Parameters.Add("@Appliances_PK", SqlDbType.Int)

loCommand.Parameters("@Appliances_PK").Value = AppliancePrimaryKey

'-- Execute the query and return the value

Return CType(Me.ExecuteScalar(loCommand), String)

End Function

[/codesnippet][/quote]

Later on I will try Tim's way using a custom field property.

RE: Show lookup description data in a grid or listview

Edhy Rijo — Wed, 07 Nov 2007 12:18:06 GMT

Hi Tim, Trent,

Thanks for the ideas, I will play with them now and post result in a bit.:D

RE: Show lookup description data in a grid or listview

Trent L. Taylor — Wed, 07 Nov 2007 09:29:38 GMT

Edhy,

It is always nice to get other peoples perspectives as I am on the other side of the fence when it comes to the ListView vs. the Grid. I think that grids have many good uses, but for read-only data, I think they produce too much overhead and too many potential bugs since the grid is so versatile and has so many properties and different types of functionality to meet a wide-spectrum of needs.

We tried to design our ListView so that read-only data could be loaded very easily. Simply create the columns, determine if the column will be pulled from a field in the BO or populated through an event. For example, you can use the approach that Tim pointed out by creating a custom property...or you can create a scalar method, create a fill method that includes the columns you need, etc. then indicate that column will be "Populated Through Event." You will then handle the RowPopulating event of the ListView and set the column information for each row:

RowPopulatingEvent
With CType(e.BusinessObject, MyBO)
    e.Columns(1).DisplayValue = CType(.CurrentRow.Item("MyIncludedField"), String)

    '-- OR

    e.Columns(1).DisplayValue = .MyCustomProperty

    '-- OR

    e.Columns(1).DisplayValue = SomeBO.ExecuteAScalarMethod()

    '-- OR

    e.Columns(1).DisplayValue = (.CalcField1 + .CalcField2).ToString("n0")

EndWith

As you can see, there are many different ways to pull in data for a column that may come from another table or may be a calcualted field.

RE: Show lookup description data in a grid or listview

Tim Dol — Wed, 07 Nov 2007 07:49:17 GMT

Edhy,

I usually create custom field properties on the business object to handle these situations. Refer to the StrataFrame help 'Adding Custom Field Properties'. You can also use a database view.

Here is a sample of a custom field property:

<Browsable(False), _

BusinessFieldDisplayInEditor(), _

Description("SalesRep Name"), _

DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)> _

Public ReadOnly Property [SalesRepName]() As System.String

Get

Dim ef As New EmployeeFileBO()

ef.FillByPrimaryKey(Me.SalesRep)

If ef.Count = 1 Then

Return ef.LongName

Else

Return String.Empty

End If

ef.Dispose()

End Get

End Property

You will need to add a property descriptor if you want to bind the custom field to a grid.

Protected Overrides Function GetCustomBindablePropertyDescriptors() As MicroFour.StrataFrame.Business.FieldPropertyDescriptor()

'-- Create and return a new array of FieldPropertyDescriptor

' objects that contains the ReflectionPropertyDescriptor

' for the Licence Description field.

Return New MicroFour.StrataFrame.Business.FieldPropertyDescriptor() { _

New MicroFour.StrataFrame.Business.ReflectionPropertyDescriptor( _

"CustServiceRep", GetType(ClientFileBO))}

End Function.