Calculation field with Count() by date-range

[Crashin]

Howdy all. I'm trying to setup a Calculation field that will get the number of records, with a specific field marked, in a related table by a date range. We'll call them Table A and Table B.

Table A contains a field called "cust_id", a "start_date" field, an "end_date" field, and a field called "no_incidents" to hold the calculation. Table B has a field called "cust_id", a field called "create_date", and a field called "incident". The two tables are related by the "cust_id" fields.

Using Count() in Table A.no_incidents I can easily calculate the number of Table B.incident records related on "cust_id". My calculation is as follows:

Count(cust_id::incident)

Can anyone help me extend my calculation so that it will only count those records in Table B marked as an incident with a date that falls between the start_date and end_date for the record in Table A?

I hope this makes sense. Let me know if I can clarify any further.

[ernst]

Hi Crashin,

What are the sort of dateranges that you think about? 1 year, 10 years?

And do the date ranges have to be adjustible to one day, or is a 'resolution' of 1 month enough?

regards,

Ernst.

[Crashin]

Hi Ernst,

Thanks for the reply! The date range will be 1 year. Basically, I'm checking to see how many records have been recorded withing a year's worth of support. The date_start is the day they purchased support, and our contracts run for one year. The date_end is the date of expiration.

[ernst]

Hi Crashin,

I can see two solutions to this:

The first....

-Make a calculated field in table A that calculates all dates between date_start and date_end and concatenates the customer ID to those dates. See attached quick-example...

-Make a calculated field in table B that is simply customer ID & " " & incident date

-Make a relation from the field created above between table A to Table B and use the count function.

Second method... Use a contract number as the key for the relation:

- Table A holds the contract n

datetest.fp5.zip

[Crashin]

Hi Ernst,

I'll give your suggestions a try and report my results. Thanks for the help!

Best,

Crashin

[Ugo DI LUCA]

Ernst,

I had never seen this kind of range calculation. Congratulations.

I would likely do it in a different way though, with a scripted method as this seems to be some kind of temporary report.

But finally, check BobWeaver's DateRange3.fp5 here on the Forum. As Ernst Suggested, create a concanation Contract and Date in the related file and modify Bob's calc to add your contract Id to the date range using a Substitute function

[Crashin]

Hi All,

I don't know what I'm doing wrong, but I can't even get past the creation of the "repetitions" field. I've created the field, but when I try to run the script to populate it the script gets stuck in a loop. When I esc out of it I find that it only populates up to 99. That's about as far as I've been able to get.

Also, where can I find the DateRange3.fp5 file? I've tried searching the forums but haven't had any luck.

[ernst]

Hey guys,

>>I don't know what I'm doing wrong, but I can't even get past the creation of the "repetitions" field. >>I've created the field, but when I try to run the script to populate it the script gets stuck in a loop. >>When I esc out of it I find that it only populates up to 99. That's about as far as I've been able to get.

Is this in the example that I posted, or in an adaption in you database?

If it's the latter:

-did you define 366 repetitions for the repeating global?

-does the layout show all 366 repetitions?

I made the script because I was to lazy to type in 0......365 by hand, needs to be run only once.

Anyway, please let me know...

Regards,

Ernst.

[Crashin]

Hey Ernst,

It is in my implementation of your example in my database. I did define 366 reps. for the global, however I don't believe the layout shows all 366 reps. (is that something I define somewhere?)

If what you're asking is if, upon creating the field and running the script, are there 366 entries, the answer is no. There are only 99, and that's where it gets stuck.

Thanks for continuing to help me out with this!

[ernst]

>>It is in my implementation of your example in my database. I did define 366 reps. for the global, >>however I don't believe the layout shows all 366 reps. (is that something I define somewhere?)

Yes, when you select the field in layout-mode and choose Format->Field Format from the menu.

Good luck & regards,

Ernst.

[Crashin]

Alright, I'm getting closer. What you suggested worked in that I can now see my entire lists. But, something peculiar is happening. My concatenated field is displaying the cust_id<space>date correctly for all dates but the two-digit months. Those display correctly until the 10th day of the month, when it begins to only display the customer number.

For example, all dates for a paticular customer are displayed properly through 10/9/2003 as:

1 10/9/2003

Then, I only get the customer number until the next month begins (i.e. 1 11/1/2003). I'm getting the same results for the same dates for November and December. Any thoughts?

[color:"red"]Nevermind...much to my embarassment, I simply needed to widen the field.

[Crashin]

Hi Ernst,

Thanks again for all of your help. I'm to a point now where I've got everything setup and am ready to perform the Count(). I'm doing so with my files related on the two new fields. However...I am getting no results when I perform the count. I'm assuming it's because all of the cust_id/date combos in File A are stored in a single field, whereas there is only one cust_id/date combo in the field in File B.

I must be missing something in the implementation...do you have any advice?

[ernst]

Hi Crashin,

Oops, just tried it and your right. I thought the repeating field would work on either side of the relation, but it does not...

So to punish myself I made an extra calculated text field field that 'adds' all repetitions and puts them in one long string. Looks like:

GetRepetition(cUserIDAndStartToEndDate, 1) & "

[Crashin]

Hey Ernst,

Yes, I see what you mean. And, I'm only trying this right now on some test tables. I can't imagine what I'd have to go through on our regular databases that contain many, many more fields.

The end purpose of this exercise is a link I'm making with our web server so that end users can enter support calls via our site. I've got a good link up and going, so I might just throw everything onto the web side and deal with the counting there. I'm finding SQL to be more forgiving in some situations than FileMaker.

At any rate, this exercise taught me a great deal, too. Thanks so much for you help!