Count number of instances with a static choice

RLJB

I have a Plain Text attribute with 4 static choices and "Other Values Allowed" ticked - so users may add more of their own.

I want to bring back a list (in a form or a report) listing all the choices and the number (COUNT) of instances for each choice.

Any ideas?

technopak

Hi Rod,

Here's what I'd do -

Create a new object to hold unique instances of your text field called UserChoice. UserChoice needs two attributes TextFieldValue and InstanceCount.
Create a rule on the existing TextFieldObject which creates an instance of UserChoice for each TextFieldObject which has a text field value which doesn't already exist in UserChoice.

Rule - CreateUserChoice

IF TextFieldObject.TextFieldValue IS DEFINED AND NOT (EXISTS UserChoice WHERE (UserChoice.TextFieldValue=TextFieldObject.TextFieldValue)) THEN CREATE UserChoice WITH UserChoice.TextFieldValue=TextFieldObject.TextFieldValue

Create a rule on UserChoice which updates the InstanceCount for each unique value of TextFieldValue by counting the number of instances of each in the text field object.

Rule - CountInstances

IF UserChoice IS NEW THEN UserChoice.InstanceCount=COUNT TextFieldObject WHERE (TextFieldObject.TextFieldValue=UserChoice.TextFieldValue)

Then create a process called UpdateUniqueChoiceCount with these two rules

FIND ALL UserChoice
DELETE UserChoice

FIND ALL TextFieldObject
Update TextFieldObject

This will create instances of the new UserChoice object which contains your unique user choices plus a count of the number of instances of each in the original text field object.

You may want to modify things a little to stop the CreateUserChoice rule firing all the time. I'd put a UpdateUserChoices YES/NO attribute into SystemSettings and only fire the CreateUserChoice rule when this is set to YES. The UpdateUniqueChoiceCount process above can set it to YES at the start and back to NO when complete.

Hope it helps,

Peter

RLJB

thanks Peter - great idea, will implement and report back asap