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!
SET Value of Parent TABLE field in Child field
Re: SET Value of Parent TABLE field in Child field
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.
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
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.
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
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.
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
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.
Hope this helps!
Kind regards,
Jan
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.
- Change datatype of criteria.SODescription to Text
- re-generate your application
- 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; }
- call that function in _before_insert and _before_update like this:
Code: Select all
criteria_updateSODescription($data);
Hope this helps!
Kind regards,
Jan
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<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 readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: SET Value of Parent TABLE field in Child field
Thank you very much for your efforts to help. I do appreciate it.sjohn wrote: ↑2019-07-26 07:12Okay - 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.
Regards,
Danie
Re: SET Value of Parent TABLE field in Child field
Hi Jan,jsetzer wrote: ↑2019-07-26 07:38Hi,
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.
I did not test the code in detail, so there may be typos , but it should work.
- Change datatype of criteria.SODescription to Text
- re-generate your application
- 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; }
- call that function in _before_insert and _before_update like this:
Code: Select all
criteria_updateSODescription($data);
Hope this helps!
Kind regards,
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
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
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
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 ...
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 ...
THANK YOU...
AppGini Pro 24.11 -
Calendar - Search Page Maker - Summary Reports - Mass Update - DataTalk -
bizzworxx AppGini Helper JabaScript Library - bizzworxx Inline Detail-View - bizzworxx Helper Pack - AppGini Helper Packaging Tool -
Udemy course
AppGini Pro 24.11 -
Calendar - Search Page Maker - Summary Reports - Mass Update - DataTalk -
bizzworxx AppGini Helper JabaScript Library - bizzworxx Inline Detail-View - bizzworxx Helper Pack - AppGini Helper Packaging Tool -
Udemy course
Re: SET Value of Parent TABLE field in Child field
Great! I am happy that my code could help you! Thanks for your feedback!
Kind regards,
<js />
My AppGini Blog:
https://appgini.bizzworxx.de/blog
You can help us helping you:
Please always put code fragments inside
AppGini 24.10 Revision 1579 + all AppGini Helper tools
<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 readabilityAppGini 24.10 Revision 1579 + all AppGini Helper tools
Re: SET Value of Parent TABLE field in Child field
Can someone please explain what the "&" in "&$data" in this code below is for?
TD
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
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
TD