Filtering client transactions by balance using drop down list

[db_tragic]

I have a layout set in list view, of transactions relating to a given client.

 

I have set up a drop-down list containing the following filter criteria:

Show all

Show unpaid

Show in credit

Show paid

 

I'm having trouble setting up a script (script trigger based on the drop-down list) that filters the currently displayed transactions for that client.

 

At the moment, selecting any of these filter criteria, causes the filter to apply to all transactions for all clients, and are not being applied to only those showing in the transaction list. Selecting 'Show All' after selecting any of the other filter items then shows all transaction for all clients again. Obviously my find criteria are all wrong.

 

The script I have tried is as follows;

Enter Find Mode []
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")]
Perform Find {}
End If
Sort Records [Restore;No Dialogue]

Any help would be appreciated.

 

TIA

[eos]

Try “Constrain Found Set” instead of “Perform Find”.

[comment]

I have a layout set in list view, of transactions relating to a given client.

 

It seems that this is a list view of a layout belonging to the Transactions table? If so, who/where is the "given client"? How did they become the "given client" - do you have their ClientID in a global field or a variable?

 

 

 

Try “Constrain Found Set” instead of “Perform Find”.

 

I believe that will work once.

[db_tragic]

Try “Constrain Found Set” instead of “Perform Find”.

Given the filter here is based on the balance amount, which field do I specify in the constrain? (Still learning about finds) Using the amount due isn't working for me.

I then have the challenge of setting the script to restore the intial find so that when an alternate filter option is subsequently selected, the new find doesn't constrain the existing found set.

It seems that this is a list view of a layout belonging to the Transactions table? If so, who/where is the "given client"? How did they become the "given client" - do you have their ClientID in a global field or a variable?

The relationship structure is below:

Basically, on the Client Details layout, I have a button (script trigger) that takes me to the transaction listing layout;

Go to Related Record [Show only related records; match found set; From table: "Visit Notes"; Using layout: "Client Transaction History"(Visit Notes)]

......which finds all transactions relating to the currently displayed Client.

[comment]

When you call Go to Related Record[], you disconnect yourself from the current context (Client table). In the new context, the Visit Notes table, there is no "current client". All you have is a found set (that could have been created in a number of ways).

 

What you need to do is store the value of the current client's _pk_Client in a global field or variable before calling GTRR. Then you will be able to use it as an additional search criterion when performing the find.

 

 

Alternatively, you could stay in the Client table and view the the related records in a portal, which you could further filter as desired using the drop-down.