Lookup Field vs What's Shown in Table View

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
TheNoLifer
Veteran Member
Posts: 67
Joined: 2015-06-06 12:10

Lookup Field vs What's Shown in Table View

Post by TheNoLifer » 2016-04-18 03:56

All,

I've always had this odd problem that I've never thought about too much, but I'm creating a new version of my app and I'd like to fix it.

I have custom lookup code in some tables to select a Person's ID, title (Mr, Mrs, etc), Forename, Surname;

SELECT `ContentItem_Persons`.`id`, IF(CHAR_LENGTH(`ContentItem_Persons`.`forename`) || CHAR_LENGTH(`ContentItem_Persons`.`surname`) || CHAR_LENGTH(`ContentItem_Persons`.`title`), CONCAT_WS('', `ContentItem_Persons`.`id`, ' - ', `ContentItem_Persons`.`title`, ' ', `ContentItem_Persons`.`forename`, ' ', `ContentItem_Persons`.`surname`), '') FROM `ContentItem_Persons` ORDER BY `ContentItem_Persons`.`id` ASC

So the lookup value looks like;
1 - lookup.JPG
1 - lookup.JPG (21.23 KiB) Viewed 934 times
That's cool. Exactly how I want it.

I also set the Lookup Parent Caption fields as follows;
2.JPG
2.JPG (18.43 KiB) Viewed 934 times
The two issues I'm having are

1 - Searching the lookups. When I drop down the lookup and start typing to search through the values I can only search for the forename and surname fields. If I search for the ID, or title, it returns no results;
3.JPG
3.JPG (16.54 KiB) Viewed 934 times
Why is that?

2 - In the Table view, the value of the lookup column is displayed as "forename surname" - how do I get it to display "ID, title, forename, surname", the same format as the lookup? I guess I have to edit the template code manually, which is a pain.
4.JPG
4.JPG (12.15 KiB) Viewed 934 times
Thanks for your help and comments!

A.

Bertv
Veteran Member
Posts: 52
Joined: 2013-12-11 15:59

Re: Lookup Field vs What's Shown in Table View

Post by Bertv » 2016-04-18 08:56

Hi,
I solved this as followed:
- add a field in the 'persons'-table an hidden field 'full_name' (rel_volledige_naam in dutch); hidden in table view and detail view. This field is filled in before insert and before update triggers. You can use the after insert and after update hooks too.
- this field is used as lookup field in the related tables
lookup.png
lookup.png (40.2 KiB) Viewed 929 times
Bert
I am using Appgini 5.62

TheNoLifer
Veteran Member
Posts: 67
Joined: 2015-06-06 12:10

Re: Lookup Field vs What's Shown in Table View

Post by TheNoLifer » 2016-04-19 02:20

Hi Bert,

Ok, I think that makes sense - annoying that we have to do that though!

Can you show me the code in your hook that you use to insert/update the full_name column? I'm still not quite sure how I'd build that column from the three parts (title, forename, surname).

Thanks!

Bertv
Veteran Member
Posts: 52
Joined: 2013-12-11 15:59

Re: Lookup Field vs What's Shown in Table View

Post by Bertv » 2016-04-19 13:28

Hi,
normally I use database triggers.
This is the code for an before insert trigger:
CREATE TRIGGER your_trigger_name BEFORE INSERT ON your_table_name
FOR EACH ROW begin
-- create ful_name
set new.full_name = trim(concat_ws(' ', new.title, new.forename, new.surname));
end


You can do the job in the after insert and after update hook too. the code:

$sql_string = "UPDATE your_table_name SET full_name = trim(concat_ws(' ', $data['title'], $data['forename'], $data['surname']))
WHERE your_table_id = " . $data['selectedID'] . ";";
$result = sql($sql_string, $eo);


or in just one row:
sql("UPDATE your_table_name SET full_name = trim(concat_ws(' ', $data['title'], $data['forename'], $data['surname'])) WHERE your_table_id = ".$data['selectedID'].";", $eo);

Succes.
Bert
I am using Appgini 5.62

TheNoLifer
Veteran Member
Posts: 67
Joined: 2015-06-06 12:10

Re: Lookup Field vs What's Shown in Table View

Post by TheNoLifer » 2016-04-19 14:23

Thank you so much Bert - that's what I suspected I'd have to do, but wasn't sure of syntax. I will give it a go!

Thanks again!

TheNoLifer
Veteran Member
Posts: 67
Joined: 2015-06-06 12:10

Re: Lookup Field vs What's Shown in Table View

Post by TheNoLifer » 2016-04-22 14:18

This worked perfectly, I'm happy to report. Good solution. Thanks!

xbox2007
Posts: 11
Joined: 2016-12-16 16:49

Re: Lookup Field vs What's Shown in Table View

Post by xbox2007 » 2017-11-13 08:45

nice

Post Reply