Welcome to the nuBuilder forums!
Please register and login to view forums and other content only available to registered users.
Please register and login to view forums and other content only available to registered users.
Browse and edit in the same form
-
- Posts: 148
- Joined: Wed Dec 05, 2012 4:56 am
Browse and edit in the same form
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. Here is a copy of the SQL used by this form: 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: 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
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. Here is a copy of the SQL used by this form: 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: 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
-
- Posts: 503
- Joined: Thu May 24, 2012 2:08 am
- Location: Milan, Italy
- Contact:
Re: Browse and edit in the same form
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
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
-
- Posts: 148
- Joined: Wed Dec 05, 2012 4:56 am
Re: Browse and edit in the same form
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
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
-
- Posts: 503
- Joined: Thu May 24, 2012 2:08 am
- Location: Milan, Italy
- Contact:
Re: Browse and edit in the same form
John,
your SQL is 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: 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.
your SQL is
Code: Select all
SELECT * FROM projects INNER JOIN ...
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 ...
- 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.
-
- Posts: 148
- Joined: Wed Dec 05, 2012 4:56 am
Re: Browse and edit in the same form
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:
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.
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
If I use the following query in phpMyAdmin,
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
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:
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.
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
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'
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
-
- Posts: 503
- Joined: Thu May 24, 2012 2:08 am
- Location: Milan, Italy
- Contact:
Re: Browse and edit in the same form
John,
in lookup -> Update Other Fields you can insert all fields you want to populate when a vendor is selected from lookup, for example: Max
in lookup -> Update Other Fields you can insert all fields you want to populate when a vendor is selected from lookup, for example: Max
-
- Posts: 148
- Joined: Wed Dec 05, 2012 4:56 am
Re: Browse and edit in the same form
Max,
I am missing something. I went into the edit form and clicked on the label for 'vendor' which gave me the following form:
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
I am missing something. I went into the edit form and clicked on the label for 'vendor' which gave me the following form:
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
-
- Posts: 503
- Joined: Thu May 24, 2012 2:08 am
- Location: Milan, Italy
- Contact:
Re: Browse and edit in the same form
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.
Maybe, change the type of the object where you want to display vendor informations from text to display can help.
-
- Posts: 148
- Joined: Wed Dec 05, 2012 4:56 am
Re: Browse and edit in the same form
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.
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.
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.
Thanks again for your help. You can close this post.
John
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.
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.
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.
Thanks again for your help. You can close this post.
John
-
- Posts: 503
- Joined: Thu May 24, 2012 2:08 am
- Location: Milan, Italy
- Contact: