How to count distinct records in a table?

Nav_il

Hi All,
I have a table with "Table Name,Employee No.,Line No." as keys. You can enter the same employee no. more than once in this table. Now i want to count the employee numbers in this table onces. for example, If i have 1222, 1222, 1222, 1333, 1333, 1444 employees numbers recorded in the table, i want to count 1222 as one record, 1333 as one and 1444 as one. So i should have a total of 3 records. Also, i want to do this counting withing a specified date range. Say i want all the records in the table withing say 010117...010317 date range which i get using Filter := "Employee".GETFILTERS; expression in the onPreReport. Any idea on how to do this please? Thanks.

ishyampandey

Nav_il wrote: »

No, My Employee No. is a Code datatype.

ya that code will work just take code type variable. by mistake i wrote integer type.
and regarding filter i said apply date filter on predata item

ishyampandey

Take one EmpVar
Now OnAfterrecord write this condition
IF EmpVar <> Table."Employee NO" THEN
BEGIN
EmpCount +=1;
END;
EmpVar := Table."Employee NO";
Do not clear any var.
And apply filter on predataitem

Nav_il

Thanks ishyampandey . However, you cannot do this "IF EmpVar <> Table."Employee NO"". It means you are comparing an integer (EmpVar) with a code (Employee number). Also why apply filter on Predataitem? I have already appied it on onPreReport.

RockWithNAV

You need to build a logic, there's no any keyword as distinct like SQL which exist here.

Nav_il

Hi RockwithNav, thanks for the comment. Yes, I am aware that there is no keyword like DISTINCT, and I have been thinking of logics which are not working. For example, I tried to compare previous Employee number with the current one, if they are the same, I skip counting and if they are not the same, I count. But I don't know how to get previous employee number which is a code and compare it with the current employee number. Do you know how to do that?

ishyampandey

Nav_il wrote: »

Thanks ishyampandey . However, you cannot do this "IF EmpVar <> Table."Employee NO"". It means you are comparing an integer (EmpVar) with a code (Employee number). Also why apply filter on Predataitem? I have already appied it on onPreReport.

Is your Employee no is integer type?

Nav_il

No, My Employee No. is a Code datatype.

ishyampandey

Nav_il wrote: »

No, My Employee No. is a Code datatype.

ya that code will work just take code type variable. by mistake i wrote integer type.
and regarding filter i said apply date filter on predata item

Nav_il

Thanks ishyampandey. This works.

guidorobben

Just build a Query. Fast and simple. Oeps.. Just say this is for the classic Client.

hydhart

you can use temp table to store each record, and insert field you wish to count distinct to primary key and count the temp table.