This topic is locked

Modifying look up wizard multi values in field events

2/28/2018 7:11:32 AM
PHPRunner Tips and Tricks
S
Scrolin author

The look up wizard is an excellent way of entering data requiring set values.
I've recently needed a look up wizard using a checkbox list where some options are implied by others. Having all the combinations as separate checkboxes would be unwieldly for both the user and me as programmer.
My first attempt simply added the associated values at the and of the array, but this upset the PHPR code supporting it causing subsequent changes to the same field to behave strangely, such as the wrong options being selected.
I just happened to notice that the values arrays had numeric keys, and that the keys, although not linear, would always be in the same order as the defined field display order. Further experimentation also showed that the key values did not always exactly match the defined field display order.
Penny dropped! The order of the selected values and the defined field display order need to be the same.
My lookup wizard maps onto this view

SELECT

TrophyCode,

TrophyName,

TopN,

HonoursOnly,

`Desc`,

HonoursOnly*100 + ifnull(EventID,0) as DisplayOrder

FROM ineligibletrophycodes

WHERE (TopN != 0) OR (HonoursOnly =true)



TrophyCode is the linked field, and DisplayOrder the display order (obviously - shame PHPR insists on a single field for the display order).
In this example, where EventID is NULL, the TrophyCode implies all other trophy codes for the same combination of TopN and HonoursOnly. Thus selecting the corresponding entry should cause all the others to be selected. Deselecting a TrophyCode for a particular event would force the 'AllEvents' trophy code to be deselected. etc etc.
Adding the following code at the end of the change field event server code fixed the strange behaviour

$result['newvalue']=array();

$rs=CustomQuery("select TrophyCode from ineligibletrophycodes where TrophyCode in ('".implode("','",$newlist)."') ORDER BY HonoursOnly,ifnull(EventID,0);");

while ($data=db_fetch_array($rs)) {

$result['newvalue'][]=$data['TrophyCode'];

}


This together with setting this.defaultValue to the new value at the end of the Client After change field event code such that each change can be monitored gives the look up wizard sensible behaviour.
Voila!

S
Scrolin author 3/4/2018

Here's the full change field event code,

Client Before:

params['newvalue']=this.getValue(); // The values set including this change

params["oldvalue"]=this.defaultValue; // The values set before this change (not necessarily the value held on the DB if this event has been triggered before - see Client After phase)



Server:

$newlist=$params['newvalue'];

$addcode=array_values(array_diff($params['newvalue'],$params['oldvalue']));

$subtractcode=array_values(array_diff($params['oldvalue'],$params['newvalue']));

// Not possible to set/clear more than one checkbox at a time so $addcode and $subtractcode can only have one value between them

if (count($addcode) != 0) {

$rs=CustomQuery("select EventID, TopN, HonoursOnly from ineligibletrophycodes where TrophyCode = '".$addcode[0]."';");

$data=db_fetch_array($rs);

if ($data['HonoursOnly']) {

$qual=" AND HonoursOnly;";

} else if ($data['TopN']) {

$qual=" AND TopN;";

} else {

$qual=";";

}

if (is_null($data['EventID'])) { // Is wildcard code, add all codes not already set

$rs=CustomQuery("select TrophyCode from ineligibletrophycodes where EventID is not NULL AND TrophyCode NOT IN ('".implode("','",$params['oldvalue'])."')".$qual);

$params['Adding']="";

while ($data=db_fetch_array($rs)) {

$newlist[]=$data['TrophyCode'];

}

} else {

$rs=CustomQuery("select TrophyCode from ineligibletrophycodes where EventID is not NULL AND TrophyCode NOT IN ('".implode("','",$params['newvalue'])."')".$qual);

if (!$data=db_fetch_array($rs)) { // No event types excluded, add the wildcard if one exists

$rs=CustomQuery("select TrophyCode from ineligibletrophycodes where EventID is NULL AND TrophyCode NOT IN ('".implode("','",$params['newvalue'])."')".$qual);

if ($data=db_fetch_array($rs)) {

$newlist[]=$data['TrophyCode'];

} } }

} else if (count($subtractcode) != 0) {

$rs=CustomQuery("select EventID, TopN, HonoursOnly from ineligibletrophycodes where TrophyCode = '".$subtractcode[0]."';");

$data=db_fetch_array($rs);

if ($data['HonoursOnly']) {

$qual=" AND HonoursOnly;";

} else if ($data['TopN']) {

$qual=" AND TopN;";

} else {

$qual=";";

}

if (is_null($data['EventID'])) {// Removed code is the wildcard, confirm that all others are set for legacy case before refusing the change

$rs=CustomQuery("select TrophyCode from ineligibletrophycodes where EventID is not NULL AND TrophyCode NOT IN ('".implode("','",$params['newvalue'])."')".$qual);

if (!$data=db_fetch_array($rs)) { // No event types excluded, cannot remove wildcard, put it back

$newlist=$params['oldvalue'];

}

} else { // Check if Wildcard is in the list and remove it

$rs=CustomQuery("select TrophyCode from ineligibletrophycodes where EventID is NULL AND TrophyCode IN ('".implode("','",$params['newvalue'])."')".$qual);

if ($data=db_fetch_array($rs)) {

$newlist=array_values(array_diff($params['newvalue'],array($data['TrophyCode'])));

} } }

// Now reconstruct the newlist such that the codes are in the same order as in the checkbox list display order (else things get confused)

$result['newvalue']=array();

if (count($newlist) != 0) {

$rs=CustomQuery("select TrophyCode from ineligibletrophycodes where TrophyCode in ('".implode("','",$newlist)."') ORDER BY HonoursOnly,ifnull(EventID,0);");

while ($data=db_fetch_array($rs)) {

$result['newvalue'][]=$data['TrophyCode'];

}

}



Client After:

this.setValue(result["newvalue"],false); // Don't trigger event for this

this.defaultValue=result["newvalue"]; // Ensure that if field is changed again , this new current value is used as the reference point