Filter field in List View using Drop down menu items

[db_tragic]

Hi folks,

 

I'm trying to replicate (in a List view layout), a portal filter set up successfully on another layout, using a selection from a drop-down list to cause a list of invoices to be filtered by balance amount (=0.00, >0.00, <0.00, or 'Not Invoiced').

 

The portal filter calculation is as follows:

Client::InvoiceFilter = "Show All"
or
Client::InvoiceFilter = "Show Paid" and Invoice::Amount Due = 0 and not IsEmpty (Invoice::_pk_InvoiceID)
or
Client::InvoiceFilter = "Show Unpaid" and Invoice::Amount Due < 0 and not IsEmpty (Invoice::_pk_InvoiceID)
or
Client::InvoiceFilter = "Show In Credit" and Invoice::Amount Due > 0 and not IsEmpty (Invoice::_pk_InvoiceID)
or
Client::InvoiceFilter = "Not Invoiced" and IsEmpty ( Invoice::_pk_InvoiceID )

I'm having trouble trying to set up a script trigger for the drop-down list (contains items: Show All, Show paid, Show Unpaid, Show In Credit, Not Invoiced).

 

I've tried the following;

Enter Find Mode[]
If [Client::InvoiceFilter = "Show All"]
 Show All Records
Else If [Client::InvoiceFilter = "Show Paid" and not IsEmpty (Invoice::_pk_InvoiceID)]
 Set Field [Invoice::Amount Due = 0.00]
 Perform Find
Else If [Client::InvoiceFilter = "Show Unpaid" and not IsEmpty (Invoice::_pk_InvoiceID)]
 Set Field [Invoice::Amount Due < 0.00]
 Perform Find
Else If [Client::InvoiceFilter = "Show In Credit" and not IsEmpty (Invoice::_pk_InvoiceID)]
 Set Field [Invoice::Amount Due > 0.00]
 Perform Find
Else If [Client::InvoiceFilter = "Show Not Invoiced" and IsEmpty (Invoice::_pk_InvoiceID)]
 // Not sure on how to effect this find// Set Field ??
 Perform Find
End If

The above returns "…the provided find criteria is not valid……" dialogue when selecting any of the items in the filter drop-down list. 

 

My first time using an 'Else If' expression and obviously getting something wrong.  Perhaps I should be using 'Case' instead (which I tried unsuccessfully) - not sure. 

 

Some advice on where I've gone wrong would be appreciated.

 

TIA

 

I should add - the reason for including the 'not/IsEmpty……';

   The invoice list corresponds to client visit dates. Not all visits will necessarily have had an invoice assigned, so only show (a conditionally formatted) greyed out visit date, with blank 'Invoice total', 'Amount Paid', and 'Amount Due' fields. I want the filter to be able to either include/exclude these non-invoiced visit dates.

[comment]

It's hard to say what the script should be when all you have is a script that doesn't do what you want it to do. I am guessing you want something like:

If [Client::InvoiceFilter = "Show All"]
    Show All Records
Else
    Enter Find Mode[]
    Set Field [Invoice::Amount Due ;  
        Case (
        Client::InvoiceFilter = "Show Paid" ; 0 ;
        Client::InvoiceFilter = "Show Unpaid" ; "< 0" ;
        Client::InvoiceFilter = "Show In Credit" ; "> 0"
        )]    
    Set Field [Invoice::_pk_InvoiceID ; Case ( Client::InvoiceFilter = "Show Not Invoiced" ; "=" ; "*" ) ]
    Perform Find
End If 

Note:

 

Set Field [Invoice::Amount Due = 0.00]

 

There's something wrong here; Set Field needs to have two parts: which field to set, and what value to set it to.

[db_tragic]

Hi comment,

 

Thankyou for responding.

 

Your guess was right on track.  I could see that my initial attempt was looking too cumbersome for what I wanted to achieve - using the case function as you have tidies it up nicely.

 

Having said that - a couple of things;

1. Your script works for the first 4 options in the filter, but not the last (not invoiced). The idea of the last filter option is simply to show those records where no invoice id exists. I don't quite understand the logic in the expression;

Set Field [Invoice::_pk_InvoiceID ; Case ( Client::InvoiceFilter = "Show Not Invoiced" ; "=" ; "*" ) 

……and how this would filter the list down to values without a "Invoice::_pk_InvoiceID" value. I would have thought something like the following (inserted "and IsEmpty(Invoice::_pk_InvoiceID") would have clarified the expression - unfortunately it doesn't.

Set Field [Invoice::_pk_InvoiceID ; Case ( Client::InvoiceFilter = "Show Not Invoiced" and IsEmpty(Invoice::_pk_InvoiceID) ; "=" ; "*" )

What I am not clear on here is how the "=" and "*" operators work in this expression.

 

2. Re Set Field - Yes, I can see where I went wrong there. I figure the expression should have been written as;

 

Set Field [Invoice::Amount Due; "0.00"]

"It's hard to say what the script should be when all you have is a script that doesn't do what you want it to do. I am guessing you want something like:"

[comment]

You cannot use calculations in find criteria. The = operator (when entered with no additional text)  will find all records where the field is empty. If that doesn't work for you, insert a pause in the script just before Perform Find[] and observe the search criteria as entered by the script. When the InvoiceFilter field contains "Show Not Invoiced", you should see nothing in the Amount Due field and = in the _pk_InvoiceID field.

[db_tragic]

Thanks comment,

 

Thanks again for your help with this. I now understand how the expression works with the operators, however, I tried what you suggested, re the pause insertion, and yes, the "=" does appear in the _pk_InvoiceID field, however the next step returns "No records match this find criteria", even though they do exist.

 

I've tried to manually perform the search, manually entering "=" into the _pk_InvoiceID field and get the same message.

 

I've double checked that the relevant _pk_InvoiceID fields are in fact empty, and they are. 

 

Any suggestions on why this might be happening? 

 

TIA

 

You cannot use calculations in find criteria. The = operator (when entered with no additional text)  will find all records where the field is empty. If that doesn't work for you, insert a pause in the script just before Perform Find[] and observe the search criteria as entered by the script. When the InvoiceFilter field contains "Show Not Invoiced", you should see nothing in the Amount Due field and = in the _pk_InvoiceID field.

[comment]

I've double checked that the relevant _pk_InvoiceID fields are in fact empty, and they are.

 

How exactly did you that? Also, what is the type of the field and is it indexed?

[db_tragic]

How exactly did you that? Also, what is the type of the field and is it indexed?

 

Checked it by going to table view, making the _pk_InvoiceID field active (where the cell appeared empty), and confirming there was no content.

 

Also, given that the portal filter (used on another layout) successfully filters out the correct (non-invoiced) records using "IsEmpty" in the expression as per below;

Client::InvoiceFilter = "Not Invoiced" and IsEmpty ( Invoice::_pk_InvoiceID )

…..would seem to suggest the field in question is actually empty, when it's supposed to be.

 

The field attributes are;

Text - Indexed, Auto-enter Serial, Required Value, Unique, Numeric Only, Allow Override

[comment]

I am stumped. If you enter * as the search criteria, does it find the "empty" records, too?

[db_tragic]

I am stumped. If you enter * as the search criteria, does it find the "empty" records, too?

 

Nope. Only those records with a value in the InvoiceID field.

 

I dunno if this helps, but worth a mention in case it's relevant.

 

This particular List view, where I'm wanting to use this filter, is based on the Visit Notes table - so shows a list of all dates that visit notes have been recorded against any of the horses owned by a given client. The 'Date' field is thus based on the Visit Notes table. Below, are the other fields present, and the table they are related to:

 

Invoice::_pk_InvoiceID

Animal::AnimalName

Invoice::Subtotal

Invoice::Paid

Invoice::AmountDue

 

These are the same fields that appear in a portal on another layout, that do filter correctly (as mentioned above).

 

I can see how your suggested  line for this 'Not Invoiced' filter should work, but can't for the life of me, figure out why it's not. This is driving me nuts!

[db_tragic]

I am stumped. If you enter * as the search criteria, does it find the "empty" records, too?

 

After sleeping on this, I think I have an idea on why this isn't working.

 

Given the goal of this problem step in the script is to filter for those visit dates that do not have an invoice associated with them, the expression is trying to use  an 'empty field' value as the search criteria, but applying it to where a record doesn't exist.

 

It would seem to me, that a better filter construct might be to filter for those visit dates that don't have an invoice associated with them. However, I figure that to do this, the filter criteria would be to find where there is a 'Visit Date' value AND the corresponding InvoiceID filed is empty.

 

However, you mentioned earlier that you can't have a calculation in the search criteria - does that mean that you can't use operators/expressions like 'and' or IsEmpty in search criteria?

 

Alternatively, a workaround could be to reconstruct the find criteria for empty InvoiceID records, by designing an expression that simply omits records with any value in the Invoice::Subtotal field, however, I don't see any find operator that specifies where a field contains 'any' value, i.e. the opposite of "=" (empty).

[LaRetta]

"the goal of this problem step in the script is to filter for those visit dates that do not have an invoice associated with them,"

 

... In another table?  You said Invoice::_pk_InvoiceID.  If you are searching for InvoiceID looking to the Invoices table then wouldn't the relationship to it be based upon InvoiceID?  You can't find empty that way.  You can search for * in InvoiceID and OMIT.

[comment]

I've put this aside for reading later, but I believe LaRetta already nailed it. To put it another way, there is a difference between searching a related field for an empty value (in an existing record!) and searching for records that do not have a related record at all.