Welcome to the nuBuilder forums!

Please register and login to view forums and other content only available to registered users.

Browse and edit in the same form

Locked
JohnKlassen
Posts: 148
Joined: Wed Dec 05, 2012 4:56 am

Browse and edit in the same form

Unread post 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 showing sections
Project Awarded form.png (27.84 KiB) Viewed 19882 times
Here is a copy of the SQL used by this form:
SQL for Project Awarded Form
SQL for Project Awarded Form
SQL for Project Awarded form 1.png (16.55 KiB) Viewed 19882 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
Fields for Project Awarded Edit Form
Project Awarded edit form fields.png (25.03 KiB) Viewed 19882 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
massiws
Posts: 503
Joined: Thu May 24, 2012 2:08 am
Location: Milan, Italy
Contact:

Re: Browse and edit in the same form

Unread post 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
JohnKlassen
Posts: 148
Joined: Wed Dec 05, 2012 4:56 am

Re: Browse and edit in the same form

Unread post 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
massiws
Posts: 503
Joined: Thu May 24, 2012 2:08 am
Location: Milan, Italy
Contact:

Re: Browse and edit in the same form

Unread post 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.
JohnKlassen
Posts: 148
Joined: Wed Dec 05, 2012 4:56 am

Re: Browse and edit in the same form

Unread post 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
SQL for Project Awarded form
Project awarded SQL.png (20.36 KiB) Viewed 19865 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 edit form with missing vendor data
Project Awarded form showing blank venodor data.png (29 KiB) Viewed 19865 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 data from vendor table
Project Awarded browse form showing vendor data.png (10.67 KiB) Viewed 19865 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
massiws
Posts: 503
Joined: Thu May 24, 2012 2:08 am
Location: Milan, Italy
Contact:

Re: Browse and edit in the same form

Unread post 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
Update other fields in lookup object
lookup.png (7.8 KiB) Viewed 19863 times
Max
JohnKlassen
Posts: 148
Joined: Wed Dec 05, 2012 4:56 am

Re: Browse and edit in the same form

Unread post 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 form with update other fields
lookup field with update other fields.png (30.64 KiB) Viewed 19842 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
massiws
Posts: 503
Joined: Thu May 24, 2012 2:08 am
Location: Milan, Italy
Contact:

Re: Browse and edit in the same form

Unread post 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.
JohnKlassen
Posts: 148
Joined: Wed Dec 05, 2012 4:56 am

Re: Browse and edit in the same form

Unread post 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.
Correct SQL for this form.
Project awarded SQL.png (20.36 KiB) Viewed 19821 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
Update Other Fields.png (26.81 KiB) Viewed 19821 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
SQL for display field.png (9.87 KiB) Viewed 19821 times
Thanks again for your help. You can close this post.

John
massiws
Posts: 503
Joined: Thu May 24, 2012 2:08 am
Location: Milan, Italy
Contact:

Re: Browse and edit in the same form

Unread post by massiws »

.
Locked