Execute SQL count tweak needed

[Rich S]

Okay, I'm close; it involves two fields, CompanyName__lxt and NoteImportant__lxt.

 

What I want to do is count the number of records in a table (JOB) where the calc looks at the current company name in the  CompanyName__lxt field and counts how many records with that same company name also have a value in NoteImportant__lxt.

 

Example: Blivet, Inc., may have a total of eight records in JOB but only four of them have a value in NoteImportant__lxt, so the calc should return a count of 4.

 

Currently, I have this calc but it counts all the records in JOB that have a value in NoteImportant__lxt; how do I tweak it so it accomplishes the above? I've added AND arguments but haven't quite nailed it.

 

ExecuteSQL (
"SELECT
COUNT ( NoteImportant__lxt )
FROM JOB
WHERE NoteImportant__lxt = ?
" ; "" ; "" ; NoteImportant__lxt
)

 

 

Now for my density to truly shine: Is this the best way to get the count I need or is there a way of summarizing in FileMaker that'll give me the same result without having to restore found sets and such after a Find? TIA for your help!

[Lee Smith]

Automatic message

This topic has been moved from "Calculation Engine (Define Fields)" to "FQL or FileMaker Query Language".

[eos]

What I want to do is count the number of records in a table (JOB) where the calc looks at the current company name in the  CompanyName__lxt field and counts how many records with that same company name also have a value in NoteImportant__lxt.

 

Try

ExecuteSQL ( "
  SELECT COUNT ( NoteImportant__lxt )
  FROM JOB
  WHERE CompanyName__lxt = ? 
  " ; "" ; "" ; CompanyName__lxt
)

 

Is this the best way to get the count I need

 

To get this result from the context of Jobs without finding/sorting, create a summary field Count of NoteImportant__lxt and a Jobs self-join by company and display the related summary field from the self-join.

 

As an aside: shouldn't you have (and use) a company ID foreign key in Jobs, rather than the name?

[comment]

Is this the best way to get the count I need or is there a way of summarizing in FileMaker that'll give me the same result without having to restore found sets and such after a Find?

 

Why don't you add a calculation field to the Companies table =

Count ( Jobs::NoteImportant__lxt )

and, if necessary, show this field on the layout of Jobs?

 

Caveat:

I did not understand the part about "having to restore found sets and such after a Find?". This suggestion will ignore any found set/s you might have and count all related Jobs where the NoteImportant__lxt is not empty.

[Wim Decorte]

Personally I would not use a calc field or a summary field.  In a big solution with many concurrent users, having the # of jobs recalculate every time you go to that layout is a waste of resources.

Update the count in a scripted fashion when a job is added.  That makes it the count a static field.  Fast to display, fast to search on, fast to report on.

[Rich S]

First, my apologies for posting in the incorrect forum--that was careless of me.

 

Thanks for all the great input! I'll play with all your suggestions.

 

To answer a few questions:

 

Yes, there's a self-incrementing primary key field in the table: _kpln_JobID. (I _always_ create primary key, record created, and record last modified fields in every table.)

 

...and to Comment: I meant that I didn't want the solution to go through the motions of either constraining or finding records that met my criteria then restoring the records back to their original found set; it seems to me to be an inelegant, klunky way of doing things.