COUNT UNIQUE VALUES

czillman

Is there an elegant way to COUNT UNIQUE VALUES of an attribute?

I think this is the SQL
SELECT COUNT(DISTINCT column_name) FROM table_name

I cant figure out the bus rule
BO.CNTUNIQUEPARTNUMBERS = SELECT COUNT(DISTINCT BO.PARTSLIST.PARTNUMBER) FROM BO.PARTSLIST

aware_support

Unfortunately, right now this can only be accomplished as a Java plug-in.

tford

czillman,

If you are really desperate to do this & not into Java plug-in, you could keep a BO with unique values of a certain attribute using a method like posted at https://www.awareim.com/forum/d/1578> . You have to really be careful about accounting for all add/change/delete activity though. It seems complicated but it's not a ton of work. I would not want to use this method for very many attributes though.

After accumulating the unique attributes, you could then easy COUNT them.

Tom

czillman

Thanks for the replies.

I've never written a java plug-in. What would I use to write the plug-in? Is there some sort of compiler that would be good for little simple thinkes like this?

cheers, charles

aware_support2

Another idea is to maintain a unique parts list in addition to the full parts list. This could be done with the following rules on BO:

Add unique part:
If Part WAS ADDED TO BO.PartsList AND NOT(EXISTS Part WHERE (Part.PartNumber = AddedPart.PartNumber AND Part IN BO.UniquePartsList))
Then INSERT AddedPart IN BO.UniquePartsList

Remove unique part:
If Part WAS REMOVED FROM BO.PartsList AND NOT(EXISTS Part WHERE (Part.PartNumber = RemovedPart.PartNumber AND Part IN BO.PartsList))
Then
FIND Part WHERE Part.PartNumber = RemovedPart.PartNumber AND Part IN BO.UniquePartsList
REMOVE Part FROM BO.UniquePartsList

Then you can count the unique parts with the following rule:

BO.CntUniquePartNumbers = COUNT Part WHERE Part IN BO.UniquePartsList

This is similar to the approach mentioned above, except that it is limited in scope to a given object instance, does not require additional objects and takes care of removed list members.

RLJB

Has anyone managed to come up with a nice solution for this in the last 10 years?

We need to count unique instances, based on an attribute, any ideas? A custom function perhaps?

tford

Hi Rod,

What's wrong with the solution support offered?

RLJB

Hi Tom

This is for a table/BO that is pretty large (it's a learning management system records store), so I want to avoid duplicating a massive table with more data.

Jaymer

FWIW...
I had no idea what a "learning management system records store" was.
A simple google search showed some sponsored ads.

Saw a neat demo - COULD have been aware in the 1st 10 seconds (if you were REALLY good), then became apparent it was something else. VERY nice! Since we're all "in the biz", I'm sure someone could learn something (UX-wise) from this:

https://www.absorblms.com/features/

I looked at all 10 products on this page:
http://www.capterra.com/sem-compare/learning-management-system-software?headline=Top%2010%20Top%2010%20Learning%20Management%20Software&gclid=CjwKEAiAoOvEBRDD25uyu9Lg9ycSJAD0cnBy-4VQZCvbTUqikyfh9CIXpb-scv-oaz1JW-mHUfIF5xoCJaDw_wcB
and the Absorb product (linked above) was the only one that lets you see a ton of real screens so I didn't learn much from the other systems.

FYI

RLJB

Funny how the same need keeps arising... 3 years laters... does anyone have a nice function or solution for this yet, that isn't:

a. a stored procedure
b. creating another table of unique entries

tks

ddumas

USE EXEC_SP, or the EXEC_SPF() function to return the value. I call stored procedures throughout my application, especially for queries. Also to set flags or calculated columns that are best left to SQL code
Know them, love them ,use them 🙂

yahya

What happens when you want to just display the unique values in a list? See my screenshot as an example.

Screenshot 2020-06-29 at 12.04.08.png

tford

@Ddumas

USE EXEC_SP, or the EXEC_SPF() function to return the value. I call stored procedures throughout my application, especially for queries. Also to set flags or calculated columns that are best left to SQL code
Know them, love them ,use them

Could you provide a couple examples of your use cases of USE EXEC_SP and EXEC_SPF() functions to return a value .. for queries, set flags, calculated columns?

jannes

See this example :

The process :
EXEC_SP DoCount WITH @regel='SELECT count(DISTINCT plaats) FROM persoon'
FIND All Msg
DISPLAY MESSAGE Msg.Regel

The stored procedure in the DB :
CREATE DEFINER=root@localhost PROCEDURE devtest.docount(IN regel varchar(2000))
BEGIN
DELETE FROM msg;
EXECUTE IMMEDIATE concat ('INSERT INTO msg (id, regel) values (1, (', regel, '))');
END

tford

Thank you!