Lookup not working in Edit Form

[JohnKlassen]

I have been doing some testing but have been unsuccessful in having the 'unit' lookup field on the form update the 'vio_building_unit' column in the violations table. I looked at the 'invoices' form in samplenufinancial. Other than some parameters like 'No description', 'Read only', etc., I didn't see anything that was wrong. I changed all of those minor parameters to match the sample but it didn't make any difference.

The only thing I really changed this time was to re-order the SQL for the violations form and qualify the columns with table names as follows:
SELECT * FROM violations
INNER JOIN unit
ON violations.vio_unit_id = unit.unit_id
LEFT JOIN zzsys_list
ON violations.vio_resolution = zzsys_list.sli_option
WHERE sli_name = 'resolution'

It still didn't fix the problem.

Just a reminder. All I am doing while in the 'violations' edit form is to click on the 'unit' lookup field, select a 'unit' from the 'unit' table/form and updating other fields in the 'violations' form. All of the other fields are getting updated in the 'violations' table except the lookup fields.

Thanks,

John

[massiws]

John,
- 1) your SQL had references to Unit and zzsys_list tables, but I don't see any reference to the table where you store values of the second lookup object (violations): is this true? Try to copy the SQL in phpmyadmin: can't you get all informations you need?
- 2) about lookup objects, I suggest to re-read/follow the wiki/video about them: surely, something is wrong in lookup fields settings.

...
It does populate all fields in the violation table except the 2 lookup fields. As I mentioned above, I only use the unit table to lookup information.

- 3) be sure all objects on Violation form have different values in Field Name field > All tab.

...
When I look at the new row in the violations table, the value of the foreign key is in 'vio_unit_id', not 'vio_building_unit'.
...
I had the vio_unit_id defined as the field name instead of vio_building_unit since vio_unit_id is the matching foreign key.

- 4) Is this right for you? The value of FK is stored in the field specified in Field Name > All tab of the lookup object.

...
All I am doing while in the 'violations' edit form is to click on the 'unit' lookup field, select a 'unit' from the 'unit' table/form and updating other fields in the 'violations' form. All of the other fields are getting updated in the 'violations' table except the lookup fields.

- 5) if your settings are ok, that's all you have to do!

Is this image showing the right relations?

img.png (34.83 KiB) Viewed 9863 times

if yes, you have to set in the first lookup:
- All tab > Field Name => vio_building_unit
- Lookup tab > ID and field to lookup (is this Unit table?)

and in the second lookup:
- All tab > Field Name => vio_violation
- Lookup tab > ID and field to lookup (is this Violation table?)

Hope this helps,
Max

[JohnKlassen]

Max,

I still don't have it working. This really should be a simple problem and solution. First of all, let's concentrate on just the 'unit' lookup field. If I get the 'unit' lookup to work, I can get the 'violations' lookup to work.

All I am doing is selecting a building unit from the units table/form while I am in the violations edit form. It retrieves the building unit and displays it in the lookup field on the violations edit form. I am very happy with how the lookup field works until I try to 'add a record' while in the violations form. Once again I can select a building unit from the unit table/form and it will display in the violations edit form.

Edit form showing the 2 lookup fields

Edit form showing 2 lookup fields.png (19.79 KiB) Viewed 9857 times

It is only when I go back to the browse form that I see that the unit number is blank (see below). On the other hand, if I click on the record in the browse form that I just added, it displays correctly in the edit form as in the screenshot above.

Violation form with missing data

Violations browse form with missing data.png (13.39 KiB) Viewed 9857 times

I reviewed the wiki pages for the 'All' tab and the 'lookup' tab and I don't see anything wrong with what I have entered. I have also compared my forms to the sample forms in the sample financial application and they still look ok.

I ran the SQL query in phpMyAdmin and the results look ok. It is my understanding that the SQL statement is used to retrieve data, not to update it.

As far as #5 is concerned, you are showing the right relationships between the form and the violations table. But if I change the field name in the 'All' tab to 'vio_building unit', all that happens is that the foreign key value gets stored in 'vio_building_unit' instead of 'vio_unit_id', where it belongs. The sample field name in the wiki page for 'Objects: all Tab' is 'cus_postcode_id' which would be similar to 'vio_unit_id'.

Today, I thought maybe the problem was how I was defining the 'lookup' tab but the fields match what I am reading in the wiki page on the 'lookup' tab. If I change the 'Code Field' or 'Description Field', I get errors.

Lookup tab for unit lookup field in violations form

Lookup tab for unit field.png (27.52 KiB) Viewed 9857 times

I just don't see what I am doing wrong.

John

[massiws]

...only when I go back to the browse form that I see that the unit number is blank (see below). On the other hand, if I click on the record in the browse form that I just added, it displays correctly in the edit form as in the screenshot above.

Are data correctly stored in db?
Can you post the form SQL?

[JohnKlassen]

Max,

Thanks again for your efforts to help me resolve this issue.

Here is the SQL statement from the violations form:

SQL statement for violations form

SQL statement for violations form.png (21.04 KiB) Viewed 9855 times

Here is a screenshot of the violations edit form after using 'Add Record' to enter data just before clicking on the Save button:

Data entered into the violations edit form from add record

Data entered into violations edit form.png (30.84 KiB) Viewed 9855 times

Below is a screenshot showing the first 2 rows in the violations browse form after adding the above record. As you can see on the first row, the Unit and the Violation fields are blank. (Those are the lookup fields.) On the other hand, the First Date, Notice, Information and Resolution fields display the data entered into the form above. The data in the second row is from data that was loaded from a spreadsheet. The second row properly displays the Unit and Violation data.

violations browse form after adding the record

Violations browse form after adding record.png (13.32 KiB) Viewed 9855 times

Below is a screenshot of the violation table after adding the record. It shows the top 2 rows. The top row shows the following from the record that was added above:
- violation_id - primary key for row in violation table
- vio_unit_id - foreign key which matches the primary key in the unit table for unit 01-101
- vio_rules_id - foreign key which matches the primary key in the rules table for the rule, "Annoying or Offensive Activities".
- vio_building_unit - this field is blank. It should have '01-101' in it.
- vio_violation - this field is blank. It should have "Annoying or Offensive Activities" in it.
- vio_date_entered_1 has '2013-06-02' in it which is from 'Date Entered 1' in the edit form. (The date field in the edit form is formatted differently.)
- vio_date_entered_2 has '2013-06-04' in it which is from 'Date Entered 2' in the edit form. (The date field in the edit form is formatted differently.)

The data in the second row is from data that was loaded from a spreadsheet. The second row properly displays the Unit and Violation data.

violations table after adding the record.

violations table after adding record.png (17.92 KiB) Viewed 9855 times

Let me know if you need anything else.

John

[massiws]

John, I think you misunderstand functionality of lookup objects; from wiki:

The Lookup Object is useful when you require the User to be able to select a record from another table.
When the User double clicks on the Code field of the Lookup Object, they are then taken to a Search Screen.
When they have made their selection, both the Code and Description fields are updated with information from the record they selected.

ID, Code and Description are taken from linked table to populate lookup fields, but in main table nuBuilder store only the FK.

In your image below, I see two fields related with lookups are populated (numbers 1 and 2); where do you get the values to fill vio_building_unit and vio_violation fields?

Table fields are populated

img.png (50.18 KiB) Viewed 9851 times

- vio_unit_id - foreign key which matches the primary key in the unit table for unit 01-101
- vio_rules_id - foreign key which matches the primary key in the rules table for the rule, "Annoying or Offensive Activities"

- 1) Are these the two lookups? If yes, you must have:
- 1st lookup > All tab > Field Name => vio_unit_id
- 2nd lookup > All tab > Field Name => vio_rules_id

- 2) I think you should correct SQL in the form (rules table is not present):

Code: Select all

SELECT * FROM violations INNER JOIN (unit, rules) ON
 (vio_unit_id = unit_id AND vio_rules_id = rules_id)
 LEFT JOIN ...

- vio_building_unit - this field is blank. It should have '01-101' in it.
- vio_violation - this field is blank. It should have "Annoying or Offensive Activities" in it.

- 3) As I understand it, these are the descriptions of the two lookups: is it true? If yes, you don't need them in violation table! These data must be stored in Unit and Rules table: you can get them in Browse Screen with proper SQL in the form and in Edit Screen with lookup objects.

If you want send me a zipped copy of your db, I'll have a look (massiws[at]gmail[dot]com).

Max

[JohnKlassen]

Max,

I have decided to take you up on your offer and have emailed my database to you with some additional information.

Just one quick reply to one of your questions. I knew that if I could get the 'unit' lookup fields to work properly, I could figure out how to get the 'violations' lookup field to work. To simplify things, I probably should have just changed the violations field back to a text field.

I look forward to hearing from you and getting this issue resolved.

John

[JohnKlassen]

Max,

Thanks for taking a look at my application and for the suggestions that you made.

When I first started working on the lookup fields for the violations form, I thought I needed the ‘vio_building_unit’ and ‘vio_violation’ columns. Now that I have made the changes, I understand that as long as I use the foreign keys, I can use ‘unit_building_unit’ from the ‘unit’ table and ‘rule_key phrase’ from the ‘rules’ table.

After reviewing your suggestions, I changed the following:
- I removed all references to ‘vio_building_unit’ and ‘vio_violation’.
- In the browse form, I changed ‘vio_building_unit’ to ‘unit_building_unit’ and ‘vio_violation’ to ‘rule_key_phrase’.
- I modified the SQL for the violations form to include the references to the rules table. Here is the SQL I used:

Code: Select all

    SELECT * FROM violations 
      INNER JOIN (zzsys_list, unit, rules) ON (
      sli_option = vio_resolution AND 
      vio_unit_id = unit_id AND
      vio_rules_id = rules_id)
      WHERE sli_name = 'resolution'

The lookup fields work great whether I am displaying, adding or updating a record.

Thanks again for your help.

John

[massiws]

.