Custom Lookup table (v2018)

silvana · Post by **silvana** » Fri Apr 19, 2019 1:59 am

Hello
I am using 4 tables :
1- tbl_year: id, year, name
2- tbl_insurance: id, name
3- tbl_insurancetarrif: id, year_id, insurance_id, amount, desc

4- tbl_invoice: id, customer, year_id, insurance_id, amount

tbl_year

id year name
1 201801 2018-01
2 201802 2018-02

tbl_insurance

id name
1 ACompany
2 BCompany

tbl_insurancetarrif

id year_id insurance_id amount note
1 1 1 300$ record1- ACompany - 201801
2 2 1 320$ record2- ACompany - 201802
3 1 2 400$ record3- BCompany - 201801
4 2 2 500$ record4- BCompany - 201802

Now, On tbl_invoice i am using lookup with order
1- tbl_insurance.id -> show name
2- tbl_year.id -> show year

now i need to filter tbl_year.id based on tbl_insurance.id when in the table tbl_insurancetarrif both available.

How can I solve this with PHPMaker 2018?

Thanks

silvana · Post by **silvana** » Fri Apr 19, 2019 5:11 am

I try this method

Client Scripts --> Add/Copy Page --> Startup Script
$(function () {
$("#x_Insurance_ID").on("change", function() {
var insuranceValue = $('[name=x_Insurance_ID]').val();
});
});

// Lookup Selecting event
function Lookup_Selecting($fld, &$filter) {
if (CurrentPageID() == "add") {
if ($fld->FldName == "Insurance_introduction"){
ew_AddFilter($filter, "Year_ID IN (SELECT Year FROM tbl_insurance_introduction WHERE Insurance_ID = ???? ) ");
}
}
}

Now, is it possible to send insuranceValue when "x_Insurance_ID" selected to the end of Lookup_Selecting Query ?

arbei · Post by **arbei** » Fri Apr 19, 2019 9:01 am

You may try to use Dynamic Selection List using tbl_insurancetarrif as lookup table of the tbl_invoice.year_id field, and setting tbl_invoice.insurance_id as the parent field. The filter field of the lookup table being tbl_insurancetarrif.insurance_id. See the tutorial about "Dynamic Selection List" in the help file.

silvana · Post by **silvana** » Fri Apr 19, 2019 5:32 pm

Thank You, I know how to use Dynamic selection, the problem on your method in this case is how we can show the name of record, because it is link to a id on other table.

Easy explanation problem is :
I want to filter the field without using the filter feature in the graphical environment, i need to use client/startup script functions with JavaScript to do filter operation.
If parent field selected, based on value of parent field, i need to execute my filter query based on parent ID, then update child field select box.

Thanks,

mobhar · Post by **mobhar** » Sat Apr 20, 2019 9:51 am

Change this code:
ew_AddFilter($filter, "Year_ID IN (SELECT Year FROM tbl_insurance_introduction WHERE Insurance_ID = ???? ) ");

to:
ew_AddFilter($filter, "Year_ID IN (SELECT Year FROM tbl_insurance_introduction WHERE Insurance_ID = " . $this->Insurance_ID->CurrentValue . " ) ");

arbei · Post by **arbei** » Sat Apr 20, 2019 10:32 am

Before v2019, the Lookup_Selecting server event was only called on server side before the page is sent to browser. Since insurance_id is selected on the client side later, Lookup_Selecting cannot be used.

arbei wrote:
You may try to use Dynamic Selection List using tbl_insurancetarrif (note: not tbl_year) as lookup table of the tbl_invoice.year_id field ......

silvana · Post by **silvana** » Sat Apr 20, 2019 11:00 pm

So, Is it possible to use Lookup selecting in Client Side with Phpmaker 2019 ?
what is the function name ?

mobhar · Post by **mobhar** » Sat Apr 20, 2019 11:55 pm

As mentioned, then try Dynamic Selection List feature instead.

silvana wrote:
I know how to use Dynamic selection, the problem on your method in this case is how
we can show the name of record, because it is link to a id on other table.

Create a Database View that will join all those related fields from the related tables; you may use "the name of record" (assume it is its Description; not the ID) to display in Select control.