Browse and edit in the same form

Browse and edit in the same form

Postby JohnKlassen » Mon Apr 07, 2014 1:05 pm

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.png
Project Awarded Form showing sections
Project Awarded form.png (27.84 KiB) Viewed 11424 times

Here is a copy of the SQL used by this form:
SQL for Project Awarded form 1.png
SQL for Project Awarded Form
SQL for Project Awarded form 1.png (16.55 KiB) Viewed 11424 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:
Project Awarded edit form fields.png
Fields for Project Awarded Edit Form
Project Awarded edit form fields.png (25.03 KiB) Viewed 11424 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
JohnKlassen
 
Posts: 148
Joined: Wed Dec 05, 2012 1:26 pm

Re: Browse and edit in the same form

Postby massiws » Wed Apr 09, 2014 7:38 am

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
massiws
 
Posts: 503
Joined: Thu May 24, 2012 9:38 am
Location: Milan, Italy

Re: Browse and edit in the same form

Postby JohnKlassen » Wed Apr 09, 2014 12:27 pm

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
JohnKlassen
 
Posts: 148
Joined: Wed Dec 05, 2012 1:26 pm

Re: Browse and edit in the same form

Postby massiws » Thu Apr 10, 2014 10:59 am

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.
massiws
 
Posts: 503
Joined: Thu May 24, 2012 9:38 am
Location: Milan, Italy

Re: Browse and edit in the same form

Postby JohnKlassen » Fri Apr 11, 2014 12:55 pm

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:

Project awarded SQL.png
SQL for Project Awarded form
Project awarded SQL.png (20.36 KiB) Viewed 11407 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 form showing blank venodor data.png
Project Awarded edit form with missing vendor data
Project Awarded form showing blank venodor data.png (29 KiB) Viewed 11407 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 vendor data.png
Project Awarded browse form showing data from vendor table
Project Awarded browse form showing vendor data.png (10.67 KiB) Viewed 11407 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
JohnKlassen
 
Posts: 148
Joined: Wed Dec 05, 2012 1:26 pm

Re: Browse and edit in the same form

Postby massiws » Fri Apr 11, 2014 4:31 pm

John,
in lookup -> Update Other Fields you can insert all fields you want to populate when a vendor is selected from lookup, for example:
lookup.png
Update other fields in lookup object
lookup.png (7.8 KiB) Viewed 11405 times


Max
massiws
 
Posts: 503
Joined: Thu May 24, 2012 9:38 am
Location: Milan, Italy

Re: Browse and edit in the same form

Postby JohnKlassen » Fri Apr 18, 2014 12:55 pm

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 field with update other fields.png
lookup form with update other fields
lookup field with update other fields.png (30.64 KiB) Viewed 11384 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
JohnKlassen
 
Posts: 148
Joined: Wed Dec 05, 2012 1:26 pm

Re: Browse and edit in the same form

Postby massiws » Sat Apr 19, 2014 6:33 pm

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.
massiws
 
Posts: 503
Joined: Thu May 24, 2012 9:38 am
Location: Milan, Italy

Re: Browse and edit in the same form

Postby JohnKlassen » Mon Apr 28, 2014 7:24 am

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.

Project awarded SQL.png
Correct SQL for this form.
Project awarded SQL.png (20.36 KiB) Viewed 11363 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.png
Update Other Fields
Update Other Fields.png (26.81 KiB) Viewed 11363 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.png
SQL for display field
SQL for display field.png (9.87 KiB) Viewed 11363 times


Thanks again for your help. You can close this post.

John
JohnKlassen
 
Posts: 148
Joined: Wed Dec 05, 2012 1:26 pm

Re: Browse and edit in the same form

Postby massiws » Mon Apr 28, 2014 10:43 pm

.
massiws
 
Posts: 503
Joined: Thu May 24, 2012 9:38 am
Location: Milan, Italy


Return to General