Question about parent tables

[brudy]

Hi all,

When we link a table, I notice that it records the ID of the parent table and not the content of the "name" field (parent caption field)

Is it possible to change this and save the name and not the ID

Thanks

[jsetzer]

By default it will always be the primary key of the master table. That's the way it was designed.

If you need data inside the table, not only the keys, you can, for example, add more readonly columns and configure them as calculated fields, automatically filled with the master's data.

[brudy]

oulala ... How do you do that ? Do you have an example ?


Thnaks

[jsetzer]

https://bigprof.com/appgini/help/calculated-fields

Create a new field, set as readonly, on tab calculated field put sql command. That's all.

[brudy]

yes I went on this page, the problem is precisely the SQL query ... I do not see how to build it to display the information I need

[brudy]

https://www.zen2cool.com/test3.png
https://www.zen2cool.com/test4.png

[jsetzer]

Post your table names, column names and lookup definition here. Then we can help you writing the SQL command.

[brudy]

I just need one correct request and then I will understand the logic, I have posted two photos in the previous post

https://www.zen2cool.com/test3.png
https://www.zen2cool.com/test4.png
https://www.zen2cool.com/test5.png
thanks

[brudy]

I don't know how to put a picture I get this error message every time! : "It was not possible to determine the dimensions of the image. Please verify that the URL you entered is correct."

I have a table "Produits" which contains 3 fields id_prod, fourni and prod
I have a table "bondecommande" with a linked field that takes the name of the product (dropdown list) parent child with Produits/prod
When I select a product, it records in the DB the id_prod (1, 2, 3 ...)
I have created a "test" field in "bondecommande" ( read only ) and in this test field I would like it to record the "prod" of the table Produits and not the "id_prod"

https://www.zen2cool.com/test3.png
https://www.zen2cool.com/test4.png
https://www.zen2cool.com/test5.png

[brudy]

I have tried several configurations including this but it doesn't work

Code: Select all

UPDATE `bondecommande`
LEFT JOIN `Produits` ON `Produits`.`id_prod` = `bondecommande`.`bon_produit`
SET `bondecommande`.`test1` = `Produits`.`prod`
WHERE `bondecommande`.`bon_produit` = '%ID%';

[jsetzer]

According to the docs about calculated fields I have given to you before (https://bigprof.com/appgini/help/calculated-fields) there is no need for an update-SQL-command but for a SQL-SELECT statement, more precisely: for a SQL-SELECT statement returning exactly one value. That one value will be set automatically.

[brudy]

and do I have to use JOIN?

[brudy]

not to beat around the bush, I have to pay how much and to whom for a concrete example?

I tried all the solutions with select and where and I have nothing in the field test1 .... while I don't want to see the product id but the content of the field prod

Code: Select all

SELECT `Produits`.`prod`  `Produits`.`id_prod` FROM `Produits`
WHERE `Produits`.`id_prod` ='%ID%'

[jsetzer]

Examples can be found in the docs for calculated fields, mentioned above. The rest is pure SQL and requires some SQL-knowledge plus your specific table names, column names and relations (lookup-configuration).

Example:

Tables

Projects

Code: Select all

projects
-id
-name
-...

Tasks

Code: Select all

tasks
-id
-project_id
-project_name
-...

Relation

Code: Select all

tasks.project_id --lookup--> projects.id

This is the SQL query for automatically calculating tasks.project_name from projects.name.

Simple version* for better readability:

Code: Select all

select name from tasks inner join projects on tasks.project_id=projects.id where tasks.id='%ID%'

Alternatively*

Code: Select all

SELECT `projects`.`name` 
FROM `tasks`
INNER JOIN `projects` 
ON `tasks`.`project_id`=`projects`.`id`
WHERE `tasks`.`id`='%ID%'

* Should work, but I did not take the time to setup a special testing-project just for this one calculated field

[brudy]

thank you for your time but I'm going to give up, it doesn't work, I'm going to continue my orders with my good old pen and paper, I've already wasted enough time and money with this; I have walls to build.

[brudy]

last essay because I think you didn't understand

top table : Produits
bottom table :bondecommande

https://www.zen2cool.com/lasttest.png

Code: Select all

SELECT * FROM `bondecommande`  INNER JOIN `Produits` ON `Produits`.`prod`=`bondecommande`.`bon_produit` WHERE `Produits`.`id_prod`='%ID%'

[brudy]

Code: Select all

SELECT `bondecommande`.`bon_produit`
FROM `bondecommande`
INNER JOIN `Produits`
ON  `Produits`.`prod` = `bondecommande`.`bon_produit` 
WHERE `bondecommande`.`id_bon`='%ID%'

with this code it fills the field test1 but with the value of the id and not with the text

[brudy]

currently I have the id number in the test1 column instead of the text corresponding to the id

https://www.zen2cool.com/test6.png

[pböttcher]

Hi, you need to select the text from the produits table. Also the join needs to reflect the field that are connected `Produits`.`id` and `bondecommande`.`bon_produit`

Try

Code: Select all

SELECT `Produits`.`prod`
FROM `bondecommande`
INNER JOIN `Produits`
ON  `Produits`.`id` = `bondecommande`.`bon_produit` 
WHERE `bondecommande`.`id_bon`='%ID%'

[brudy]

Many thanks Pböttcher, I had to change the request to this
now I can put this code for my other fields good evening
I was wrong with the SELECT FROM where in a normal query you select the fields in the table where it is located and here you select the fields in the other database

Code: Select all

SELECT `Produits`.`prod`
FROM `bondecommande`
INNER JOIN `Produits`
ON  `Produits`.`id_prod` = `bondecommande`.`bon_produit` 
WHERE `bondecommande`.`id_bon`='%ID%'

Thanks