filtering in bsvw

nkuebelbeck

VIP Member
Is there any possible way to filter a bsvw in the design aid?

AFAIK, you can only do this in a form with FC and QBE or in a report with data selection.

Just making sure i'm not missing out..
 

Larry_Jones

Legendary Poster
Not sure what you mean "filter a bsvw in the design aid?".

Do you want a static filter or a dynamic (changeable at runtime) filter?

If static you can use a virtual table technique to perform your filtering in a database query that is presented to JDE as a table.
 

nkuebelbeck

VIP Member
Not sure what you mean "filter a bsvw in the design aid?".

Do you want a static filter or a dynamic (changeable at runtime) filter?

If static you can use a virtual table technique to perform your filtering in a database query that is presented to JDE as a table.

Do you have a DOC ID or link to what technique you are referring to?
 

Larry_Jones

Legendary Poster
As far as I know this is a "JDEList" term and you can find many posts about it using Virtual Table as the keywords.
It refers to a technique of defining a database query (usually but not necessarily in PRODDTA schema) that accomplishes one or more of the following:
1. does something a bit more complex then JDE can readily handle in a view
2. Allows JDE to access non JDE Tables
3. Overcomes performance issues thru feats of database sql legerdemain

So basically you create your DB Query and name it according to JDE standards - F55XXXX.
Column names in query similarly must have corresponding JDE field names with table prefix and valid alias names.
Data Types, decimals, dates, etc must be in JDE format.

Then in JDE Table Design Aid you define that "Virtual Table" as if you were going to have a real table with that name - but you never generate the table (or you define the table first in JDE, generate it, drop the table in the DB, and create the query in the DB - same thing).

As far as JDE is concerned its now a real table that you can query. Adding/Updating generally doesn't happen unless your Virtual Table is based off a real JDE table with just some filtering or some such.

Hope that answers the question.
 

johndanter

Legendary Poster
You can drop the view and create in SQL to do whatver you want yes. So select sum etc. No reason why you then couldn't add Where clauses too.

So create your table, no need to do the generation but it doesn't do any harm.
SQL Drop it on the database.
Create a view with the same name as your dropped new table.
Then in here do whatever you want.

A fetch to this table will now just called the new SQL view. E1 is happy as Larry. You can even point to external tables this way.

One thing I sometimes do is create joins on the main table to a worktable that has the fields I want to filter on with the values already to records.
This way the main BSVW only returns records that match.

Example a table could have a column with values X Y Z in there but I only need X
I'll create a joined BSVW joining main table A to new worktable B on the column that can contain X Y Z
I create a record in worktable B with X in it and then select from the BSVW..... away you go.
 
Last edited:
Top