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;
That's cool. Exactly how I want it.
I also set the Lookup Parent Caption fields as follows;
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;
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.
Thanks for your help and comments!
A.
Lookup Field vs What's Shown in Table View
-
- Veteran Member
- Posts: 67
- Joined: 2015-06-06 12:10
Re: Lookup Field vs What's Shown in Table View
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
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
Bert
I am using Appgini 5.75
I am using Appgini 5.75
-
- Veteran Member
- Posts: 67
- Joined: 2015-06-06 12:10
Re: Lookup Field vs What's Shown in Table View
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!
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!
Re: Lookup Field vs What's Shown in Table View
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.
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.75
I am using Appgini 5.75
-
- Veteran Member
- Posts: 67
- Joined: 2015-06-06 12:10
Re: Lookup Field vs What's Shown in Table View
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!
Thanks again!
-
- Veteran Member
- Posts: 67
- Joined: 2015-06-06 12:10
Re: Lookup Field vs What's Shown in Table View
This worked perfectly, I'm happy to report. Good solution. Thanks!