counting records

[H]

Hi , the following is very simple just can't to the bottom of it

i have a field which has a value of a color.

i want to get the found count of the record but it should only count every color once meaning 

record 1 - red

record 2 - green

record 3 - blue

record 4 - green

 

my result should be 3

i would appreciate if someone could help me out on this.

Edited June 26, 2018 by H

[Steve Martino]

If you created a summary field (sListColor), 'list of' with your color field (YourTable::color field) you would get:

red

green

blue

green 

for your summary field based on your found set.

Then the expression, once again based on your found set of records:

valuecount(UniqueValues (YourTable::sListColor ))

would return 3.

You could also do this with a one row, one field self join portal, or if you wanted to skip all that, you could use a SQL statement (which I couldn't figure out, but probably easy for one of the developers).

Edited June 26, 2018 by Steve Martino
additional info

[bcooney]

for SQL use DISTINCT.

https://www.w3schools.com/sql/sql_distinct.asp

[Steve Martino]

@bcooney  Barbara, can you (how would you) use the statement on a found set?

ValueCount(ExecuteSQL ( "SELECT DISTINCT yourField 
FROM YourTable "; "" ; "" ))

Steve

Edited June 26, 2018 by Steve Martino

[bcooney]

Op didn't mention a a found set. If so, I wouldn't use sql.