Speed up Finding when using a unsorted calculation

[Devin]

I'm trying to see if there is a way that I can speed up Finds when searching a calc filed that can not be index because of a related filed.

Is their anything that can be done?

Were searching based on the Job status Calc. With over 200,000 records it takes 30-40 Sec.

Quick break down.

Table:Jobs
Job ID
Job status Calc.  This can not be indexed as the calc is based on the Parts: Parts Status

Table:Parts
Parts ID
Parts Job ID
Parts Status

Relationship  Jobs: Job ID <--> Parts: Parts Job ID

 

 

[comment]

Is their anything that can be done?

​Tell us what the calculation does, then we'll know. Or, even better, tell us which records do you want to find.

[Devin]

The Jobs::Job status Calc does this.

Let (
shortList = FilterValues ( "In Prepress¶Out on Proof¶Approved¶Complete¶Hold" ; List ( Parts::Part Status ) )
;
Case (
shortList = "In Prepress¶Out on Proof¶Approved¶Complete" ; "Partially Complete" ;
shortList = "In Prepress¶Out on Proof¶Approved¶" ; "Partially Approved" ;
shortList = "In Prepress¶Out on Proof¶Hold¶" ; "Partially Held" ;
shortList = "In Prepress¶Out on Proof¶Complete¶" ; "Partially Done" ;
shortList = "In Prepress¶Out on Proof¶" ; "Partially on Proof" ;
shortList = "In Prepress¶Approved¶" ; "Partially Approved" ;
shortList = "In Prepress¶Approved¶Complete¶" ; "Partially Complete" ;
shortList = "In Prepress¶Complete¶" ; "Partially Complete" ;
shortList = "In Prepress¶Complete¶Hold¶" ; "Partially Held" ;
shortList = "In Prepress¶Hold¶" ; "Partially Held" ;
shortList = "Out on Proof¶Approved¶" ; "Partially Approved" ;
shortList = "Out on Proof¶Complete¶" ; "Partially Complete" ;
shortList = "Out on Proof¶Hold¶" ; "Partially Held" ;
shortList = "Approved¶Complete¶" ; "Partially Complete" ;
shortList = "Approved¶Hold¶" ; "Partially Held" ;
shortList = "Complete¶Hold¶" ; "Partially Held" ;
shortList = "In Prepress¶" ; "In Prepress" ;
shortList = "Out on Proof¶" ; "Out on Proof" ;
shortList = "Approved¶" ; "Approved" ;
shortList = "Complete¶" ; "Complete" ;
shortList = "Hold¶" ; "Held"
)
)

I've got a  script that takes the users to list mode and shows the jobs that do not equal Complete. 

[comment]

I've got a  script that takes the users to list mode and shows the jobs that do not equal Complete. 

​You could do that by searching the Parts::Part Status field directly, using multiple requests;

Go to Layout [ Jobs ] 
Enter Find Mode [  ] 
Set Field [  Parts::Part Status; "Prepress" ] 
New Record/Request 
Set Field [  Parts::Part Status; "Proof" ] 
New Record/Request 
Set Field [  Parts::Part Status; "Approved" ] 
New Record/Request 
Set Field [  Parts::Part Status; "Hold" ] 
Perform Find [  ] 

 

BTW, I believe the calculation itself could be streamlined, if you take into account that the Case() function returns the first result whose test returns true.

Edited May 31, 2015 by comment

[Devin]

​You could do that by searching the Parts::Part Status field directly, using multiple requests;

Go to Layout [ Jobs ] 
Enter Find Mode [  ] 
Set Field [  Parts::Part Status; "Prepress" ] 
New Record/Request 
Set Field [  Parts::Part Status; "Proof" ] 
New Record/Request 
Set Field [  Parts::Part Status; "Approved" ] 
New Record/Request 
Set Field [  Parts::Part Status; "Hold" ] 
Perform Find [  ]

​This worked Perfectly.. Oh so much faster 

 

As with streamlining the Calc.. I'm not sure what your talking about. I'm always up for learning.

 

[Lee Smith]

comment was telling you that it is important that you structure your case statement so that it evaluates your statement in the manner you want. The Help on Case Statements is pretty clear, read more about it here.

FileMaker Help Case Statement

Edited May 31, 2015 by Lee Smith

[eos]

​As with streamlining the Calc.. I'm not sure what your talking about. I'm always up for learning.​

In your calc, a single-value result is the result itself; all other results are "Partially " & someStatus; examine the result for the presence of a status in their order of priority:

Let  (
  shortlist = … ; 
  Case (
  ValueCount ( shortlist ) = 1 ; shortlist ; 
  not IsEmpty ( FilterValues ( shortList ; "Complete" ) ) ; "Partially complete" ;
  // first in priority order; takes care of all cases with "Complete"
  not IsEmpty ( FilterValues ( shortList ; nextInPriority ) ) ; "Partially nextInPriority" ;
  // takes care of all remaining cases with nextInPriority
  // etc.
)

btw, if you need to search by a mix of indexed and non-indexed fields, perform a Find on the indexed field(s), then Constrain[] on the non-indexed one(s)

[Wim Decorte]

The other obvious thing is to NOT rely on calculations for any of this.  Set the status based on a scripted workflow so that you don't have to search on unstored calculations in the first place.  The status of a thing is very much a "point in time" thing.  No reason at all to keep calculating it over and over.  Set it once.

[Devin]

Wow.. Lots of info thanks Guys.

Eos... I'm trying to digest and understand what your doing.

Wim... Your saying that I should do a trigger script that runs when a change to the Part status changes. That sounds a better way of handling the Job Status.

[Wim Decorte]

Don't let your first reflex be to use triggers.  They can work well but you may want a little more fine-grained control and just run it off a button-click, not any other event.

[Devin]

Don't let your first reflex be to use triggers.  They can work well but you may want a little more fine-grained control and just run it off a button-click, not any other event.

​I would rather not use Triggers script, But then I get into the issue of people needing to do two steps.

The only time the trigger would run is when they change the part status.

[eos]

Eos... I'm trying to digest and understand what your doing.

Take the individual tests in your Case() and group them by result; that should help you to spot a pattern.

Edited May 31, 2015 by eos

[Devin]

Take the individual tests in your Case() and group them by result; that should help you to spot a pattern.

​eos... This seems to work great, Thanks for your guidance.

Let (
shortList = FilterValues ( "In Prepress¶Out on Proof¶Approved¶Complete¶Hold" ; List ( Parts::Part Status ) )
;

Case (
ValueCount ( shortList ) = 1 ; shortlist ;
not IsEmpty ( FilterValues ( shortList ; "Hold" ) ) ; "Partially Held";
not IsEmpty ( FilterValues ( shortList ; "Complete" ) ) ; "Partially Complete" ;
not IsEmpty ( FilterValues ( shortList ; "Approved" ) ) ; "Partially Approved" ;
not IsEmpty ( FilterValues ( shortList ; "Out on Proof" ) ) ; "Partially on Proof"

)
)