Lookup field

[jaddison]

hi. Love using Appgini.
I have 2 tables.
Team which has fields for Name and Logo.
Game which has fields for Home and Away.
These 2 fields have dropdown selection from Team table that inserts the Name of the team.
I would like to have the Logo in a field in Game table. This works fine for Auto Update if I only had Home.
When I have 2 fields Home and Away the logo shows correctly for the first field in table Game. in other words if
Home is first on the list of fields then the home logo shows for both Home and Away.
How do I get it to differentiate for the 2 entries...Home and Away.

regards
John

[pfrumkin]

Hi John,

I'm having trouble visualizing, can't tell if you're using hooks, JS, or standard AG. Sounds like you need a Home Logo and an Away Logo in your Game table. Can you put the code you are using for Auto Update?

~Paul

[jaddison]

Hi Paul
thanks for the reply

I'll try to explain using some screenshots
Home.jpg - Appgini tbl Game.Home from dropdown tbl Team.Name
HomeImage.jpg - Appgini tbl Game.HomeImage from dropdown tbl Team.Picture
Away.jpg - Appgini tbl Game.Away from dropdown tbl Team.Name
AwayImage.jpg - Appgini tbl Game.AwayImage from dropdown tbl Team.Picture
Result.jpg - the result showing the same image for both Home and Away.
How do I get Appgini to show the correct image for both Home and Away?

Hope this is clearer.
John

[jaddison]

Paul

The image for Home is correct, but the image for away is not, as you can see.

John

[pfrumkin]

Hi John,

I don't think that there is anything in the AutoFill that is linking the Away Image to the Away entry. I'm guessing it's just pulling the first entry. I haven't used AutoFIll a lot so maybe I don't know all about how it works. Rather than the AutoFill I would look at customizing that Lookup Field Query (click the Advanced button), pulling the picture based on the value of the Home for HomeImage and Away for AwayImage. Maybe you can make it ReadOnly.

~Paul

[pbottcher]

Hi Paul,

what you try to do is not possible via the standard AppGini implementation of the autfill feature. The autofill for a reference to a table allways works on the entry you select and will update all autofill entries that have a reference to that table.

if you want to get your solution, you need to modify the game_autofill.php file and apply the correct case to your needs.

Also you need to add an additional ajay call to the game_autofill.php to fill your "away" field when this has been selected.

Hope that helps.

[jaddison]

Wow. Getting involved.
What does the Advanced button do?
When I change the sql to "SELECT `Team`.`TeamID`, `Team`.`Picture` FROM `Team` WHERE `Team`.`TeamID` = `Game`.`Away` ORDER BY 2" it does not show an error but does not show the logo either, just a link to a random Team entry.

[jaddison]

Looking at the info when the advanced button is used. The sql only affects the list presented from the parent table, so its not going to help me as pböttcher indicated.
I'm at a loss as to how to modify the game_autofill.php, see code below:

<?php
// This script and data application were generated by AppGini 5.84
// Download AppGini for free from https://bigprof.com/appgini/download/

$currDir = dirname(__FILE__);
include("$currDir/defaultLang.php");
include("$currDir/language.php");
include("$currDir/lib.php");

handle_maintenance();

header('Content-type: text/javascript; charset=' . datalist_db_encoding);

$table_perms = getTablePermissions('Game');
if(!$table_perms[0]) { die('// Access denied!'); }

$mfk = $_GET['mfk'];
$id = makeSafe($_GET['id']);
$rnd1 = intval($_GET['rnd1']); if(!$rnd1) $rnd1 = '';

if(!$mfk) {
die('// No js code available!');
}

switch($mfk) {

case 'Home':
if(!$id) {
?>
$j('#HomeImage<?php echo $rnd1; ?>').html('&nbsp;');
$j('#AwayImage<?php echo $rnd1; ?>').html('&nbsp;');
<?php
break;
}
$res = sql("SELECT `Team`.`TeamID` as 'TeamID', `Team`.`Name` as 'Name', `Team`.`Image` as 'Image', `Team`.`Description` as 'Description', `Team`.`Colour1` as 'Colour1', `Team`.`Colour2` as 'Colour2', `Team`.`Colour3` as 'Colour3', concat('<i class=\"glyphicon glyphicon-', if(`Team`.`Visible`, 'check', 'unchecked'), '\"></i>') as 'Visible', concat('<i class=\"glyphicon glyphicon-', if(`Team`.`Complete`, 'check', 'unchecked'), '\"></i>') as 'Complete', `Team`.`CreatedTime` as 'CreatedTime', `Team`.`CreatedID` as 'CreatedID', `Team`.`LastEditedTime` as 'LastEditedTime', `Team`.`LastEditedID` as 'LastEditedID', `Team`.`Picture` as 'Picture' FROM `Team` WHERE `Team`.`TeamID`='{$id}' limit 1", $eo);
$row = db_fetch_assoc($res);
?>
$j('#HomeImage<?php echo $rnd1; ?>').html('<img src="thumbnail.php?i=<?php echo urlencode($row['Picture']); ?>&t=Team&f=Picture&v=dv" class="img-thumbnail">');
$j('#AwayImage<?php echo $rnd1; ?>').html('<img src="thumbnail.php?i=<?php echo urlencode($row['Picture']); ?>&t=Team&f=Picture&v=dv" class="img-thumbnail">');
<?php
break;


}

?>

[pbottcher]

Hi,

try to change

Code: Select all

switch($mfk) {

case 'Home':
if(!$id) {
?>
$j('#HomeImage<?php echo $rnd1; ?>').html('&nbsp;');
$j('#AwayImage<?php echo $rnd1; ?>').html('&nbsp;');
<?php
break;
}
$res = sql("SELECT `Team`.`TeamID` as 'TeamID', `Team`.`Name` as 'Name', `Team`.`Image` as 'Image', `Team`.`Description` as 'Description', `Team`.`Colour1` as 'Colour1', `Team`.`Colour2` as 'Colour2', `Team`.`Colour3` as 'Colour3', concat('<i class=\"glyphicon glyphicon-', if(`Team`.`Visible`, 'check', 'unchecked'), '\"></i>') as 'Visible', concat('<i class=\"glyphicon glyphicon-', if(`Team`.`Complete`, 'check', 'unchecked'), '\"></i>') as 'Complete', `Team`.`CreatedTime` as 'CreatedTime', `Team`.`CreatedID` as 'CreatedID', `Team`.`LastEditedTime` as 'LastEditedTime', `Team`.`LastEditedID` as 'LastEditedID', `Team`.`Picture` as 'Picture' FROM `Team` WHERE `Team`.`TeamID`='{$id}' limit 1", $eo);
$row = db_fetch_assoc($res);
?>
$j('#HomeImage<?php echo $rnd1; ?>').html('<img src="thumbnail.php?i=<?php echo urlencode($row['Picture']); ?>&t=Team&f=Picture&v=dv" class="img-thumbnail">');
$j('#AwayImage<?php echo $rnd1; ?>').html('<img src="thumbnail.php?i=<?php echo urlencode($row['Picture']); ?>&t=Team&f=Picture&v=dv" class="img-thumbnail">');
<?php
break;


}

to

Code: Select all

switch($mfk) {

case 'Home':
if(!$id) {
?>
$j('#HomeImage<?php echo $rnd1; ?>').html('&nbsp;');
<?php
break;
}
$res = sql("SELECT `Team`.`TeamID` as 'TeamID', `Team`.`Name` as 'Name', `Team`.`Image` as 'Image', `Team`.`Description` as 'Description', `Team`.`Colour1` as 'Colour1', `Team`.`Colour2` as 'Colour2', `Team`.`Colour3` as 'Colour3', concat('<i class=\"glyphicon glyphicon-', if(`Team`.`Visible`, 'check', 'unchecked'), '\"></i>') as 'Visible', concat('<i class=\"glyphicon glyphicon-', if(`Team`.`Complete`, 'check', 'unchecked'), '\"></i>') as 'Complete', `Team`.`CreatedTime` as 'CreatedTime', `Team`.`CreatedID` as 'CreatedID', `Team`.`LastEditedTime` as 'LastEditedTime', `Team`.`LastEditedID` as 'LastEditedID', `Team`.`Picture` as 'Picture' FROM `Team` WHERE `Team`.`TeamID`='{$id}' limit 1", $eo);
$row = db_fetch_assoc($res);
?>
$j('#HomeImage<?php echo $rnd1; ?>').html('<img src="thumbnail.php?i=<?php echo urlencode($row['Picture']); ?>&t=Team&f=Picture&v=dv" class="img-thumbnail">');
<?php
break;

case 'Away':
if(!$id) {
?>
$j('#AwayImage<?php echo $rnd1; ?>').html('&nbsp;');
<?php
break;
}
$res = sql("SELECT `Team`.`TeamID` as 'TeamID', `Team`.`Name` as 'Name', `Team`.`Image` as 'Image', `Team`.`Description` as 'Description', `Team`.`Colour1` as 'Colour1', `Team`.`Colour2` as 'Colour2', `Team`.`Colour3` as 'Colour3', concat('<i class=\"glyphicon glyphicon-', if(`Team`.`Visible`, 'check', 'unchecked'), '\"></i>') as 'Visible', concat('<i class=\"glyphicon glyphicon-', if(`Team`.`Complete`, 'check', 'unchecked'), '\"></i>') as 'Complete', `Team`.`CreatedTime` as 'CreatedTime', `Team`.`CreatedID` as 'CreatedID', `Team`.`LastEditedTime` as 'LastEditedTime', `Team`.`LastEditedID` as 'LastEditedID', `Team`.`Picture` as 'Picture' FROM `Team` WHERE `Team`.`TeamID`='{$id}' limit 1", $eo);
$row = db_fetch_assoc($res);
?>
$j('#AwayImage<?php echo $rnd1; ?>').html('<img src="thumbnail.php?i=<?php echo urlencode($row['Picture']); ?>&t=Team&f=Picture&v=dv" class="img-thumbnail">');
<?php
break;


}

Now you need to call game_autofill.php like it happens when you select a home item, but for the away case. mfk needs to be away.

[jaddison]

pböttcher
I bet you knew I would ask this next question, not only for myself but all those following the thread.
What do you mean by
"Now you need to call game_autofill.php like it happens when you select a home item, but for the away case. mfk needs to be away."

thanks in advance
John

[pfrumkin]

Hi John,

Yep, what I was thinking with the Advanced SQL doesn't work because we can't make anything fire off the SQL on the picture when the team is changed. That's the beauty of autofill, but you can't control the autofill well enough OOB.

Another option is to add the SQL in the Game_before_insert and Game_before_update hooks. The problem is that you don't get the real time update to your form, you would only see it after you Save. To get real time update would require JS and AJAX, becoming more involved.

Pascal, won't the game_autofill.php get updated everytime you generate the app?

~Paul

[jaddison]

Paul
I tried your suggestion and it works. I can live with that method, but if Pascal (is this name correct?) can lead the way on his method it would be great. That will mean that the logo is always correct.

thanks again
John