Displaying a one-to-many relationship in a grid, with search

Applications Grid / Reports
#1

Hi there-

What’s the best way to do this… We want a grid that shows a list of people, and all the jobs they can possibly do.
But, because one person can do hundreds of jobs (ex: general contractor), we want to display it well. Ex: show the first 2 jobs they can do, and then if they click something, it shows more. NOTE we also want the list searchable by job.

How can this be done? Table details below…

We have a table of people:
pid (autoincrement primary key)
Name
Email

Then a table of jobs they can do:
job_id (autoincrement primary key)
Title

Then, a “link” table, people_jobs:
pid
job_id

NOTE: Some people can do 1000 jobs! Example: Job can be woodwork, painting, framing. And someone who’s a general contractor can support thousands of these jobs.

#2

The best way is difficult to say. My first reaction should be a nested grid. You can limit records by using the appropiate sql statement (i.e. limit x in mysql). If you need to change the amount of records to be shown dynamically you need to apply global variables to the sql statement. These variables are then set by pressing your button.

select … where … limit [glob_limit_q1]

or something similar

#3

We used Double Select and the N-N relationship builder. Looks great.

#4

try creating a view combining all tables
create a grid and a nested grid
first grid limit the jobs to 2 using select
nested grid goes to the same view to show all

raj