StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Setting up a grid / BO with related fields

Setting up a grid / BO with related fields

thegwill — Fri, 28 Sep 2007 22:55:58 GMT

In my database I have TableA which has a FK to TableB

In my project I have BizObA which consists of fields from TableA. I would also like it to include a field from TableB as it's related via an FK

If I were writing the SQL it would be something like:

SELECT A.Field, A.AnotherField, B.SomeField
FROM TableA A
INNER JOIN TableB B
ON A.FK = B.PK

...but I'm not sure how this is achieved using the BO Mapper so that BizObA truly represents my business object and not just reflects a table structure

I guess I could have a separate BizObB to point at TableB and stitch them together but this seems like a bad idea

Once I have my BizObB I want to bind it to a grid using the BBS...

RE: Setting up a grid / BO with related fields

Edhy Rijo — Fri, 28 Sep 2007 22:55:58 GMT

Hi Greg,

Thanks for such a detail response, it helps me a lot.

RE: Setting up a grid / BO with related fields

Greg McGuffey — Wed, 26 Sep 2007 18:53:40 GMT

Glad that helped :D

RE: Setting up a grid / BO with related fields

Peter Jones — Wed, 26 Sep 2007 16:00:27 GMT

Hi Greg,

Thanks for taking the time to write Arrays 101 - your comments are indeed very helpful.

Cheers, Peter

RE: Setting up a grid / BO with related fields

Greg McGuffey — Wed, 26 Sep 2007 12:27:26 GMT

Yeah, that whole curly bracket thing confused the heck out of me at first too. It is related to arrays. First lets look at some simple array examples:

[codesnippet]' An array of strings
Dim names As String()
' An array of integers
Dim ages As Integer()
'An array of FieldPropertyDescriptors
Dim descriptors As FieldPropertyDescriptor()[/codesnippet]

You probably already figured out this is how you dim an array. Of course, then you have to Redim it to the correct size and then add each item, which is a pain lots of times:

[codesnippet]' Dim an array of names
Dim names As String()
'...later in code, size and fill the array
Redim names As String(2)
names(0) = "Joe"
names(1) = "John"[/codesnippet]

As you likely know, with other types of variables, you can just set the value or initialize the variable when you dim it. But how do you do that with an array? Arrays need to be sized before adding elements to them and the elements are added by index. That is were the curly brackets come into play. The curly brackets are array initializers and allow you set the initial values of the array:

[codesnippet]' An array of strings
Dim names As New String() {"Joe","Bob","Sam"}
' An array of integers
Dim ages As Integer() {23, 34, 56}
'An array of
Dim descriptors As FieldPropertyDescriptor() { _
New MicroFour.StrataFrame.Business.ReflectionPropertyDescriptor( _
"Product", GetType(boPUNAllocatedToFleshBatch)), _
New MicroFour.StrataFrame.Business.ReflectionPropertyDescriptor( _
"BinCode", GetType(boPUNAllocatedToFleshBatch))}[/codesnippet]

So, the curly brackets simply allow you to initialize the array with values when you create the array. Note that you do [b]NOT[/b] set the size of the array when you use an initializer (the curly brackets). You can also use initializers with multi dimension arrays. In that case, you need to set the rank of the array, but not the size:

[codesnippet]' Array of names, first in 0 and last in 1
Dim names As String(,) { {"Joe", "John"}, {"Smith","Simpson"} } [/codesnippet]

Hope that helps!

RE: Setting up a grid / BO with related fields

Peter Jones — Wed, 26 Sep 2007 00:27:02 GMT

Hi Guys,

Yes - it all worked as advertised (in the SF Help) - thanks a lot. The only issue I had (being a .Net newbie) was how to get all my (seven off) custom fields into GetCustomBindablePropertyDescriptors. This turned out to be easy but I thought I would post the code in this thread in case someone else has similare issues. It hasn't sunk in what yet as to the special meaning of the swiggly braket but my offsider pointed out what I needed to do.

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

        Return New MicroFour.StrataFrame.Business.FieldPropertyDescriptor() { _
             New MicroFour.StrataFrame.Business.ReflectionPropertyDescriptor( _
            "Product", GetType(boPUNAllocatedToFleshBatch)), _
             New MicroFour.StrataFrame.Business.ReflectionPropertyDescriptor( _
            "BinCode", GetType(boPUNAllocatedToFleshBatch)), _
             New MicroFour.StrataFrame.Business.ReflectionPropertyDescriptor( _
            "ReceiveBatch", GetType(boPUNAllocatedToFleshBatch)), _
             New MicroFour.StrataFrame.Business.ReflectionPropertyDescriptor( _
            "FleshingBatch", GetType(boPUNAllocatedToFleshBatch)), _
             New MicroFour.StrataFrame.Business.ReflectionPropertyDescriptor( _
            "ShortTermBatch", GetType(boPUNAllocatedToFleshBatch)), _
             New MicroFour.StrataFrame.Business.ReflectionPropertyDescriptor( _
            "TanningBatch", GetType(boPUNAllocatedToFleshBatch)), _
             New MicroFour.StrataFrame.Business.ReflectionPropertyDescriptor( _
            "OrderNo", GetType(boPUNAllocatedToFleshBatch))}
    End Function

Cheers, Peter

RE: Setting up a grid / BO with related fields

thegwill — Tue, 25 Sep 2007 13:31:32 GMT

Thanks for the tip(s) guys. The day job is taking over at the moment so I'll give it a try soon...

BTW, it would be really helpful to have a "recipe" book of such topics for SF noobs like myself (you know the kind of thing, like the o'reilly books).

RE: Setting up a grid / BO with related fields

Greg McGuffey — Tue, 25 Sep 2007 11:32:08 GMT

Glad the headache is gone...that is never fun.

RE: Setting up a grid / BO with related fields

Trent L. Taylor — Tue, 25 Sep 2007 09:30:14 GMT

Ah...yeah, thanks Greg. That is correct...and a very important step :D I was trying to answer the forum questions last night with a pounding headache...stupid I know :blush: .... but today...headache gone and back to work :D

RE: Setting up a grid / BO with related fields

Greg McGuffey — Mon, 24 Sep 2007 22:18:57 GMT

[quote]You do not have to actually have the field strong-typed through the BO mapper. Let assume that you want to create an alias field or produce a custom field via your query, then even without creating a custom property on the BO you can access that field through the Item property on the BO. The field will exist in the internal data table but it will just not have a strong-typed property. So you could then create a custom property to access it if the need exists.[/quote]

There are about a million bloody ways to access data with SF. I'm constantly amazed. I hadn't thought of this way at all....:blink:

RE: Setting up a grid / BO with related fields

Greg McGuffey — Mon, 24 Sep 2007 22:16:53 GMT

[quote]1)Create the BO on the table.
2)Use a stored procedure to populate the BO that has all the columns in the source table plus joins on the three FKs so I can extract the Name value associated with each FK value. This will give me three extra columns: Name1, Name2 and Name3.
3)I was then going to create three custom fields in my BO called (you guessed it) Name1, Name2 and Name3.
[/quote]

Just one more step:

4. Override the GetCustomBindablePropertyDescriptors method to include each of the custom properties that you want to be bindable. There is info about this in help (I think). If you have trouble, I'll post an example.

Note that if your custom properties are readonly, then the BO will never enable them for editing (assuming normal SF binding).

You might also want to track when you call the fill method that fills the BO with the FK names, so you can gracefully handle (or informatively throw an exception) if you access on of the properties when those extra columns are in the data table.

RE: Setting up a grid / BO with related fields

Trent L. Taylor — Mon, 24 Sep 2007 20:53:46 GMT

thegwill, if I understand what you are trying to accomplish, then I am going to add to Greg's comment. You do not have to actually have the field strong-typed through the BO mapper. Let assume that you want to create an alias field or produce a custom field via your query, then even without creating a custom property on the BO you can access that field through the Item property on the BO. The field will exist in the internal data table but it will just not have a strong-typed property. So you could then create a custom property to access it if the need exists. Let me know if this is what you are trying to accomplish.

RE: Setting up a grid / BO with related fields

Peter Jones — Mon, 24 Sep 2007 18:58:01 GMT

Hi Guys,

A fortuitous thread - I think I have the a similar issue and was going to go down the custom field path but, before I start working out how to do that, perhaps you can say if the custom field approach will work in my case.

I have a BO based on a table that has three FKs which are not maintainable in the BO however I want to show the “Name” associated with each FK when I display the row details in a grid. My cunning plan was:

1) Create the BO on the table.
2) Use a stored procedure to populate the BO that has all the columns in the source table plus joins on the three FKs so I can extract the Name value associated with each FK value. This will give me three extra columns: Name1, Name2 and Name3.
3) I was then going to create three custom fields in my BO called (you guessed it) Name1, Name2 and Name3.

I then “hoped” that when I created my Business Binding Source the custom fields would be visible so that I could include them in my grid and these fields would be automatically bound to Name1 etc from the stored proc. Is this the way it works?

Cheers, Peter

RE: Setting up a grid / BO with related fields

thegwill — Mon, 24 Sep 2007 14:53:45 GMT

Thanks Greg but this isn't really what I'm after. I understand the custom field concept but I am looking to retrieve the "custom field" from a related table as per the SQL

If I were to follow the "custom field" approach then surely I would need to hit the database again? This is what I am trying to avoid...

RE: Setting up a grid / BO with related fields

Greg McGuffey — Mon, 24 Sep 2007 11:02:40 GMT

Check out custom fields in help. I do this all the time. They can be setup to be bindable also.