Page 1 of 1

SET Value of Parent TABLE field in Child field

Posted: 2019-07-25 08:37
by dklerk
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!

Re: SET Value of Parent TABLE field in Child field

Posted: 2019-07-25 15:26
by sjohn
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.

Re: SET Value of Parent TABLE field in Child field

Posted: 2019-07-25 18:35
by dklerk
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.

Re: SET Value of Parent TABLE field in Child field

Posted: 2019-07-26 07:12
by sjohn
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.

Re: SET Value of Parent TABLE field in Child field

Posted: 2019-07-26 07:38
by jsetzer
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

Re: SET Value of Parent TABLE field in Child field

Posted: 2019-07-26 15:36
by dklerk
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

Re: SET Value of Parent TABLE field in Child field

Posted: 2019-07-26 15:38
by dklerk
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

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

Posted: 2019-07-27 09:34
by dklerk
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

Re: SET Value of Parent TABLE field in Child field

Posted: 2020-12-03 16:35
by mohamed
Dear jsetzer,

Just for the record, THANK YOU VERY MUCH ;)

you have just saved me hours of testings and workarounds by using your code submitted in this thread.

Thanks again ...

Re: SET Value of Parent TABLE field in Child field

Posted: 2020-12-03 17:01
by jsetzer
Great! I am happy that my code could help you! Thanks for your feedback!

Re: SET Value of Parent TABLE field in Child field

Posted: 2023-05-31 16:49
by dlee
Can someone please explain what the "&" in "&$data" in this code below is for?
TD

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;
}

Re: SET Value of Parent TABLE field in Child field

Posted: 2023-05-31 17:24
by dlee
Google is your friend .. "The & states that a reference to the variable should be passed into the function rather than a clone of it."
TD