DISTINCT values not applying to LIST page

rafee · Post by **rafee** » Sun Apr 21, 2019 6:39 pm

.
I have VIEW that displays the details of the foreign key.
e.g.
tables: Employee & Payroll_Transactions
-> Where Employee can have 1 or more transactions in 1 payroll schedule.
ex: Employee transactions : base pay, allowance, bonus and deductions like absences, advances etc...)

This happens when an employee has 2 or more allowances or bonuses added to his payroll schedule.
e.g. (view display)

ID | TransactionType | Amount | PayrollPeriod

Employee1 | Allowance | 200.00 | April 1-15

Employee1 | Allowance | 100.00 | April 1-15

Employee1 | Bonus | 50.00 | April 1-15

Employee1 | Bonus | 150.00 | April 1-15

PAYROLL table:
In ADD page I set the ff:

Set the Edit tag to CHECKBOX in which I can select multiple TransactionType values.
DISTINCT value to display on TransactionType field (Allowance & Bonus) w/c works very well.

Problem:
In List page, the values selected were duplicated just like on VIEW display of TransactionType (see the presentation).
e.g.
Allowance, Allowance, Bonus, Bonus

NOTE:

Actual database stores it as comma separated values ( with NO duplication). Meaning its perfectly correct.

Where to fix this issue? If possible, I want to fix this in PHP NOT JS or some other way

sangnandar · Post by **sangnandar** » Mon Apr 22, 2019 2:32 am

I would assumed by "VIEW" you mean database-view.

rafee wrote:

ID | TransactionType | Amount | PayrollPeriod

Employee1 | Allowance | 200.00 | April 1-15

Employee1 | Allowance | 100.00 | April 1-15

Employee1 | Bonus | 50.00 | April 1-15

Employee1 | Bonus | 150.00 | April 1-15

If above is your database-view then you just need to add GROUP BY clause in your CREATE VIEW syntax. Be sure to also sum(Amount).
For example,
CREATE VIEW AS
SELECT ..., SUM(Amount), ... from yourTable GROUP BY TransactionType ;

rafee · Post by **rafee** » Fri Apr 26, 2019 10:42 am

sangnandar wrote:

If above is your database-view then you just need to add GROUP BY clause in your
CREATE VIEW syntax. Be sure to also sum(Amount).
For example,
CREATE VIEW AS
SELECT ..., SUM(Amount), ... from yourTable GROUP BY TransactionType ;

Thank u and yes, it is a database view but I think its not the best option for now.
This issue is only to CHECKBOX - for multiple selection (stored as comma separated value)

If I will grouped 'Allowance' and 'Bonus', the ID-> Employee1 will remain duplicated.

Pls NOTE:
I have no problem with ADD page. The "Distinct" or "Group" will work. The duplication of data display ISSUE is ONLY TO LIST page.

LIST page display will show like : Employee1,Employee1
BUT Actual DB stores it as : Employee1

I think the reason is because the result returns 2 rows. I think this is NOT on SQL query problem because I still need those 'Allowance' and 'Bonus' value but I only need 1 'Employee1' to display. The issue is mainly on Phpmaker CHECKBOX comma separated display.

Question:
On what server/client event can I fix this?

sangnandar · Post by **sangnandar** » Sun Apr 28, 2019 5:53 am

rafee wrote:
Problem:
In List page, the values selected were duplicated just like on VIEW display of TransactionType

If List Page is the same as database-view, i.e. duplicated records, then you have problem with your view-query, not with php.

rafee · Post by **rafee** » Sun May 05, 2019 3:23 pm

In ADD page, I can make it to display DISTINCT values on "ID" column but on LIST page it shows the exact query results: ("Employee1" shows up twice like the view query results above)

sangnandar · Post by **sangnandar** » Mon May 06, 2019 5:07 am

rafee wrote:
In ADD page, I can make it to display DISTINCT values on "ID" column but on LIST ...

Irrelevant.
Those two things absolutely has nothing to do with each other.

rafee · Post by **rafee** » Sun May 19, 2019 2:44 pm

Yes it has nothing to do with ADD events but Can I solve this?
Again, sorry for repeating but the saved values in DB are not duplicated. e.g.: 1,2,4. but when displayed on LIST: 1,1,2,4

mobhar · Post by **mobhar** » Sun May 19, 2019 6:35 pm

Did you use Master/Detail relationship in order to display Employee & Payroll_Transactions information?
Please post your tables/views schema by using SQL syntax and some records sample for more discussion.
Please post also your Table/Fields setup in your PHPMaker project for more discussion.