Filter field values based on dates and AM?PM

[Jalz]

Hi Guys,

 

Need some advice as I'm not sure how to appoach this, whether to use a recursive function or whether the bog standard calculation engine with Let and position might solve what I am after. I have a field, with data similar to that below, but tons of it:

 

10/09/2012am entered by name on 13/09/2012 07:48:19
11/09/2012pm entered by name on 13/09/2012 07:51:22
12/09/2012am entered by name on 13/09/2012 07:54:55
12/09/2012pm entered by name on 13/09/2012 07:55:07
14/09/2012am entered by name on 14/09/2012 08:33:51
13/09/2012am entered by name on 14/09/2012 08:34:11
13/09/2012pm entered by name on 15/09/2012 11:40:51
17/09/2012am entered by name on 17/09/2012 08:42:58
19/09/2012am entered by name on 19/09/2012 08:38:23
20/09/2012am entered by name on 20/09/2012 08:31:57
21/09/2012am entered by name on 21/09/2012 08:42:38
18/09/2012pm entered by name on 21/09/2012 14:51:47
19/09/2012pm entered by name on 21/09/2012 14:52:39
21/09/2012pm entered by name on 21/09/2012 14:56:19
24/09/2012am entered by name on 24/09/2012 08:33:12
26/09/2012am entered by name on 26/09/2012 08:38:20
26/09/2012pm entered by name on 26/09/2012 16:23:04
28/09/2012am entered by name on 28/09/2012 08:49:59
25/09/2012pm entered by name on 29/09/2012 11:57:19
27/09/2012am entered by name on 29/09/2012 12:00:01

 

 

What I need to do is allow the user to type in a from and to date and whether its AM/PM.

 

Using that data typed in by the user, filter through these fields and only produce the total number of entries from the criteria. So for instance if the user typed in 12/09/2012 - 21/09/2012 AM, I want the result to be 6. I'm currently using FileMaker Pro 11

 

Many thanks as always

[Lee Smith]

Have you looked at any of the custom functions at Brian Dunning's site?

BD Site

 

I seem to remember a couple of them were for pulling dates out of timestamps. Your example data uses 24 hours so I'm not sure why am pm is a problem?

[LaRetta]

You say you have a lot of them.  Then two things come to mind:  1) you do not want to use filtered portals and 2) you should take advantage of filtered relationships first and foremost to keep your speed up.  Here is how I would handle it (attached).

 

Note:  Adding the calculation cAMPM may seem redundant because FM already knows (thus we know) whether a timestamp is AM or PM.  However, we cannot use a range relationship (non-equijoin) because it would pick up the PM timestamps when you request 12/09/2012 - 21/09/2012 AM using a range and we cannot relate date to timestamp (data-types must match).

 

In this example, an empty start date is filled to catch ALL younger records and same with an empty end date.  For 'all' just check both AM and PM. 
 

NumInstances.zip