nuBuilder Forum

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

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

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

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.

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

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

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

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.

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

.