2. PHPRunner General questions
  3. [SOLVED] Is there a Grid_details_link_clicked event?
This topic is locked
[SOLVED]

Is there a Grid_details_link_clicked event?

 		11/13/2023 4:53:48 AM
PHPRunner General questions
P
 PK author

I have master-detail relationship of eval_groups as the master table and eval_group_details as a details table.The 2 are linked and everything works fine.
However, to add new records to the details table, I have a Lookup field on details table that gets its data from another table (lets call full_list). So full_list contains the list of all the items that can be selected into the details table. See image below:

img alt

When click "select", a list from full_list opens up for selection but what I want to do is to exclude from this popup list, items that already exist in the detail currently selected eval_group. I am familiar with how the construct the sql of the pop up list to do this but in this sql string, I also need to specify the group_id selected otherwise the query will exclude all items that exists in the details table for all eval_groups, instead of just the selected eval_group.

So the idea I have in mind is the capture (in maybe a global variable) the selected group_id when the grid_details_link is clicked. This was I can use this ID on the Before SQL Query event of the popup items list to make sure the query works correctly.
The problem here is that I'm struggling with which event the grid_details_link_clicked generates in order to achieve this.
Or is there a more elegant way to achieve this?
Sergey Kornilov admin 11/13/2023

We have click events for table rows and individual fields but it will not work here because this is not a regular grid but a lookup wizard.

I think you are looking for something custom, maybe to you can use multi-select lookup wizard to avoid selecting any question more than once.

G
 		Grdimitris 11/13/2023

Lets asume that the master table named master_t has fileds id and description. The details table named detail_t has fileds id,master_id and d_question. The table full_list has fields id,group_id and question.
In PHPRUNNER fields tab to details_t table and d_question filed use Lookup field custom query with this

SELECT question FROM full_list WHERE (question not in (select question from details_t))

In where write

group_id=:master_id
group_id is the field from full_list table where is the id of the master_t table. and :master_id is the field in detail_t with the connection to mastter table.

If the questions is for all the master records the same then leave the where empty.
P
 		PK author 11/14/2023

Thank you Grdimitris,
You said..The table full_list has fields id,group_id and question. But this is not the case. The table full_list is not really related to the master_t and details_t tables. It has a list of questions that can be selected and inserted into the details_t table. So its like master list of questions the user can select from into the details_t.

So in this statement..
SELECT question FROM full_list WHERE (question not in (select question from details_t)),

I need to specify the group_id in the details_t part of the query right? Like this:

SELECT question FROM full_list WHERE (question not in (select question from details_t where master_id = id_of_the_selected_master_record )),

otherwaise it will exclude questions in the full_list which exist in the details_t but belonging to ther group (or master id). That is where the problem is, I dont have the master_id to use in the query until the user has clicked the link. And when that link is clicked no event is raised for me catch and do a Before SQL Query on the questions list before it pops up.
G
 		Grdimitris 11/14/2023

The master_id is the id of the your master table. I mean is the link field between your master table and your detail table.
the Group id you use is a field of master, detail or full list table?
if the full list has no relation with the other tables then the custom query is
SELECT question FROM full_list WHERE (question not in (select question from details_t))
and leave the where with the 3 dots near the right end, in field properties as it shown in the screenshot
if the Group id is in details and in full list and you want to get all questions from full list with the spesific group id value then in the where in field properties pane you have to write the group id (belongs to full list table)=:group id (belongs to details table). Dont forget the semicolon after the =
If i cant make you undestand please write the actual table names, field names and the link that have each other (table.field connects to table.field). I will write it for you.

img alt

P
 		PK author 11/15/2023

Hi Grdimitris,
Thank you for your continued assistance.
This is to create the full_list of questions in mysql datatbase I call it appraisal_360_master

CREATE TABLE `appraisal_360_master` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Description` tinytext,
`Category` int(11) DEFAULT NULL,
`Comments` tinytext,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

Then this is for the master table of Groups

CREATE TABLE `appraisal_360_groups` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Description` varchar(45) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

Then this is for the details table

CREATE TABLE `appraisal_360_group_lines` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`GroupID` int(11) NOT NULL,
`QuestionID` int(11) NOT NULL,
PRIMARY KEY (`ID`,`GroupID`,`Question`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

then the relationship looks like this:

img alt

So the QuestionID in appraisal_360_group_lines is filled from ID in appraisal_360_master using Lookup wizard. An item in appraisal_360_master can be put in appraisal_360_group_lines more than once if only in a different group. So for instance in appraisal_360_group_lines you can have this:

img alt

In this case when I select group 1 and trying to add new questions, Question 1, 2 and 3 from appraisal_360_master should not be in the list and if I select group 2, then question 1 and 3 should not be in the list.
G
 		Grdimitris 11/15/2023

Create a custom view to table appraisal_360_master with this sql
SELECT
Description,
Category,
ID
FROM appraisal_360_master
where (ID not in (select QuestionID from appraisal_360_group_lines where GroupID=:GroupID))

and lets call the view as appraisal_360_questions.

Then in table appraisal_360_group_lines set the field groupID as Lookup field using the custom view appraisal_360_questions
The Link Field is ID, and the Display Field is Description.
That is your solution. The questions you can select to every appraisal_360_group_lines row is limited only to them tha have not insered with the same GroupID.

Dont forget to allow details table to appear in edit and view page of appraisal_360_groups table and in Pages pane change the Add new Settings to After record is added to Openthe new record Edit page.
P
 		PK author 11/15/2023

Grdimitris,
This is excellent. It works perfectly!!

Thank you so much.