The fact that you are sorting on a calculation field will not affect the sorting time. The fact that you are sorting on an unstored calculation field will add significantly to the amount of time it takes to sort the records.
Can you describe your set up in more detail. I'd need to have a clear picture of both your relationships and your layout design before I can suggest any alternative approaches.
Also, what version of FileMaker do you have? If you have version 12, we might be able to do something with ExecuteSQL().
Contacts::_PKContactID----<CasesReferred::_fkReferralID (this is a table occurrence of Cases)
When a new case is generated for a patient, the user selects the referrer from the contacts table. This enters the Contacts Serial ID into the _fkReferralID field in Cases.
When a user needs to "cancel" a case for various reasons (I do not allow deletion of records in the database), they click a button followed by a prompt and script, which sets Cases::Canceled = "Canceled"
In some situations, I filter my portals with the following expression: ...Visible when: Cases::Canceled ≠ "Canceled" so my sum fields are accurate and show the count of actual cases that werent canceled. But for
I use a sum field to display the amount of cases referred in a given time period, etc. No problems here.
The problem I am having is when trying to create a sort in list view that recognizes the count of _fkCasesReferred or even _fkPatientID for a Contact while subtracting the count of Cases::Canceled = "Canceled"
I use FMP12 Advanced
This sounds like something you can set up as a summary report. Such a report would completely avoid any of the issues slowing down your current layout.
You can base such a layout on an occurrence of the Cases table. You'd perform a find on this layout to omit all canceled records. The same find can specify other criteria such as a date range.
On this layout, a summary field can count the number of records. If you put it inside a sub summary layout part "when sorted by" _fkReferralID, you'll get a sub total showing the number of referrals for each referring contact that were not canceled. If you don't want to list the individual cases in this report, you can remove the body layout part.
Two key details:
The records must be sorted by the "when sorted by" field or the sub summary part will not be visible.
The records must be viewed in list view.
Here's a thread with a lot of scripted find examples that you may find helpful: Scripted Find Examples
And here's a tutorial thread on Summary Reports: Creating Filemaker Pro summary reports--Tutorial
Phil thanks for this. Problem is, I use the data on Dasboards as well as the list view layout. I guess I can integrate this into my existing list view layout, but it looks like I am stuck as far as the portals on the dashboards go. I use a portal to display the "Top Referrers" at a glance
I suggested a summary report based on very limited information in your original post. It's almost always my first suggestion because it is more generally applicable to many different needs. But it is not the only option.
Please describe what you need to see and on what type of layout. A "summary recap" of this same data could be displayed in a large calculation field equipped with a scroll bar by using ExecuteSQL().
Basically, I want to display which cntacts referred the most cases. I will use this data in portals sorted in descending order. I also have a list view of contacts, where the user can click on different headings to sort the contacts by: Zipcode, Name, # of cases referred, etc.
The portals appear on Dashboard layouts and the user is able to scroll up and down in the portal to view the data and click on portal rows to be taken to the contacts record
And the unstored # of cases referred field will represent a potential "peformance hit" as you are sorting on an unstored calculation field.
You might experiment with script triggers that update a simple number field in contacts each time a referring contact is selected and each time a Case record is cancelled. This then gives you a stored/indexed field for faster searches and sorts, but now your user interface must be very carefully designed to make sure that errors do not creep into this number field.
Thanks Phil. What you suggest is normally what I do with date fields (I use a set field scipt to set the most current date of a related record in the parent table). Sorts become instant when sorted by the field in the parent table. I need to get clever and figure out a way to implement what you uggested if it is the best way. For now, I am just going to sort by the sum field of _fkCasesReferred but disaply the calc field in the portal/layout. The calc field will be accurate (cases- canceled) but the actual sort will be slightly off at times.
If you have these relationships
From a layout based on cases....
Set Field [ ReferringContacts::ReferralCount ; Count ( cases 2::UncanceledFlag ) ]
coudl be used to update a number field, ReferralCount each time a Referral contact Id is selected or the case record is canceled.
UnCanceledFlag can be a calculation field that returns a value only if the case record is not canceled.
I set up the ReferringContacts::ReferralCount and cases 2::UncanceledFlag fields. I think I incorrectly set up the UncanceledFlag field calc (If ( CancelCase ≠ "Canceled" ; 1 ; 0 ) I think it should be If ( CancelCase ≠ "Canceled" ; 1 ; "" ) instead.
I am trying to understand the concept here before I add the Set Field function to my scripts. If a user cancels a case, shouldn't UnCanceledFlag return -1 instead of zero or no value at all?
Count counts the number of related records where the field is not empty. So if you use Count, the flag field should be empty for canceled case records.
You could use Sum instead of count, but then the value for canceled cases would need to be 0 or empty.
-1 would not work in either case.