Show only certain records in drop down

[bruceholt]

Hi all,

I have a page where animal records are stored. In the records we have a couple of drop down which show the parentage of the animal. One is for the dam and one is for the sire. Obviously, I have have to have inherit access permissions ticked. I unticked the check box in Appgini so that I could change the default permissions. I had for one of them:

Code: Select all

SELECT `animals`.`id`, `animals`.`name` FROM `animals` WHERE `animals`.`sex` !='female' ORDER BY 2

and the other:

Code: Select all

SELECT `animals`.`id`, `animals`.`name` FROM `animals` WHERE `animals`.`sex` !='male' ORDER BY 2

but the dropdown would not show anything except the loading icon unless I was logged in as admin.

I tried to chenge the default to:

Code: Select all

SELECT `animals`.`id`, `animals`.`name` FROM `animals` LEFT JOIN `livestock_species` as livestock_species1 ON `livestock_species1`.`id`=`animals`.`species` LEFT JOIN `livestock_breed` as livestock_breed1 ON `livestock_breed1`.`id`=`animals`.`breed` LEFT JOIN `animals` as animals1 ON `animals1`.`id`=`animals`.`sire` LEFT JOIN `animals` as animals2 ON `animals2`.`id`=`animals`.`dam` WHERE `animals`.`sex` !='male' ORDER BY 2

but this also caused nothing to show.

Under the lookup for Sire, only the males should show and the same for Dam, only the females should be displayed.

Hoping someone can see what I am doing wrong, please.

[jsetzer]

Did you check your SQL queries with any SQL tool like phpMyAdmin or Adminer or SQL Workbench?

[pbottcher]

Hi,

as you reference another field you need to choose that field as the one you compare.

As I cannot see you defintions it is hard to say which one holds the values 'male' and 'female'.

Maybe you can post those definitions

[bruceholt]

Hi,

The table named "animals" has a field named "sex". I can to add the sire (male) and the dam (female) to an animals record. The sires and dams are just lookups from the same table but if I use default Advanced Lookup Options it shows both sexes. If I select Sire it should only show the males and if I select Dam, it should only show the females.

I did and SQL query using phpmyadmin with success but cannot work out how and where the query should go.

The sql query I used in phpmyadmin is:

Code: Select all

SELECT * FROM animals WHERE sex='Female'

[bruceholt]

I can get it to work correctly if I place

Code: Select all

SELECT * FROM animals WHERE sex='Female' 

into the Advanced Lookup Options but when I click Inherit Access Permissions, nothing will display only the spinning loaded.

This is the part concerning the dropdown list in the ajax_combo.php file:

Code: Select all

'sire' => array(
				'parent_table' => 'animals',
				'parent_pk_field' => 'id',
				'parent_caption' => '`animals`.`name`',
				'parent_from' => '`animals` LEFT JOIN `livestock_species` as livestock_species1 ON `livestock_species1`.`id`=`animals`.`species` LEFT JOIN `livestock_breed` as livestock_breed1 ON `livestock_breed1`.`id`=`animals`.`breed` LEFT JOIN `animals` as animals1 ON `animals1`.`id`=`animals`.`sire` LEFT JOIN `animals` as animals2 ON `animals2`.`id`=`animals`.`dam` ',
				'filterers' => array(),
				'custom_query' => 'SELECT * FROM animals WHERE sex=\'Female\'  ORDER BY 2',
				'inherit_permissions' => true,
				'list_type' => 0,
				'not_null' => false
			),

[pbottcher]

Hi,

can you try as query in the advance sql (and via phpmyadmin)

Code: Select all

SELECT `animals`.`id`, `animals`.`name` FROM `animals` LEFT JOIN `livestock_species` as livestock_species1 ON `livestock_species1`.`id`=`animals`.`species` LEFT JOIN `livestock_breed` as livestock_breed1 ON `livestock_breed1`.`id`=`animals`.`breed` LEFT JOIN `animals` as animals1 ON `animals1`.`id`=`animals`.`sire` LEFT JOIN `animals` as animals2 ON `animals2`.`id`=`animals`.`dam` where `animals`.`sex`='Female'

[bruceholt]

Hi pböttcher,

In phomyadmin is shows all the required results except that it shows all users records and the same when used in Appgini advanced options. When I then check the Inherent Access Permissions, nothing will show except the ajax loader.