Can Lotus Embedded views be used to filter fields? Just like where" in SQL

As the title says, I want to achieve the effect of a purchase order. All product details will be displayed using the embedded view, but I want to display the ReceiptID of specific product details.

I've been looking for a solution for a long time but still can't find it.

There are multiple ways to do this.

Easiest: Single category view

The "easiest" way is to use a so called "single category view". But this possibility is quite restricted and works best if you only have one value to filter for. And you can only filter for "exact" values.

You create a computed field called "SingleCategory" and hide it just between your input field and the view. Then you make the first category of the embedded view a category. In the properties of the embedded view within your form you enter SingleCategorie as formula for the property "Show single category".

Now lets assume you only want to filter by "ReceiptID". Then the formula for your hidden field "SingleCategory" is:

ReceiptID

And the formula for the column in your view is:

ReceiptID

Now you just enter a ReceiptID in the field, press F5 or F9 (can later be automatic) and the view will only show the entries with exactly that ReceiptID.

If you want to filter for e.g. ReceiptID and ProductID (both being entered or only one of them), then the formula for SingleCategory and the categorized column becomes more complicated:

SingleCategory:

@If( ReceiptID = ""; "-" ; ReceiptID ) + "~" + @If( ProductID = ""; "-" ; ProductID )

Column in view:

@Trim(ReceiptID : "-") + "~" *+ @Trim( ProductID : "-" )

Here the "*+" operator in the column formula is not a typo: It is the so called "permutation operator": It creates a permutated list of all possible combinations.

Lets take the example ReceiptID = "A12345" and ProductID = "P99999" then the document would show in 4 categories in the view:

A12345~P99999
A12345~-
-~P99999
-~-

You see: Depending on the selection in your dialog you will always find this document either if you only select a ReceiptID (then ProductID = -) or only a ProductID (then ReceiptID = -).

But the more columns you add, the more permutations for the column you get and the bigger you view becomes.

Lets add Quantity to the example.

SingleCategory:

@If( ReceiptID = ""; "-" ; ReceiptID ) + "~" + @If( ProductID = ""; "-" ; ProductID ) + "~" + @If( Quantity = ""; "-" ; Quantity ) 

Column formula:

(@Trim(ReceiptID : "-") + "~" *+ @Trim( ProductID : "-" ) + "~") *+ @Trim( Quantity : "-" )

Document values for Quantity = 100:

A12345~P99999~100
A12345~-~100
A12345~P99999~-
A12345~-~-
-~P99999~100
-~-~100
-~P99999~-
-~-~-

Harder but more flexible: with a user search folder
Instead of showing a view you could show a "Search folder". You'd need one search folder for every user as otherwise the users would mess with the other users filter.

You then write a little script that takes your input and creates a search formula, then searches all documents with this formula and puts it in the search folder. Don't forget to empty the folder before putting documents in it.

The base code that could be behind a "filter" button in the form could look like this, assumed "docSearch" is the document from your screenshot:

Dim ses as New NotesSession
Dim db as NotesDatabase
Dim query as String
Dim dc as NotesDocumentCollection
Dim folderTarget as NotesView

Dim searchFolderName as String

searchFolderName = "NameOfUserSearchFolder"

Set db = ses.CurrentDatabase
Set folderTarget = db.GetView( searchFolderName )
Call folderTarget.Allentries.RemoveFromFolder( searchFolderName )

query = {@Contains( ReceiptID ; "} & docSearch.ReceiptID(0) & {") & } &_
  {@Contains( ProductID ; "} & docSearch.ProductID(0) & {") & }

Set dc = db.Search( query, Nothing, 0 )
Call dc.PutInFolder( searchFolderName )

This approach is way more flexible (as it can use @Contains instead of exact maching) but need a lot of expertise in LotusScript to make it right, but I got it to run successfully in different environments