Page 1 of 1
Browse and edit in the same form
Posted: Mon Apr 07, 2014 5:35 am
by JohnKlassen
Hi,
I am trying to use browse and edit in the same edit form. I have a form with 2 lookup fields: ‘project’ and ‘vendor’. The form uses 3 tables: ‘project’, ‘vendor’ and ‘proj_vendor_crew’. After selecting the ‘project’ and ‘vendor’ from the lookup fields, I want to fill in the appropriate fields in the second and third columns. Based on the ‘vendor’ selected, I want the form to automatically populate all of the fields in the first column starting with ‘Office Phone’ from the ‘vendor’ table.
Here is a picture of the form showing the lookup fields, the fields in the first column that I want to automatically display from the vendor table and the fields in the proj_vendor_crew table that I want to enter in the second and third columns.
- Project Awarded Form showing sections
- Project Awarded form.png (27.84 KiB) Viewed 20384 times
Here is a copy of the SQL used by this form:
- SQL for Project Awarded Form
- SQL for Project Awarded form 1.png (16.55 KiB) Viewed 20384 times
Here is a copy of the fields used in the edit form showing the lookup fields, the fields I want to display in the first column and some of the fields that I want to edit in the second and third columns:
- Fields for Project Awarded Edit Form
- Project Awarded edit form fields.png (25.03 KiB) Viewed 20384 times
When I try to add a record, I select a ‘project’ and ‘vendor’ and then enter the crew information. When I click on the ‘save’ button, no data is saved.
Is it possible to display data from one table while entering data into another table while accessing the same ‘edit’ form?
Thanks,
John
Re: Browse and edit in the same form
Posted: Wed Apr 09, 2014 12:08 am
by massiws
John, with that SQL, the saved record is in projects table.
On Edit form you can display all data you need from lookup-ed or joined tables, but you can save only one record, in your case in projects table; the saved data may be link (FK) to other related tables.
Max
Re: Browse and edit in the same form
Posted: Wed Apr 09, 2014 4:57 am
by JohnKlassen
Max,
Are you saying that I only need to change the SQL to save the data in the 'proj_vendor_crew' table? If yes, what would the SQL look like? I do have foreign keys in proj_vendor_crew that point back to the primary keys in the project table and the vendor table.
I only need to save one record at a time to the proj_vendor_crew table but I still want to display fields from the vendor table for the record selected from the look-up for the vendor field.
Thanks,
John
Re: Browse and edit in the same form
Posted: Thu Apr 10, 2014 3:29 am
by massiws
John,
your SQL is
Code: Select all
SELECT * FROM projects INNER JOIN ...
so you are working on
projects table.
On save, you will save records in
projects table, but on this records you could/should have FK to link related tables.
If you want to add records on
proj_vendor_crew table, your SQL should be something like this:
Code: Select all
SELECT * FROM proj_vendor_crew INNER JOIIN projects ON ...
so you can:
- leave lookup objects on edit form to select values from vendor/projects tables
- add other fields to populate when selecting values in
lookup object -> Update Other Fields;
If you still want to save data into other tables, you can use PHP in
Custom Code -> Before Save/After Save.
Re: Browse and edit in the same form
Posted: Fri Apr 11, 2014 5:25 am
by JohnKlassen
Max,
Thanks for all of your help so far.
I am making some headway but it still isn't working quite right. I changed the table name, primary key and SQL as seen below:
- SQL for Project Awarded form
- Project awarded SQL.png (20.36 KiB) Viewed 20367 times
The lookup for 'project' and 'vendor' still work. I can now add and save data in the proj_vendor_crew table. But I still can't get it to display data from the 'vendor' table in the red box in the form as seen below.
- Project Awarded edit form with missing vendor data
- Project Awarded form showing blank venodor data.png (29 KiB) Viewed 20367 times
On the other hand, if I go back to the browse form, you will see that it does display the 'POC1 name' and 'Office Phone' from the 'vendor' table for the same record
- Project Awarded browse form showing data from vendor table
- Project Awarded browse form showing vendor data.png (10.67 KiB) Viewed 20367 times
If I use the following query in phpMyAdmin,
Code: Select all
SELECT * FROM proj_vendor_crew
INNER JOIN (vendor, projects)
ON (pvc_vend_id = vendor_id
AND pvc_proj_id = project_id)
WHERE proj_vend_crew_id = '153460cf3bb908'
it retrieves all of the data needed to populate the edit form.
I think I am missing just one step and I need your help in figuring why data from the vendor table shows up in the 'browse' form but not the 'edit' form.
Thanks,
John
Re: Browse and edit in the same form
Posted: Fri Apr 11, 2014 9:01 am
by massiws
John,
in lookup ->
Update Other Fields you can insert all fields you want to populate when a vendor is selected from lookup, for example:
- Update other fields in lookup object
- lookup.png (7.8 KiB) Viewed 20365 times
Max
Re: Browse and edit in the same form
Posted: Fri Apr 18, 2014 5:25 am
by JohnKlassen
Max,
I am missing something. I went into the edit form and clicked on the label for 'vendor' which gave me the following form:
- lookup form with update other fields
- lookup field with update other fields.png (30.64 KiB) Viewed 20344 times
In the 'Update Other Fields' portion of the form, I entered 2 fields that display content in the browse form. I entered the field name in the table which is the same field name in the form. When I go back to the browse form and click on the record to go to the edit form, these fields still do not display any content.
I looked at the video and the wiki for 'lookup' fields and am not sure what I am doing wrong.
Thanks,
John
Re: Browse and edit in the same form
Posted: Sat Apr 19, 2014 11:03 am
by massiws
John, it seems the same problem I faced some time ago: have a look at
this post.
Maybe, change the type of the object where you want to display vendor informations from
text to
display can help.
Re: Browse and edit in the same form
Posted: Sun Apr 27, 2014 11:54 pm
by JohnKlassen
Max,
Thanks for pointing me in the right direction. I was able to get a form to display the contents of a row for some fields based on what vendor I selected from a drop-down list while still updating other fields in another table.
For the benefit of any other user who may want to do this, I will describe what I did to fix the problem.
First of all, I changed the SQL, table name and primary key to point to the correct table.
- Correct SQL for this form.
- Project awarded SQL.png (20.36 KiB) Viewed 20323 times
I went to the 'Lookup' format for the vendor field and added the fields I wanted displayed in the form to the 'Update other Fields' section.
- Update Other Fields
- Update Other Fields.png (26.81 KiB) Viewed 20323 times
Finally, I chose each field where I wanted to display data and not edit it and gave it a type of 'Display'. In the 'Display' form I used SQL to get the proper data to display.
- SQL for display field
- SQL for display field.png (9.87 KiB) Viewed 20323 times
Thanks again for your help. You can close this post.
John
Re: Browse and edit in the same form
Posted: Mon Apr 28, 2014 3:13 pm
by massiws
.