Filtering data using one checkbox

[#1]

I have a big project with a lot of table. I have done an example to simply what I really want to obtain. Below is attached.

Suppose I have two tables, "Job_Data" and "Personal_Data" which are related through a filed called "ID"

Both tables have their own data. Both tables have a bit column (0 or 1 possible values). For "Job_Data" table is "Consultant" which indicates if the person is a consultant, and for "Personal_Data" table is "license_car" to indicate if that person can drive a car.

So, according this model, I am trying to filter the data from both tables using one checkbox.

This checkbox will filter data by taken into account below posible cases:

Consultant	license_car
0	0
0	1
1	0
1	1

So I have created one checkbox "Only consultants and/or license car".

So according to the sample attached:

• If I check this checkbox:
  • For table "Job_Data" I want to show the registers with "ID" 300 and 900 because "Consultant" field is marked as 1
  • For table "Personal_Data" I want to only show registers with "ID" 120, 300 and 1000 because their "license_car" field is marked as 1. However I do not want to show "ID" 200 and 900 because in this case their "license_car" field is 0.
• If I uncheck this checkbox:
  • I want to show all data for both tables independently of the values for "Consultant" and "license_car" fields.

So how can I do this?  Preferably, I would like to use an expression through the checkbox if possible and not script.

[vinieme12]

You will need to add another LOAD From RESIDENT statement after the Join, but why?

[Job_Data]:

LOAD *,ID as ID_JD INLINE [

ID, Name, Category, Consultant

1000, 'David', 'Auxiliar', 0

900, 'John', 'Analyst', 1

200, 'Anne', 'Manager', 0

120, 'Peter', 'Director', 0

300, 'Paul',  'Auxiliar', 1

]

;

[Personal_Data]:

LOAD *,ID as ID_PD INLINE [

ID, birthday, license_car

1000,01/10/1970, 1

900, 12/01/1980, 0

200, 23/05/1960, 0

120, 04/05/1982, 1

300, 11/10/1990, 1

]

;

COMMON:

LOAD * INLINE [

ID,hobby

1000,xyx

900,abc

200,efg

120,pla

300,gdf

];

left join (COMMON)

LINK_TABLE:

Load ID,ID_JD,Consultant as Consultant_License RESIDENT [Job_Data];

Concatenate

Load ID,ID_PD,license_car as Consultant_License RESIDENT [Personal_Data];

Drop Field ID FROM [Job_Data];

Drop Field ID FROM [Personal_Data];

NOCONCATENATE

RESULT_DATA:

LOAD * RESIDENT COMMON;

DROP TABLE COMMON;

[#3]

I am curious about what you said regarding to table box.... Why not use table box to verify data? As I understood, the Straight Table or Pivot Table are for calculating the value using expressions such as sum, count, aggregation, etc. However in a Table Box you can not use any aggregation function. Table box is used to represent raw data as is.

Are you saying that the information shown by table box is not the correct data that original table contains? If so why? I do not understand, can you provide an example?

[Neil_B]

kumarkp412 Thanks for your solution, that  worked perfectly for me. Had been looking for awhile to be able to use the listbox  option as a checkbox option like in VB, true or false, Yes or No.