Question about parent tables

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
brudy
Veteran Member
Posts: 38
Joined: 2023-02-04 11:35

Question about parent tables

Post by brudy » 2023-03-14 19:13

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

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1806
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Question about parent tables

Post by jsetzer » 2023-03-14 19:39

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.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

brudy
Veteran Member
Posts: 38
Joined: 2023-02-04 11:35

Re: Question about parent tables

Post by brudy » 2023-03-14 20:47

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


Thnaks

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1806
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Question about parent tables

Post by jsetzer » 2023-03-14 20:58

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

Create a new field, set as readonly, on tab calculated field put sql command. That's all.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

brudy
Veteran Member
Posts: 38
Joined: 2023-02-04 11:35

Re: Question about parent tables

Post by brudy » 2023-03-14 21:08

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


User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1806
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Question about parent tables

Post by jsetzer » 2023-03-14 21:27

Post your table names, column names and lookup definition here. Then we can help you writing the SQL command.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

brudy
Veteran Member
Posts: 38
Joined: 2023-02-04 11:35

Re: Question about parent tables

Post by brudy » 2023-03-14 21:32

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
Veteran Member
Posts: 38
Joined: 2023-02-04 11:35

Re: Question about parent tables

Post by brudy » 2023-03-15 07:49

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
Veteran Member
Posts: 38
Joined: 2023-02-04 11:35

Re: Question about parent tables

Post by brudy » 2023-03-15 12:12

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%';

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1806
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Question about parent tables

Post by jsetzer » 2023-03-15 12:31

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.
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

brudy
Veteran Member
Posts: 38
Joined: 2023-02-04 11:35

Re: Question about parent tables

Post by brudy » 2023-03-15 13:02

and do I have to use JOIN?

brudy
Veteran Member
Posts: 38
Joined: 2023-02-04 11:35

Re: Question about parent tables

Post by brudy » 2023-03-15 13:15

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%'

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1806
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Question about parent tables

Post by jsetzer » 2023-03-15 13:32

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
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

brudy
Veteran Member
Posts: 38
Joined: 2023-02-04 11:35

Re: Question about parent tables

Post by brudy » 2023-03-15 14:15

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
Veteran Member
Posts: 38
Joined: 2023-02-04 11:35

Re: Question about parent tables

Post by brudy » 2023-03-15 14:26

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
Veteran Member
Posts: 38
Joined: 2023-02-04 11:35

Re: Question about parent tables

Post by brudy » 2023-03-15 15:40

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
Veteran Member
Posts: 38
Joined: 2023-02-04 11:35

Re: Question about parent tables

Post by brudy » 2023-03-15 15:53

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
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Question about parent tables

Post by pböttcher » 2023-03-15 20:20

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%'
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

brudy
Veteran Member
Posts: 38
Joined: 2023-02-04 11:35

Re: Question about parent tables

Post by brudy » 2023-03-15 20:52

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

Post Reply