SET Value of Parent TABLE field in Child field

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
dklerk
Posts: 5
Joined: 2019-07-25 08:10

SET Value of Parent TABLE field in Child field

Post by dklerk » 2019-07-25 08:37

I have gone through the forum discussions but I can't seem to get this right.

I have three tables:

outcomes
criteria
questions

In ‘outcomes’ I have a text input field called ‘SONumber’. The other two tables have two Integer fields as a lookup to the ‘SONumber’ in ‘outcomes’ table. When I insert the associated records in the ‘criteria’ and ‘questions’ tables, it insert the ID value in the MySQL database field instead of the text value that was inserted in the ‘SONumber’ field in the ‘outcomes’ field. I need the text value in ‘outcomes’ to be set after insert and updating.

I was thinking of creating a after insert SQL statement and after update SQL statement in the specific hooks file, but I am struggling to get the right code for it.

Can you please help me with the correct code to achieve this?

Thank you in advance!

sjohn
Veteran Member
Posts: 84
Joined: 2018-05-23 09:32

Re: SET Value of Parent TABLE field in Child field

Post by sjohn » 2019-07-25 15:26

Maybe I misunderstand you, but you need no code for this.

In the field in the criteria and questions, you have as lookup in outcomes, you set the
Parent caption field part 1, to the textvalue you want to see.
Then in criteria and questions you will have a SOnumber. This field is a dropdown field and will show the textvalue for the SOnumber.
Hope I understood what you described, and hope you understand what I try to describe.

If not then maybe post an image showing your structure.

dklerk
Posts: 5
Joined: 2019-07-25 08:10

Re: SET Value of Parent TABLE field in Child field

Post by dklerk » 2019-07-25 18:35

Thank you for your reply.

I get the lookup fields to work properly. I can see it when selected, but it writes the actual ID to the database in the field name I have selected and which is the field name in my MySQL database. So instead of the value, it populates the parent ID value. I don't want the ID value populated in the field of the database, but what I see on the drop down (lookup) needs to get populated. I need the text value to be populated.

Hope this clarifies it better.

outcomes table:
ID | SONumber | SODescription
4 | 1 | Some text

criteria table:
ID | SONumber | SODescription
5 | 4 | 4

The SONumber in outcomes was inserted as text and setup as a lookup in criteria. Now in the criteria table in the database it contains the ID value of outcomes. The value '1' should be inserted in the criteria SONumber field.

sjohn
Veteran Member
Posts: 84
Joined: 2018-05-23 09:32

Re: SET Value of Parent TABLE field in Child field

Post by sjohn » 2019-07-26 07:12

Okay - I see. Although I still don't understand why you want to store the text instead of the ID, I think I understand the situation.

I think this, however - will require some code that I cannot help with.

But this forum has many great users that can do this - I think.
I will now leave this thread and hope you will get help from a capable person.

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

Re: SET Value of Parent TABLE field in Child field

Post by jsetzer » 2019-07-26 07:38

Hi,

if you only need to show outcomes.SODescription in criteria-tableview and criteria-detailview it should be enough to set criteria.SODescription as lookup towards outcomes.SODescription and check [x] Autofill.

But if you need the text value inside criteria-table you are right: you'll have to "copy" the value from outcomes.SODescription to criteria.SODescription.
  1. Change datatype of criteria.SODescription to Text
  2. re-generate your application
  3. in hooks/criteria.php create a new function

    Code: Select all

    function criteria_updateSODescription(&$data) {
        $outcome_id = $data["SONumber"];
        $outcome_description = sqlValue("SELECT SODescription FROM outcomes WHERE ID={$outcome_id} LIMIT 1");
        $data["SODescription"] = $outcome_description;
    }
    
  4. call that function in _before_insert and _before_update like this:

    Code: Select all

    criteria_updateSODescription($data);
    
I did not test the code in detail, so there may be typos :oops:, but it should work.

Hope this helps!
Kind regards,
Jan

dklerk
Posts: 5
Joined: 2019-07-25 08:10

Re: SET Value of Parent TABLE field in Child field

Post by dklerk » 2019-07-26 15:36

sjohn wrote:
2019-07-26 07:12
Okay - I see. Although I still don't understand why you want to store the text instead of the ID, I think I understand the situation.

I think this, however - will require some code that I cannot help with.

But this forum has many great users that can do this - I think.
I will now leave this thread and hope you will get help from a capable person.
Thank you very much for your efforts to help. I do appreciate it.

Regards,

Danie

dklerk
Posts: 5
Joined: 2019-07-25 08:10

Re: SET Value of Parent TABLE field in Child field

Post by dklerk » 2019-07-26 15:38

jsetzer wrote:
2019-07-26 07:38
Hi,

if you only need to show outcomes.SODescription in criteria-tableview and criteria-detailview it should be enough to set criteria.SODescription as lookup towards outcomes.SODescription and check [x] Autofill.

But if you need the text value inside criteria-table you are right: you'll have to "copy" the value from outcomes.SODescription to criteria.SODescription.
  1. Change datatype of criteria.SODescription to Text
  2. re-generate your application
  3. in hooks/criteria.php create a new function

    Code: Select all

    function criteria_updateSODescription(&$data) {
        $outcome_id = $data["SONumber"];
        $outcome_description = sqlValue("SELECT SODescription FROM outcomes WHERE ID={$outcome_id} LIMIT 1");
        $data["SODescription"] = $outcome_description;
    }
    
  4. call that function in _before_insert and _before_update like this:

    Code: Select all

    criteria_updateSODescription($data);
    
I did not test the code in detail, so there may be typos :oops:, but it should work.

Hope this helps!
Kind regards,
Jan
Hi Jan,

Thanks for this! I do appreciate and will test and revert once I have time available. I am excited to test. It will help a lot.

Speak to you later.

Danie

dklerk
Posts: 5
Joined: 2019-07-25 08:10

Re: SET Value of Parent TABLE field in Child field - SOLVED

Post by dklerk » 2019-07-27 09:34

Hello Jan,

Thank you very much for the solution. It works now and I will be able to adapt to situation or table specifics.

It is much appreciated!

Regards,

Danie

Post Reply