Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Need help with count calculations


Carlos F. Martinez
 Share

Recommended Posts

I need some help in figuring out something similar to the countif function in Excel for a Filemaker database report layout.  I have tried using If, Case and even ExecuteSQL but none seem to work for me.  I am attaching a screen shot of the report layout with notes on what I am trying to accomplish.  The counts and totals would be placed in the footer (the ones in the attachment are images from an Excel file with a similar format)5a9f15a6bcef5_ScreenShot2018-03-06at4_22_19PM.thumb.png.3841ff2ee4f25eade5c3e60feb364608.png.  I would greatly appreciate any help on this.

Link to comment
Share on other sites

What about using an IF statement along the lines of if Product Unit = "Auto 6" and Status = "On Time" is true, count the number of records?  Also,  I forgot to mention that the summaries are in the footer of the report and that the Case function in the notation refers to the Open Days column.

Link to comment
Share on other sites

You can't create a statement assessing an aggregate batch of records using "count if" logic; FileMaker doesn't have that.

You can, however, create calc fields that reconcile to 1 under appropriate circumstances:

Case (Product Unit = "Auto 6" and Status = "On Time", 1)

 

... and then sum THOSE fields up.

 

Sum (OneIfAuto6OnTime)

or

Summary field defined as Sum of OneIfAuto6OnTime

 

for the "On Time" segment, assuming it compares a date somewhere in the system to the current date, be sure to make it an unstored calc field.  The function Get(CurrentDate) doesn't get reevaluated within a stored calc field every time midnight goes by.  An unstored calc field, however, only comes up with an answer on a "calculate as needed" basis and hence will fetch the correct value for "today".

 

 

 

 

Link to comment
Share on other sites

AHunter3,

Thanks for your help.  It worked.  It's going to mean adding a few more fields but that will not be a problem.

  • Like 1
Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use