StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Filter BO2 "where BO2 pk not in (Select fkBO2 from BO1)"

Filter BO2 "where BO2 pk not in (Select fkBO2 from BO1)"

Charles R Hankey — Wed, 09 Sep 2009 08:07:00 GMT

I have a BO1 typed to a table which has an FK that is the PK of BO2 ( no relationship setup between the BOs )

I want to filter BO2 to exclude records where the pk is found in the FKs of BO1

Guidance as to SF best practice for this appreciated :)

RE: Filter BO2 "where BO2 pk not in (Select fkBO2 from BO1)"

Charles R Hankey — Wed, 09 Sep 2009 08:07:00 GMT

I know the techniques you are talking about and they work very well. This is something that came up in a wizard, where there is not really a parent-child relationship, but rather a situation where there is a checkedlistbox on page1 that displays PDF names. They are derived from a table which has, as one of its fields, the PK of the template (in the Templates table) used to generate the PDF in the first place.

The user selects PDFs.

The second page has a checked list box of templates which the user may select to generate further PDFs, but we don't want to show templates for PDFs that have already been generated.

So, on leaving page1, I am pulling the iTemplatekey from the PDFs bo based on the collection of PDF pks from the checkedlistbox. They become a collection to compare against the PKs in the TemplatesBO and set a filter.

I can visualize many instances of clientside data munging where this stuff is going to come in handy. I love the stuff built into the BOs on parent-child relationships, but this just adds another technique for stuff like this wizard thing.

( In exploring other ways to play with subsets of data already pulled, yesterday McGuffey dazzled me by figuring out filling list controls on the fly from the results of a LINQ query. Very useful in these kinds of situations. I'm getting pretty excited about LINQ in conjunction with the SF framework )

RE: Filter BO2 "where BO2 pk not in (Select fkBO2 from BO1)"

Trent L. Taylor — Tue, 08 Sep 2009 16:35:37 GMT

Well, you could go about this several ways. You could take the Custom BBS approach like you do for a report and create collections. Look at the StrataFlix sample to see how to do this. Fo example, take a CustomersBO and an OrdersBO. When I am on the customer record I want to reference the orders for that customer and have things already filtered out:

[codesnippet]Customers.Orders[0][/codesnippet]

In the above example, you would go ahead and retrieve all of the records that you need then have the class automatically filter these out. Again, instead of me re-hashing a lot of code, look at the reporting samples that come with the StrataFlix sample. Specifically the data source of one of the reports.

The other is to use the auto-filter options of the BO, but until I hear back then this might be a better avenue.

RE: Filter BO2 "where BO2 pk not in (Select fkBO2 from BO1)"

Charles R Hankey — Mon, 07 Sep 2009 15:53:02 GMT

just to keep from confusing lurkers - to get the keystring the line that precedes the above code :

[codesnippet] dim keystring As String = keyarraytoString(_ExcludedTemplatekeys)[/codesnippet]

where _ExcludedTemplateKeys is a property holding the list of key gathered from the other business object.

RE: Filter BO2 "where BO2 pk not in (Select fkBO2 from BO1)"

Charles R Hankey — Mon, 07 Sep 2009 15:49:07 GMT

I reread your post, Trent, and thought I might be missing something glaringly obvious ( Lord knows it would not be the first time :) )

I thought perhaps you were telling me that the

[codesnippet]MyBO2.Filter = "FKField = " + MyBO1.PK.ToString()[/codesnippet]

would test FKField against a collection of all the pks in MyBO1. I even tried it as MyBO1.PK().Tostring()

But it seems it just compares it to the currentrow value, so would only get one record. Did I misunderstand or do it wrong or it that just the way it is?

I created a function to return a string of integers from my keylist and can build the filter that way, but I still feel there is some magic I'm missing

( I thought this function might already exists somewhere in the framework but couldn't find it )

[codesnippet] Public Shared Function keyarraytoString(ByVal keyarray As List(Of System.Int32)) As String
Dim keystring As String = ""

For Each key In keylist
keystring += key.ToString()
If keylist.IndexOf(key) <> keylist.Last Then
keystring += ","
End If
Next

Return keystring

End Function
[/codesnippet]

So then I filter my templates :

[codesnippet] Me.TemplatesBO1.Filter = "ikey not in (" & keystring & ")"[/codesnippet]

It works fine and I can certainly create overloads of the function to handle other types in the passed in list.

But I can't help but feel that while it is [b]a[/b] solution it is not [b]the[/b] solution. :cool:

RE: Filter BO2 "where BO2 pk not in (Select fkBO2 from BO1)"

Charles R Hankey — Mon, 07 Sep 2009 13:11:22 GMT

A little more complex than that (perhaps)

I want to create a filter on BO2 based on a collection of values in BO1.

I have a list of bo1.itemplatekeys (not the pk)

I want to filter BO2 such that BO2.ikey is not in that list.

Basically a subquery Select * from BO2 where BO2 not in ( Select BO1.itemplateskey from BO1)

If my list is keylist, I have tried

BO2.filter = "keylist.contains(bo2.ikey) = false"

but get an error (at runtime) saying I am trying to use an undefined function in a filter

RE: Filter BO2 "where BO2 pk not in (Select fkBO2 from BO1)"

Trent L. Taylor — Mon, 07 Sep 2009 11:45:22 GMT

So really you just want to create a filter, correct? So the most simple case, unless I am missing something, would be applying a filter like this:

[codesnippet]MyBO2.Filter = "FKField = " + MyBO1.PK.ToString();[/codesnippet]

When you do this and add new records, however, you will want to remove the filter BEFORE adding a new record, set the filter properties, then reapply the filter. Also keep in mind that you can use the MyBO.RegisterForeignKey command to have the BOs manage the FKs for you even if they are not setup within the database. Just FYI. :)