How to get nested data from multi tables?

PHPMaker General Discussion (PHPMaker)
1

Dear Experts, Greetings !!using V2022
We have tables → class, section, student, feesetup, feetxns…class → id, classname
section → id, class, section, teacher
student → id, regid, name, father,…
feesetup → id, class, fee1, fee2, fee3, fee4, total_Fee…
feetxns → id, date, rcptno, stud_id, class_id, fee1, fee2,fee3, totalfee, etc…in feetxns → after selecting student id got master data from Student master through AutoFill, how to fetch FEESETUP records same time after selecting student id.
(searching in lookup selecting or recordset option but no result.)thanks in advance :slight_smile:

2

You may try setup AutoFill for the Classid field.

3

As we select student → RegId, we got student basic info [AutoFill] like Name, Father, Class, etc…

how to auto select FeeSetup → Fee1, Fee2, Fe3, TotalFee… there are only 1 loopup child option in AutoFill, how to fetch all related records from multi tables linked with student’s id.Thanks

4

You need to create a view in your database to join the table first.

5

Any Input Field [Stud->RegId] Entry/Selection Level Execute Query Option for selecting multi table’s records. Have to verify conditions first Student’s basic info then select data from execute query.Just give us a hint or idea for run sql query in change / after lostfocus event. Thanks

6

You may try some examples from Ajax by API and Client Scripts.

7

after study https://discourse.hkvstore.com/t/ajax-startup-script-to-fill-fields-v2019/2203/1 and Ajax by API and Client Scripts, tried some code from this forums but getting 401 in console, any help? Thanks :)Request URL: http://localhost/school/api/?action=getfeesetup&qno=1&mclassno=1&oldnew=Old
Request Method: GET
Status Code: 401 Unauthorizedin API Action

$API_ACTIONS["getfeesetup"] = function(Request $request, Response $response) {
    $mclassno = Param("mclassno"); // Get the input value from $_GET or $_POST
    $qno = Param("qno");
    $oldnew = Param("oldnew");
    if ($mclassno !== NULL && $qno !== NULL && $oldnew !== NULL)
        WriteJson(ExecuteRow("SELECT * FROM feesetup WHERE qno = ".AdjustSql($qno)." and mclassno = ".AdjustSql($mclassno)." and groupid = 1" ));
    };

in Startup Script

$("#x_regno").change(function() {
    //var url = ew.API_URL, action = "getfeesetup", id = encodeURIComponent($(this).val());
    var url = ew.API_URL;
    var action = "getfeesetup";
    var qno = encodeURIComponent($("#x_qno").val());
    var mclassno = encodeURIComponent($("#x_mclassno").val());
    var oldnew = encodeURIComponent($("#x_oldnew").val());
    //$.get(url + "/" + action + "?ProductID=" + id, function(res) { // URL format if URL Rewrite enabled
    $.get(url + "?action=" + action +"&qno="+qno+"&mclassno="+mclassno+"&oldnew="+oldnew, function(res) { // Get response from custom API action
        if (res){
            if (oldnew == "Old"){
                $("#x_mctqdue").val(res['mctqfeeo']); // Set the result (manipulate it first if necessary) to the target field
                $("#x_mcdue1").val(res['mcfeeo1']);
                $("#x_mcdue2").val(res['mcfeeo2']);
            }else{
                $("#x_mctqdue").val(res['mctqfeen']); // Set the result (manipulate it first if necessary) to the target field
                $("#x_mcdue1").val(res['mcfeen1']);
                $("#x_mcdue2").val(res['mcfeen2']);
            }
        }
    });
});
8
  1. If you site has a base path, you should use: var url = ew.PATH_BASE + ew.API_URL;
  2. You better use Api_Action server event, see Create Your Own API Action. ($API_ACTIONS is deprecated.)
9

Thanks a lot Experts !! Solved. Thanks a Lot :slight_smile: +10