Using more than one portal filter type (date range AND/OR invoice status)

[db_tragic]

I currently have a portal showing all invoices attributed to a given client.

 

At the moment, I use the following portal filter calculation to filter based on the 'Amount due' balance (i.e. invoice status) for each invoice (using a drop-down list Client::InvoiceFilter, on the layout);

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)

What I want is for the user to be able to also filter based on a date range. This date range filter would be mutually exclusive of the above filter.

 

Scenario would be;

1. Layout opens with portal showing all invoices.
2. User has the choice to set a date range (using global startdate and enddate fields shown on the layout) - default is 'all'
3. User has additional option to filter remaining items in portal using the above 'invoice status' filter.

 

Should I be trying to incorporate the date range into the above calculation? e.g add a line to the above calculation like;

and 

Set Field [VisitNotes::Date; DateRangeGlobal::gStartDate & "…" & DateRangeGlobal::gEndDate] 

or is there another way of having an additional portal filter using another means?

 

Alternatively, should I be using, say, a List layout (not a portal), to implement these two filter types (date range, invoice status)?

 

TIA

[eos]

Should I be trying to incorporate the date range into the above calculation? e.g add a line to the above calculation like;

and
Set Field [VisitNotes::Date; DateRangeGlobal::gStartDate & "…" & DateRangeGlobal::gEndDate] 

Set Field [] is a script step that you cannot use in a calculation. The code line would be used to set a find criterion in a scripted search, but then a found set has no impact on the records being displayed in a portal. (But I can see where you may be a tad confused as to what is what, and when to use it …) 

 

The logic though is (almost) correct; except that AND would add the date filter to the last balance condition, as per operator precedence (where AND binds more closely than OR – that's why your original expression doesn't need parentheses), while you want the date filter to be mutually exclusive to the entire balance block.

 

See if this sample file helps you. I'm not sure why you had added a 'not IsEmpty ( …PK )’ to the calculation, since all related record have (or should have!) a primary key, so it makes no difference to the result. This expression is usually employed when you want to prevent Conditional Formatting being applied to fields in a spare row of a portal whose relationship allows creation of related records; but you cannot hide the row itself.

MediumComplexPortalFilter_eos.fmp12.zip

[Fitch]

An alternative to the portal filter would be to gather the invoice IDs into a list using a script; store them in a global field; and make the portal's relationship from the global to the invoice ID. FileMaker 13 has a new function that makes it easier to do the ID gathering.

 

A couple of reasons I like this approach are 1. I generally like to put my logic into scripts, it is often easier and more flexible to do so, and 2. there may be a performance gain.

 

You also asked about using a list, and to that I'd say yes, if a list works for your design, use that instead of a portal. For one thing, it makes sorting a lot easier.

[db_tragic]

 

Should I be trying to incorporate the date range into the above calculation? e.g add a line to the above calculation like;

and
Set Field [VisitNotes::Date; DateRangeGlobal::gStartDate & "…" & DateRangeGlobal::gEndDate] 

Set Field [] is a script step that you cannot use in a calculation. The code line would be used to set a find criterion in a scripted search, but then a found set has no impact on the records being displayed in a portal. (But I can see where you may be a tad confused as to what is what, and when to use it …) 

 

The logic though is (almost) correct; except that AND would add the date filter to the last balance condition, as per operator precedence (where AND binds more closely than OR – that's why your original expression doesn't need parentheses), while you want the date filter to be mutually exclusive to the entire balance block.

 

See if this sample file helps you. I'm not sure why you had added a 'not IsEmpty ( …PK )’ to the calculation, since all related record have (or should have!) a primary key, so it makes no difference to the result. This expression is usually employed when you want to prevent Conditional Formatting being applied to fields in a spare row of a portal whose relationship allows creation of related records; but you cannot hide the row itself.

 

 

Thanks for the input re Set Field - yes it sounded logical to me, but wasn't sure if it was possible. Also had concerns about it's location in the calculation too.

 

The 'not IsEmpty….' expression is because the invoices are related to visit notes, recorded against a client's owned horse/s. There may be times when visit notes are recorded, but not necessarily an invoice, and the 'date' field in the portal is taken from the visit notes date (the date field in the invoice table is a lookup field referencing that in the visit notes, and using the date field from the invoice table caused problems with sorting in the correct date order in the portal - no idea why!)

 

I'll check out the sample file, thanks.

 

 

An alternative to the portal filter would be to gather the invoice IDs into a list using a script; store them in a global field; and make the portal's relationship from the global to the invoice ID. FileMaker 13 has a new function that makes it easier to do the ID gathering.

 

A couple of reasons I like this approach are 1. I generally like to put my logic into scripts, it is often easier and more flexible to do so, and 2. there may be a performance gain.

 

You also asked about using a list, and to that I'd say yes, if a list works for your design, use that instead of a portal. For one thing, it makes sorting a lot easier.

 

I'll check out that new function in Fm13 - I've deliberately not ventured there yet, in case I find myself wanting to make a number of changes to the solution, because of new functionality, when I'm still trying to tidy the front end as it is.

 

I may stick with the list idea for now (till I've looked into the referenced sample file 'eos' pointed to). 

 

Thanks for the help folks - always appreciated.