Counting Instances of a Recurrence in a Field

[n7mafia]

I have an input field, where the user can choose from a dropdown list between three non-numerica values (A,B,C), I'd like to create three fields which display the instances of the three values based on the data on the records, possibly sorted descending, so for example if the values in the field are: A,A,B,C,C,C the database should return:

 

C: 3

A: 2

B: 1

 

And if the user creates two new record with values A,A, it should become:

 

A: 4

C: 3

B: 1

 

How can I accomplish that?

[No_access]

If I understand you correctly, I would use a calulcation field using patterncount to get the count of a's.b's, etc... Then create 3 summary fields to get the totals

[n7mafia]

How do I use patterncount efficiently? I tried to look for examples but FMP gives a too short one to understand and on the net there are plenty of instances of it but all too complicated as opposed to what I want to achieve. Also, I need to evaluate the occurrences of A,B,C, in a number of records rather than in a single textbox, does it work anyways?

[Vaughan]

You're much better doing this using related records instead of entry into a single field.

 

Try this custom function:

 

http://www.briandunning.com/cf/1027

[n7mafia]

I would, but I am clueless on how to adapt it to my needs, I mean, it is not exactly intuitive for a novice like me

[Lee Smith]

Why not Attach a copy of your file. Remove any confidential information first. This will allow us to see your structure and either correct it or give you help so that you can do it yourself. It will allow us to describe things in your file naming of layouts, field names, scripts, etc.

 

Save a copy, zip it and follow these steps.

 

How to add an attachment to a reply.

[n7mafia]

You are right.

I made a sample DB with a few records in it. There are 3 layouts, first one's the one where the user enters the data record by record (I did include a few sample records), second one's a sorted list and the third one's the one I can't achieve; I created a bunch of fields where I did manually input the data the way I would like them to appear with calculations, to visually give you an idea, more specifically:

- The first part should be a "top ten" where the DB checks the records and display the first 10 results ordered by descending High Score.

- The second part should display which are the most used cars, most used circuits and the most common weather (all text fields), along with the occurences.

- The third part should display a 'self updating' "top four" with the four available weathers sorted by occurences.

Test.zip

[n7mafia]

Anyone?

[n7mafia]

Why not Attach a copy of your file. Remove any confidential information first. This will allow us to see your structure and either correct it or give you help so that you can do it yourself. It will allow us to describe things in your file naming of layouts, field names, scripts, etc.

 

Save a copy, zip it and follow these steps.

 

How to add an attachment to a reply.

 

Apparently making a sample DB to provide the "structure" of the database only had the effect to scare the heck out of everybody which priorly tried to help?

 

Does anyone, by any chance, know how I could tackle my problems?

[No_access]

I still think you should do it the way I said above  please see attached file...

 

sample.zip

[mr_vodka]

See here. You can use Fast Summaries or use SQL to get your results.

 

http://fmforums.com/forum/topic/85844-only-show-5-highest-values/

[n7mafia]

I still think you should do it the way I said above  please see attached file...

This solution helps me solving the "counting instances" problem on one hand, on the other hand it doesn't allow me to have them sorted like:

A: 5
C: 3
B: 1

but rather like

A: 5
B: 1
C: 3

Plus it doesn't let me determine which is the "most common occurrence". Any way these two things could be implemented?

[No_access]

Add a calulation field  called most common  and use this script

this will give you the most common used.

Case(
suma >sumb and sumc;"A";
sumb > suma and sumc;"B";
sumc > sumb and suma;"C"
)

 

 

we can do the same thing to get the order is you need.

 

This way works well since you have only a few choices.. if their were more we might want a little bit more advanced approach.

 

case(

suma >sumb and suma>sumc and sumb > sumc;"A="&suma&¶&"B="&sumb&¶&"C="sumc;

etc...

etc...

etc...

)

 

 

if you need help with it I will be glad to code it for you if needed.

[n7mafia]

Add a calulation field  called most common  and use this script

this will give you the most common used.

Case(

suma > sumb and sumc;"A";

sumb > suma and sumc;"B";

sumc > sumb and suma;"C"

)

This part seems pretty straight forward and I can easily replicate it, but as far as I understood, it would just return the most common value rather than an a "top five" ordered by occurrences (which is what I aim to)

 

 

we can do the same thing to get the order is you need.

 

This way works well since you have only a few choices.. if their were more we might want a little bit more advanced approach.

 

case(

suma >sumb and suma>sumc and sumb > sumc;"A="&suma&¶&"B="&sumb&¶&"C="sumc;

etc...

etc...

etc...

)

 

 

if you need help with it I will be glad to code it for you if needed.

This part is definitely beyond understanding to me, if you could code it for me I'd be grateful so I could try it out and 'add to my repertoire'.

As for the number of values, one "list" is made of just 4 values, another one has more than 10 entries, which is the more complicated approach you had in mind? Otherwise I could just replicate the system you mentioned and manually add lines as required.

Sorry if my post seems confused but apparently I have some problems translating what I want into words, thank you for your patience :)

[No_access]

What I read above from you was

Plus it doesn't let me determine which is the "most common occurrence"

which is what that lets you do.

Please let me know exactly what you want and I will be glad to help.

thanks

[n7mafia]

Let's say I got one field which may contain up to four values (A,B,C,D), say I have 10 records like this:

A: 5
B: 1
C: 3
D: 1

I would like to have a dinamycally changing "top four" which would return the following result:

A: 5
C: 3
B: 1
D: 1

I mentioned dinamycally changing since if I create ten more records where the data entered in the field is "B" I'd like to look like this:

B: 11
A: 5
C: 3
D: 1

[mr_vodka]

[mr_vodka]

Did you try it?

LeftValues (

ExecuteSQL ( "
              SELECT
                  Circuit,
                  Count ( Circuit ) AS kount
              FROM Test
              GROUP BY Circuit
              ORDER BY kount DESC
             ";
                ""; ""; ""
           )

            ; 3
           )

[n7mafia]

I'd love to but I am totally at a loss when it comes to SQL, I have no idea where should I write this function, if I should create a field, what kind of field should that be etc..

[mr_vodka]

If you are uncomfortable with it then you can use the fast summaries method.

 

If using SQL, in your case here you can either set a variable, use a calc field, set a field etc with it. What this SQL statement does is return the Circuit field and count of the Circuit field when grouping it by that field ( think subsummary report grouping or GetSummary ( ) function ). Then it is sorted in descending order based on that count result of that field ( I assigned an alias of 'kount' to it ).

[n7mafia]

I tried creating a calc field to put the code you gave me in the previous post but I get a "?" in the field, what did I miss?

[mr_vodka]

Do you have it as result text?

[mr_vodka]

I have modified your file and attached it. I have left some notes for you in the field definition comment section as well as a screen shot of the inspector to show you where to modify the tab positions. You can change the column separator to be what you want. I gave an example of using the tab character as well as a space dash space " - ". The default is a comma.

 

 

top3_count.zip

[n7mafia]

Okay, thanks to your help I managed to make it work, the reason why it wasn't working was because the FROM table was supposed to be one word only and in my case it was made of two words, but I changed it to one word and it now works.

Thanks a lot.

Edited April 20, 2013 by Mafia2020