Populate Grid Add with data from other tables

This public forum is for user-to-user discussions of PHPMaker. Note that this is not support forum.

Populate Grid Add with data from other tables

Postby oahmed » Sun Jan 07, 2018 3:55 pm

Detail subject: Populating identification data from other tables for Grid Add/Edit but don't save these data into the Grid Add table when inserting/updating Grid Add/View

Dear All,

I am developing a small app for students’ attendance but I am facing a problem.

I have the following tables with the following main fields
1. Schools – fields: school_code, school_name,..etc
2. Students – student_id, std_name, std_grade, std_age, school_code
3. Attendance_master
4. Attendance_details

The table ‘attendance_master’ has the following fields
Attendance_id – primary key
School_code – foreign key [primary key in table schools]
Month_of_attendance
academic_year

The tabble ‘attendance_details’ has the following fields
Attendance_id – foreign key [primary key in table ‘attendance_master’]
Student_id – fk [Primary key in table ‘students’]
days_present – integer /* total number of school days student is present */
days_absent - integer /* total number of school days student is absent */

To fill out attendance of month e.g. January for 100 students in school X, I would like to do the following
a. I have attendance data of the students in a hard copy format and copying from there to the database (Mysql)
b. To avoid redundancy, I first fill out table ‘attendance_master’ with the above mentioned fields and then use the ‘attendance_id’ to update the ‘attendance_details’ records for that school and for the month of January (Master/Detials)
c. To accomplish point (b), I need to use ‘Grid Add’ to update the ‘attendance_details’. I want the details of the 100 students in school X especially (scode, std_id, std_name, std_grade) to be pre-populated in the Grid Add and I want them to appear as labels on the Grid so that the person performing the data entry knows for which student is s/he filling for (based on the hard copy attendance) and the fields days_present and days_absent to be as text and ready for user input.

The data that I need to be updated in table ‘attendance_details’ are
Attendance_id ,
student_id,
days_present,
days_absent

but not the following fields
scode,
std_name,
std_grade

because I can use the ‘attendance_id, student_id to fetch the other relevant data using Mysql join queries but t

The issue is that table ‘attendance_details’ does not have the fields that I want to show in the Grid Add i.e. (scode, std_id, std_name, std_grade). I am avoiding to put these fields into the table ‘attendance_details’ as it creates redundancy.

My question is how can I accomplish point (c) and is there a better way to do it?

Looking forward to your help

Thanks in advance,

PS: I am using Mysql and PhpMaker 12

Regards,
Omar
oahmed
 
Posts: 7
Joined: Sat Jun 10, 2017 6:37 pm

Re: Populate Grid Add with data from other tables

Postby kirondedshem » Mon Jan 08, 2018 4:26 pm

>The issue is that table ‘attendance_details’ does not have the fields that I want to show in the Grid Add i.e. (scode, std_id, std_name, std_grade).
Since the field Student_id links to the student table that has the std_name, std_grade etc.

You can just set the Student_id in attendance detials to always use lookup to get the scode, std_id, std_name, std_grade. Such that although the database keepd the student_is, it will always display the above field instead.

-SO go to field settings of Student_id in attendance detials
-Set edit tag to text (so that it can show them in an autocomplete format).
-Tick use lookup table and set the following
#tablename = students (this means always pick from this atble)
#link field = student_id (this means match whats in the db field wicth this field when searching)
#display field 1 = std_name (set what to display to the user)
#display field 2 = std_grade
#display field 3 = school_code
HINT: ALternatively you can make a view that returns the full concatenated name from the students table and even join to toher tbales to show extra infor if you wish, then connect that to lookup setting instead
- Regenerate files

NOW you should be bale to go to the add/edit form of ored details and see the std_name,std_code etc of a particular studet record instead of thier id, and after you add / edit successfully you should also be able to see these on the list page.


>As for "I need to use ‘Grid Add’ to update the ‘attendance_details’. I want the details of the 100 students in school X especially (scode, std_id, std_name, std_grade) to be pre-populated in the Grid Add". You can autofill the student details for a particular attendance of a particular school during gridd add/ master/detail add if you have any of the two setup.

Refer to these topics
viewtopic.php?f=4&t=40867.

OR

viewtopic.php?f=4&t=40961
kirondedshem
 
Posts: 331
Joined: Sat Jan 28, 2017 7:13 pm

Re: Populate Grid Add with data from other tables

Postby oahmed » Mon Jan 08, 2018 7:37 pm

Dear Kirondedshem,

Thanks very much for your help.

I am reading it and will try it.

Will get back to you how it works

Best,
O.
oahmed
 
Posts: 7
Joined: Sat Jun 10, 2017 6:37 pm

Re: Populate Grid Add with data from other tables

Postby oahmed » Tue Jan 09, 2018 9:59 pm

Hi Kirondedshem,

Thanks a lot! The lookup option worked well for me!

I also used "Option Template" in the Edit Tag. I put the 4 Display fields with fields from a view and wrote the following in the Option template

<span class="text-info">{{:df1}}</span>
<small class="text-muted">({{:df2}})</small>
<small class="text-muted">({{:df3}})</small>
<small class="text-muted">({{:df4}})</small>

Regarding the populating the GridAdd/Edit with some data, I am looking at your previous posts to some similar topic as you responded to. I will get back to you should I face a problem.

Thanks again for your help!

Best, O.
oahmed
 
Posts: 7
Joined: Sat Jun 10, 2017 6:37 pm


Return to General Discussion (PHPMaker)