Questions about summary fields and the average function.

[tanbog]

Hello,

I apologize in advance as I am still very new to FM Pro.

I have been tasked with making a DB in FM Pro 11 That manages a record of Clients and Individual Contacts with those clients.

As part of the DB I have to generate reports which collect certain statistics for our funding bodies. While working on this I got a little confused and was hoping someone here could set me straight

As part of my brief I am having to import a a bunch of old records from an excel spreadsheet.

One key stat I have to calculate is the time interval between a contact being requested and contact actually being made with a client.

I have the two dates in the DB and a calculation field generating each interval. I then have a summary field storing an average of the interval.

The user must be able select a month and year and then get - amongst other things - a calculation of the average for that month only.

My questions:

1. How do I restrict the average to only show that for the desired month? This kind of calculation would be trivial in SQL but I can't for the life of me figure out how to do it in FM Pro ><

2. A number of the incoming date fields have non-integer values in them (people were writing comments into the spreadsheet fields). My interval calculation is currently placing an "error" comment when this occurs until I get instructions what to do with it.

I expected this to totally break the summary field however it is happily spitting out a seemingly valid average. Does it ignore strings in the field or is it performing some other calculation?

[tanbog]

Never min question 1 I was over-complicating what I was doing. All I had to do was make a summary field and the average adjusted on its own for my found set.

My second question stands however, what does the the average summary field do when confronted with Strings?

[Tori Mitchell]

It tries to read the whole thing as a date which dosen't work. Personally, I'd use text manipulation to get the date and comments into separate fields or, if the comments are pointless, just replacing data in the field with GetAsDate(<<fieldName>>) should clear out those issues nicely.