Need help generating a count list based upon record status

[needtodestroy]

Hi all,

I'm currently trying to build a project tracking database. I'm running FM 10 Pro and have most of the database laid out.

What I'm having issues with is figuring out how to display a count list summary with numbers next to each case status, i.e.

Cases

Pending: #

Open: #

Closed: #

Referred: #

I have all of these statuses in a value list which must be chosen for each record, but I'm unsure of how to show a count of how many of each status we have in the entire database on an overview page.

[NovaChan]

Since you posted this in the custom function section, I'm going to show how to create a text string result using custom functions. There are other (probably better) ways to show results that dynamically update on your layout, but I already typed up this response, so that will have to wait for another post!

I like to approach complex functions by breaking them down into essential aspects. This makes it easier and gives you more modular code in the end. Sometimes you can find the custom functions for many of these aspects on sites like Brian Dunning's.

Here is a basic custom function that performs your essential task:

custom function , FounList( field; start )

//list.countValueOccurrences( valueList; textToSearch )



Let([

	vValueCurrent = GetValue( valueList; 1 );

	vValuesRemaining = RightValues( valueList; ValueCount( valueList ) - 1 );

	vCount = PatternCount( textToSearch; vValueCurrent);

	vResult = vValueCurrent & ": " & vCount

	];

	vResult

	& Case( not IsEmpty( Trim( vValuesRemaining ) );

		¶ & list.countValueOccurrences( vValuesRemaining; textToSearch )

	)

)

All that it requires is for you to feed it the value list items and the foundset values. There are several methods for collecting values from your foundset. I'm sure a search will reveal some if someone doesn't chime in with a resource. Here is one way to accomplish this using Eilert Sundt's 

list.countValueOccurrences( 

	ValueListItems( "YourValueListName" );

	FoundList( "YourFileName"; 1 )

)

We can improve this function by using tail recursion like this:

//list.countValueOccurrences( valueList; textToSearch )



Let([

	vValueCurrent = GetValue( valueList; 1 );

	vValuesRemaining = RightValues( valueList; ValueCount( valueList ) - 1 );

	vCount = PatternCount( textToSearch; vValueCurrent);

	vResult = vValueCurrent & ": " & vCount;

	

	//--Store results in global variable

	$$fn_countValues_result = 

		If( Length($$fn_countValues_result); $$fn_countValues_result & ¶ )

		& vResult

	];

	Case( 

		//-- Return Result if Out of Values --

		IsEmpty( Trim( vValuesRemaining ) );

			$$fn_countValues_result

			& Let( $$fn_countValues_result = ""; "" );

		

		//-- Default: Continue --

		list.countValueOccurrences( vValuesRemaining; textToSearch )

	)

)

Some Notes:

The slowest part of this calculation will probably be compiling the list of all record values you're searching in. Like I said, this post doesn't assume to address the most efficient way of collecting that list.

[comment]

I'd suggest a report based on the Cases table, with a sub-summary part by Status (and no body part).