Get HOD of a department in an ajax subform

Hello,

I have a question on ajax subform. I have a table which contains cost center with departments that needs to validate the workflow :

CostCenter 1 : Dept A - Dept B - Dept C

CostCenter 2 : Dept B - Dept C - Dept D

CostCenter 3 : Dept A - Dept C - Dept D

In my parent form, I have a select box with all cost center Inside and just below, I have an ajax subform. What I would like to get is when we select a value in the select box, we get HOD users of each three departments in the subform. So :

Parent form :

Selectbox : CostCenter 1 selected

Ajax subform :

HOD user of Dept A
HOD user of Dept B
HOD user of Dept C

Parent form :

Selectbox : CostCenter 2 selected

Ajax subform :

HOD user of Dept B
HOD user of Dept C
HOD user of Dept D

So here are my questions :

Is there an hash variable that gives the HOD user for a given department ?
I have tried to use Bean shell form binder but I am blocked because I can't get the select box value in the parent form when I am in the subform. Is there a way to get the selectbox value in the ajax subform so I can use it in my Bean shell form binder. Here is my code. If you have an other idea, I also take it.

Thanks for your help

import org.joget.apps.form.model.*;
import org.joget.apps.form.service.*;
import java.sql.*;
import org.apache.commons.collections.SequencedHashMap;
import java.util.*;
FormRowSet f = new FormRowSet();     
f.setMultiRow(true);
 
Class.forName("com.mysql.jdbc.Driver").newInstance();
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jwdb?characterEncoding=UTF-8", "username", "password");
if(!con.isClosed()){

String sql = "select a.id, a.c_field_cc_texte, c1.userId as list_responsable, c2.userId as list_controleur, c3.userId as list_directeur FROM app_fd_tbl_centrecout a LEFT JOIN dir_department b1 on a.c_field_cc_responsable=b1.id LEFT JOIN dir_employment c1 on b1.hod=c1.id LEFT JOIN dir_department b2 on a.c_field_cc_controleur=b2.id LEFT JOIN dir_employment c2 on b2.hod=c2.id LEFT JOIN dir_department b3 on a.c_field_cc_directeur=b3.id LEFT JOIN dir_employment c3 on b3.hod=c3.id"; 

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

while (rs.next()) {
 FormRow r1 = new FormRow();
 r1.put("id", rs.getString(1));
 r1.put("field_cc_texte", rs.getString(2));
 r1.put("field_cc_responsable", rs.getString(3));
 r1.put("field_cc_controleur", rs.getString(4));
 r1.put("field_cc_directeur", rs.getString(5));
 f.add(r1);
                  }
return f;
rs.close();
stmt.close();
con.close();    }

Space shortcuts

Get HOD of a department in an ajax subform

1 answer