AG 5.8: Help with SQL to retrieve single value

[rprevost]

Hello to you all,

I would like to retrieve some info from a table using a calculated field. It would behave as a vlookup in Excel.

Here is the situation:
Table people:
id,
name,
phone,
email

Table event:
id,
name,
supervisor_ name,
supervisor_ phone,
supervisor_ email,
assistant_ name,
assistant_ phone,
assistant_ email.

supervisor_ name: Lookup field in the people table
supervisor_phone: Lookup field with auto-fill
supervisor_emai: Lookup field with auto-fill

assistant_name: Lookup field in the people table
assistant_phone and assistant_email cannot be retrieve with autofill

I thought a calculated field could do the job.

SELECT `supervisor`.`phone`
FROM `supervisor`
WHERE `supervisor`.`id` = `event`.`assistant_name`;

Of course this SQL statement does not work since I am not good at it.
I would surely appreciate some help.

Regards,

RP

[pbottcher]

Hi,

try

select people.phone from people left join event on event.assistant_name=people.id where event.id=#ID#

[rprevost]

Thank you for the suggestion. However nothing was displayed for phone or email.

Note that the true names of tables are: people table real name is superviseur, event table real name is stage

Code: Select all

SELECT `superviseur`.`telephone` FROM `superviseur`
LEFT JOIN `stage` ON `superviseur`.`id` = `stage`.`cosupernom`
WHERE `stage`.`id` = #ID#

In the developer section of Chrome I received two warnings while choosing the cosupernom from dropdown list:
[DOM] Found 2 elements with non-unique id #superviseur_add_new: (More info: https://goo.gl/9p2vKq)
[DOM] Found 2 elements with non-unique id #superviseur_view_parent: (More info: https://goo.gl/9p2vKq)

I do not know what to make of it ... as usual.
Regards

[pbottcher]

Hi,

please use % instead of #

Code: Select all

SELECT `superviseur`.`telephone` FROM `superviseur`
LEFT JOIN `stage` ON `superviseur`.`id` = `stage`.`cosupernom`
WHERE `stage`.`id` = %ID%

[rprevost]

Thank you Mr Böttcher,

Almost perfect. The lookup is returning the proper info. If I choose another cosupernom from the dropdown list and save, the new telephone appears. Great.
However, if I choose nothing from the dropdown list (ex. there is no more cosuper for this particular stage, then the phone number of the "previous" cosuper still appears. I refresh the browser page (normal reload, hard reload, empty cache and hard reload) with the same result. Maybe there is a way to add an if statement to check if the cosupernom is "null" before launching the Select statement.
Regards

[rprevost]

Okay, here is the problem.
I wanted to mimic the behavior of a "Lookup field" so I would get a dropdown list and some "autofill" fields.
I already used the Lookup field for the Supervisor (got back the name, telephone and email).
This means that I cannot use the Lookup field for the Co-Supervisor (got back the name but telephone and email belongs to the Supervisor).
So I thought that using a "calculated field" I could bypass this limitation in AG.
This solution is working but not to my satisfaction since one has to "save" the record in order to see the telephone and email of the supervisor. Using the Lookup field and auto-fill the update appears instantly after a change in the choice of the supervisor. If I select nothing in the supervisor dropdown list, then the telephone and the email is instantly update (empty). This is the behavior I would like to obtain.
I hope someone can help me since sometimes I have 4 or 5 of these dropdown lists that point to the same reference table.
Regards

[pbottcher]

Hi,

in order to circumvent your problem I would suggest that you create multiple views to the original table.

Create the view view_supervisor in the database as

select * from superviseur

Then create a table view_supervisor as a copy of the supervisor table.

Now reference (lookup) the stage.cosupernom to the view_supervisor table and autofill both other fields as well to that view.

Like that you have only to maintain one table (supervisor), but can use the lookup and autofill feature.

Only thing you need to keep in mind. If you change the definitions or fields in the supervisor table, you need to recreate the view and recopy the view_supervisor table.

Hope that helps