I've had a good look round the web, and the archives of this group, but can't find anything to help me with something I'd like to do. I wondered if anyone here had struggled with this before and worked out a solution.

I have a large table. There are five particular fields, which are Text fields, containing (text!) values like "1", "2.1", "3" etc. It's important to remember that these are text values, despite their deceptively numeric appearance.

In any given row, these five fields may be either empty, or contain one or more values such as the ones given above, or similar.

What I'd like to do is to count the number of instances of any given value in a given row. To say, for example, that there are five instances of "1", or three instances of "2.2' and one instance of "2.1" and one empty.

I come from a MySQL background, but I have to do this in FM, so bear with me as I struggle a bit to get control of the FM way of doing things, which is really quite different!

I know I'll need to use calculations to effect this, but I just can't work it out. I'll probably need to use another five calculated fields, as I shall need to display up to the full five 'instance counts' on the layout, depending on the data present in the fields.

Count() doesn't do what I want. ValueCount() may do, but I just can't work it out.

If anyone can help me, I'll be eternally grateful.

Dave
-- 
Faculty of Divinity, University of Cambridge, West Road, Cambridge, CB3 9BS
http://www.cus.cam.ac.uk/~djg39/ [Dave's University home page]
http://www.food-in-due-season.info/ [Dave's first book, published May 2005]
http://www.living-bread.info/ [Dave's next book, published February 2006]

Are those simili-numbers random, ie can take any value, or else can you make 
a finite list of all their possible values ?
Because if the last is true, you may make a calculation using a set of 
'PaternCount' functions, one for each value.
If they can be 'random' then I hope someone else will provide you with a 
solution that I can't see for the moment.
Remi-Noel


"David Goode" <djg39@cam.ac.uk> a �crit dans le message de news: 
20064185828.759653@ignatius...
> I've had a good look round the web, and the archives of this group, but 
> can't find anything to help me with something I'd like to do. I wondered 
> if anyone here had struggled with this before and worked out a solution.
>
> I have a large table. There are five particular fields, which are Text 
> fields, containing (text!) values like "1", "2.1", "3" etc. It's important 
> to remember that these are text values, despite their deceptively numeric 
> appearance.
>
> In any given row, these five fields may be either empty, or contain one or 
> more values such as the ones given above, or similar.
>
> What I'd like to do is to count the number of instances of any given value 
> in a given row. To say, for example, that there are five instances of "1", 
> or three instances of "2.2' and one instance of "2.1" and one empty.
>
> I come from a MySQL background, but I have to do this in FM, so bear with 
> me as I struggle a bit to get control of the FM way of doing things, which 
> is really quite different!
>
> I know I'll need to use calculations to effect this, but I just can't work 
> it out. I'll probably need to use another five calculated fields, as I 
> shall need to display up to the full five 'instance counts' on the layout, 
> depending on the data present in the fields.
>
> Count() doesn't do what I want. ValueCount() may do, but I just can't work 
> it out.
>
> If anyone can help me, I'll be eternally grateful.
>
> Dave
> -- 
> Faculty of Divinity, University of Cambridge, West Road, Cambridge, CB3 
> 9BS
> http://www.cus.cam.ac.uk/~djg39/ [Dave's University home page]
> http://www.food-in-due-season.info/ [Dave's first book, published May 
> 2005]
> http://www.living-bread.info/ [Dave's next book, published February 2006]
>
> 

On Sat, 1 Apr 2006 09:40:18 +0100 Remi-Noel Menegaux wrote:

> Are those simili-numbers random, ie can take any value, or else can you
> make a finite list of all their possible values ?
> Because if the last is true, you may make a calculation using a set of
> 'PaternCount' functions, one for each value.

Thanks, Remi-Noel. 

No, they're not random: they can only be selected from a value-list, which=
 is itself populated from seven possible values in another table.

I'll have a look at PatternCount() and see if I can work it out. I actually=
 meant PatternCount() when I said ValueCount() earlier! I have already=
 looked at this function, but couldn't work out how to do it. I was=
 struggling to make the function look at several fields in the table. Should=
 I craft a new calculated field to check each of the five fields for each of=
 the seven possible values, something like this:

PatternCount(paper_1_agreed_mark; "1")
PatternCount(paper_1_agreed_mark; "2.1")
..
..
..
PatternCount(paper_5_agreed_mark; "Fail")

This would do the trick, but I'd have to a huge number of new fields! And,=
 how would I then summarise them to tell me that:

paper_1_agreed_mark was "2.1"
paper_2_agreed_mark was "2.2"
..
..
..
paper_5_agreed_mark was "1"

in that row, and that there were, for example, two instances of "2.1" and=
 one instance of "2.2" and one instance of "1" and one instance of "3" in=
 that row?

If it's possible as you say, I clearly wasn't thinking about it hard enough=
 ;-)

Dave

-- 
Faculty of Divinity, University of Cambridge, West Road, Cambridge, CB3 9BS
http://www.cus.cam.ac.uk/~djg39/ [Dave's University home page]
http://www.food-in-due-season.info/ [Dave's first book, published May 2005]
http://www.living-bread.info/ [Dave's next book, published February 2006]

Ok, that's good news.
So to answer the following paragraph :
> What I'd like to do is to count the number of instances of any given value
> in a given row. To say, for example, that there are five instances of "1",
> or three instances of "2.2' and one instance of "2.1" and one empty.
Here, I will suppose that by 'row' you mean 'record', as you should work in 
the file/table which has the records that contain the fields you want to 
check, and not in a portal in another file/table.
So, your first request needs that you create 7 new fields one per value. The 
'Count_2.1' field (numeric) for example should get the number of times the 
(text) value '2.1' has been found in the 5 fields that you want to look 
into, each of those fields being able to have several values separated by 
some character like space or paragraph return or anything.
I suggest also that you create one new field (calc) = Field1 & " / " & 
Field2  & " / " & Field3  & " / " & Field4  & " / " & Field5 that could be 
named 'AllFields'.
Then your 'Count_2.1' field should be defined as a calculated value equals 
to : "PatternCount(AllFields; "2.1"), and the other 6 'Count_x' made 
similarly.
That answers the first request.
Now you want also to summarize those Counts by just making a layout with a 
'Trailing Grand Summary' part in which you'll show the other 7 new fields 
named 'TotalOfCount_2.1' for example, being of  'Summary' type and 'Total' 
of the 'Count-2.1'.
Done.
If you need so, I can make a small example file in FMP6.
After all, it is simple isn't it ?
Remi-Noel




"David Goode" <djg39@cam.ac.uk> a �crit dans le message de news: 
200641102634.668204@ignatius...
On Sat, 1 Apr 2006 09:40:18 +0100 Remi-Noel Menegaux wrote:

> Are those simili-numbers random, ie can take any value, or else can you
> make a finite list of all their possible values ?
> Because if the last is true, you may make a calculation using a set of
> 'PaternCount' functions, one for each value.

Thanks, Remi-Noel.

No, they're not random: they can only be selected from a value-list, which 
is itself populated from seven possible values in another table.

I'll have a look at PatternCount() and see if I can work it out. I actually 
meant PatternCount() when I said ValueCount() earlier! I have already looked 
at this function, but couldn't work out how to do it. I was struggling to 
make the function look at several fields in the table. Should I craft a new 
calculated field to check each of the five fields for each of the seven 
possible values, something like this:

PatternCount(paper_1_agreed_mark; "1")
PatternCount(paper_1_agreed_mark; "2.1")
..
..
..
PatternCount(paper_5_agreed_mark; "Fail")

This would do the trick, but I'd have to a huge number of new fields! And, 
how would I then summarise them to tell me that:

paper_1_agreed_mark was "2.1"
paper_2_agreed_mark was "2.2"
..
..
..
paper_5_agreed_mark was "1"

in that row, and that there were, for example, two instances of "2.1" and 
one instance of "2.2" and one instance of "1" and one instance of "3" in 
that row?

If it's possible as you say, I clearly wasn't thinking about it hard enough 
;-)

Dave

-- 
Faculty of Divinity, University of Cambridge, West Road, Cambridge, CB3 9BS
http://www.cus.cam.ac.uk/~djg39/ [Dave's University home page]
http://www.food-in-due-season.info/ [Dave's first book, published May 2005]
http://www.living-bread.info/ [Dave's next book, published February 2006]

On Sat, 1 Apr 2006 11:54:07 +0100 Remi-Noel Menegaux wrote:

> After all, it is simple isn't it ?

Thanks for the tips. In fact, I'd thought of something very similar, and it=
 works very nicely.

You've been very helpful, and I thank you very much.

Dave
-- 
Faculty of Divinity, University of Cambridge, West Road, Cambridge, CB3 9BS
http://www.cus.cam.ac.uk/~djg39/ [Dave's University home page]
http://www.food-in-due-season.info/ [Dave's first book, published May 2005]
http://www.living-bread.info/ [Dave's next book, published February 2006]

You should check also that those 'values' being text are sufficiently 
distinct from each other to not counting them twice or more.
To illustrate what I said, if you had the values, 2, 2.1, 2.2, then the 
"PatternCount" would count in the first 'Count_2' all the '2's but also all 
the '2.1's and the '2.2's. So be sure you have 2.0, 2.1, 2.2 (to stick with 
my example).
Remi-Noel

"David Goode" <djg39@cam.ac.uk> a �crit dans le message de news: 
200641141748.666956@ignatius...
On Sat, 1 Apr 2006 11:54:07 +0100 Remi-Noel Menegaux wrote:

> After all, it is simple isn't it ?

Thanks for the tips. In fact, I'd thought of something very similar, and it 
works very nicely.

You've been very helpful, and I thank you very much.

Dave
-- 
Faculty of Divinity, University of Cambridge, West Road, Cambridge, CB3 9BS
http://www.cus.cam.ac.uk/~djg39/ [Dave's University home page]
http://www.food-in-due-season.info/ [Dave's first book, published May 2005]
http://www.living-bread.info/ [Dave's next book, published February 2006]

On Sat, 1 Apr 2006 15:42:56 +0100 David Goode wrote:

> PatternCount (concatenation_field;"1")

Whoops. I meant:

 PatternCount (concatenation_field;"/1/")

of course!

Dave
-- 
Faculty of Divinity, University of Cambridge, West Road, Cambridge, CB3 9BS
http://www.cus.cam.ac.uk/~djg39/ [Dave's University home page]
http://www.food-in-due-season.info/ [Dave's first book, published May 2005]
http://www.living-bread.info/ [Dave's next book, published February 2006]

On Sat, 1 Apr 2006 15:28:17 +0100 Remi-Noel Menegaux wrote:

> You should check also that those 'values' being text are sufficiently
> distinct from each other to not counting them twice or more. To=
 illustrate
> what I said, if you had the values, 2, 2.1, 2.2, then the "PatternCount"
> would count in the first 'Count_2' all the '2's but also all the '2.1's=
 and
> the '2.2's. So be sure you have 2.0, 2.1, 2.2 (to stick with my example).

I discovered that. In fact, delimiting them in the concatenation field so it=
 looks like this:

 /1//2.1//2.2//...

and then using 

PatternCount (concatenation_field;"1")

works fine, finding only /1/ and not /2.1/, which, as you kindly point out,=
 doesn't work if the delimiting is absent or insufficient.

I have an additional question: is it possible to output as plain, readable=
 text, the database structure and especially each field's type and any=
 calculations and validations. I have a large number of fields across quite=
 a few tables in this database, and many of them are highly complex=
 calculations: it would help me greatly if I could dump the whole, complete,=
 structure to a text file for when I come to document the application.

And, thanks again for your help.

Dave

-- 
Faculty of Divinity, University of Cambridge, West Road, Cambridge, CB3 9BS
http://www.cus.cam.ac.uk/~djg39/ [Dave's University home page]
http://www.food-in-due-season.info/ [Dave's first book, published May 2005]
http://www.living-bread.info/ [Dave's next book, published February 2006]

David Goode wrote:
> I have an additional question: is it possible to output as plain, readable text, the database
> structure and especially each field's type and any calculations and validations. I have a large
> number of fields across quite a few tables in this database, and many of them are highly
> complex calculations: it would help me greatly if I could dump the whole, complete, structure to
>  a text file for when I come to document the application.

Have a look at Analyzer
http://www.wmotion.com/s.nl/sc.2/category.-102/it.I/id.6/.f

FP wrote:

> Have a look at Analyzer

Thanks. That looks useful. I've emailed them for an 'education' price.

Dave

-- 
Faculty of Divinity, University of Cambridge, West Road, Cambridge, CB3 9BS
http://www.cus.cam.ac.uk/~djg39/ [Dave's University home page]
http://www.food-in-due-season.info/ [Dave's first book, published May 2005]
http://www.living-bread.info/ [Dave's next book, published February 2006]