• Registered: 2016-01-17
  • Posts: 139

Topic: TableGrid Duplicate Records

Hi Every one
I have encountered a problem in my project and I attached it.
there are many personnel in a company  and each one has 2 or 3 children.
when I search the name of a personnel, the tablegrid in the searchtable shows records based on the children. For example instead of showing only one personnel it shows 2 or 3 records same name.
I attached the picture and in that picture it is clear what I need to do

Post's attachments

Attachment icon New 1.zip 406.04 kb, 110 downloads since 2023-02-23 

2 Reply by pavlenko.vladimir.v (edited by pavlenko.vladimir.v 2023-02-23 09:10:43)

  • From: Нефтеюганск
  • Registered: 2022-06-03
  • Posts: 455

Re: TableGrid Duplicate Records

The program works correctly, shows the same person twice, because he has two children whose names are also displayed in the table.
   
Программа работает правильно, показывает два раза одного и того же человека, потому, что у него двое детей имена которых так же выводятся в таблице.

  • Registered: 2016-01-17
  • Posts: 139

Re: TableGrid Duplicate Records

thanks pavlenko.vladimir.v
I know that it works but what to I want to know is that, is there any way that the children are shown in just one line?
imagine that there are more than 50 personnel and each one has 2 , 3 or 4 children, then there will be many records

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 896

Re: TableGrid Duplicate Records

Hi,

Post's attachments

Attachment icon NEW2.zip 12.24 kb, 105 downloads since 2023-02-23 

  • From: Нефтеюганск
  • Registered: 2022-06-03
  • Posts: 455

Re: TableGrid Duplicate Records

identity wrote:

thanks pavlenko.vladimir.v
I know that it works but what to I want to know is that, is there any way that the children are shown in just one line?
imagine that there are more than 50 personnel and each one has 2 , 3 or 4 children, then there will be many records

(SELECT group_concat(childname, ", ") FROM Children WHERE Children.id_student=student.id)
Post's attachments

Attachment icon NEW.rar 10.17 kb, 129 downloads since 2023-02-23 

6 Reply by identity (edited by identity 2023-02-23 11:00:40)

  • Registered: 2016-01-17
  • Posts: 139

Re: TableGrid Duplicate Records

thanks a lot sparrow and  pavlenko.vladimir.v
both of the solutions are perfect and exactly what I needed.

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,682

Re: TableGrid Duplicate Records

Hi All,
Vladimir - using 'group_concat' with a calculated field - that's a great option.
Derek.

  • Registered: 2016-01-17
  • Posts: 139

Re: TableGrid Duplicate Records

Hi sparrow
In your sample (in the SQL Query of the Search Form) how can I add a Auto-number at the beginning of the tablegrid 1?

9 Reply by sparrow (edited by sparrow 2023-03-06 12:35:51)

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 896

Re: TableGrid Duplicate Records

SELECT
    "$autoinc",
    student.firstname, 
    student.lastname, 
    Wife.wifefirstname, 
    Wife.wifelastname, 
    GROUP_CONCAT(Children.childname,', '),
    student.id
FROM student 
 INNER JOIN Wife ON student.id = Wife.id_student
 INNER JOIN Children ON student.id = Children.id_student

WHERE CASE WHEN '{Edit3}' = '' THEN 1 ELSE Wife.wifefirstname LIKE '%{Edit3}%' END
AND CASE WHEN '{Edit1}' = '' THEN 1 ELSE Wife.wifelastname LIKE '%{Edit1}%' END
AND CASE WHEN '{Edit2}' = '' THEN 1 ELSE Children.childname LIKE '%{Edit2}%' END
GROUP BY 1

P.S. and add header for new first columns for example Num in the propeties of Button4.

Num, Personel first name, Personel last name, Wife first name, Wife lst name, Child name, delete_col

and please read manuals or forum