Lookup not working in Edit Form

[admin]

John,
Going back to your first post, I misunderstood your problem.

I think your problem is simple, Unit and Violation don't seem to be getting saved.

Is this the case?

Steven

[JohnKlassen]

Steven,

I apologize for all of the confusion. I think I complicated the issue by referring to 2 different fields in the form, 'unit' and 'violations'. What I want to accomplish with 'unit' is the same thing I want to accomplish with 'violation', so let's concentrate on just the 'unit' example.

When I am in the 'violations' browse form, I will do one of two different things. I will either click on a record or click on the 'Add Record' button.

Option 1. If I click on a record in the browse form, I want the 'unit' number to be displayed in the edit form.

Option 2. If I click on the 'Add Record' button, I want the 'unit' field to be a lookup field where if I click on the magnifying glass, it pops up the contents of the 'unit' table and I can choose a particular unit. If I am in the edit form and click on the 'clone' button, I also want it to have a lookup for the 'unit' field.

If I define the 'unit' field in the edit form as a 'text' field, option 1 works but option 2 doesn't. If I define the 'unit' field in the edit form as a 'lookup' field, option 2 works but option 1 doesn't.

Is there an easy way to resolve this?

Thanks,

John

[massiws]

John,
I think you better start from your database schema: how many table you have to manage from Violation form? Violation, Unit and...?

Text objects and lookup objects are not the same thing and they work in different ways; you can't switch from text object to lookup object, because data stored in violation table fields aren't the same. Text object store in db the text inserted in the form text field, lookup object store in db the FK of the referenced table field.

Database tables, relation 1:n

img2.png (8.43 KiB) Viewed 6543 times

If you want a lookup object (or more) on your Violation form, to put/get data in/from Unit table, you must have:
- a Unit table in your database where you store the Unit numbers;
- a field in Violation table where to store the Unit FK.
- a reference in SQL sentence of the Violation form.

Option 1. If I click on a record in the browse form, I want the 'unit' number to be displayed in the edit form.
Option 2. If I click on the 'Add Record' button, I want the 'unit' field to be a lookup field where if I click on the magnifying glass, it pops up the contents of the 'unit' table and I can choose a particular unit. If I am in the edit form and click on the 'clone' button, I also want it to have a lookup for the 'unit' field.

This can be done configuring a lookup object like this:

Lookup object settings

img.png (15.65 KiB) Viewed 6543 times

The form SQL should be like this:

Code: Select all

SELECT * FROM violation INNER JOIN unit ON vio_unit_id = unit_id;

This work in both cases: adding and modifing records.

If you want another lookup object (Violation lookup) on the form, you must have another referenced table to store data, the same way as explained above.

Hope this helps,
Max

[JohnKlassen]

Max,

Thanks for your response. This issue is getting much more complicated than I expected.

I understand that the text field and the lookup fields work differently. (I do have a foreign key defined as 'vio_unit_id' which I can use in the 'lookup' definition). My basic question is, can I use the same 'unit' field in the edit form to display 'text' when I am in browse mode and display 'lookup' when I am in 'add record' or 'clone' mode? If the answer is 'yes', how do I do that? If the answer is 'no', do I define the 'unit' field on the form in 2 different places? In one place, it would be defined as 'text'. In another place on the form, it would be defined as 'lookup'.

My second question has to do with where to place the form SQL? Are you talking about placing the SQL in the SQL box in the 'General' tab. This form already has a drop-down list so there is already SQL in the form like this:

Form with sample SQL for dropdown list

Violations_Form_with SQL.png (20.81 KiB) Viewed 6541 times

Is this the place where I need to add the form SQL? If no, where do I need to put it? If yes, does that mean I can't have a drop down list for a different field on the same edit form as the lookup field?

Thanks again,

John

[massiws]

John,
I think you have to work around your SQL sentence in the form.

... can I use the same 'unit' field in the edit form to display 'text' when I am in browse mode and display 'lookup' when I am in 'add record' or 'clone' mode? If the answer is 'yes', how do I do that?

Yes! Lookup objects can be used to read/save values from/to fields in another table: in main table you will have a FK.
You can use lookup object for both edit and browse screen (is this what you want to get? http://forums.nubuilder.cloud/viewtopic.p ... 201#p11643).

My second question has to do with where to place the form SQL? Are you talking about placing the SQL in the SQL box in the 'General' tab.

Yes! As explained in previous my post, you must build a reference in your SQL, like this:

Code: Select all

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

If you have many Lookup (or DropDown) objects on your form, you must build a reference in SQL for each of them; copy&paste this SQL in phpmyadmin to see if you get all fields you need on your form.

In this way, your violation form is build with all the fields from SQL and you can reference each of them in browse screen (simply adding a field on browse tab) and in edit screen (adding a Lookup (or DropDown) object on the form).

Have a look at this video tutorial to learn more about lookup object: https://www.youtube.com/watch?v=Vv5-4G7CaIk

Max.

[JohnKlassen]

Max and Steven,

I finally got the lookup field working on my form. Thanks very much to both of you. I apologize for making you work so hard before I finally understood what I needed to do. I was confused and thought I needed both a lookup field and a test field and finally realized that the lookup field was the only one necessary but it had to be defined correctly. Once I defined a foreign key, defined the lookup field correctly and had the correct SQL statement, everything worked.

For those of you who have been following this issue, here is what I needed to do:

I had a violations table which had a lookup field that pointed to a unit table. I created a foreign key in the violations table that had the same value as the primary key in the unit table for the corresponding building_unit. I then populated the foreign key in the violations table using a procedure. Here is a screenshot of the unit field as a lookup field in the violations form.

Unit field in form after lookup was defined.

Unit field with lookup.png (3.47 KiB) Viewed 6537 times

I double clicked on the 'unit' label and defined it as follows:

Unit field defined

Unit field defined.png (21.62 KiB) Viewed 6537 times

I then defined the lookup field as:

Definition of lookup field

Lookup definition for unit.png (23.62 KiB) Viewed 6537 times

I then modified the violations form with the SQL as shown in this form:

Form defintion

Form defintion.png (20.86 KiB) Viewed 6537 times

Since this form also had a drop down field I had to reference both the lookup and drop down fields.

On to the next part of this project.

John

[admin]

.

[JohnKlassen]

I thought everything was working with the lookup fields so I had moved onto other parts of my project.

Today I realized that although the lookup fields work great with the use of foreign keys to tie to the primary keys in the lookup tables, when I add a record, it is not saving the actual value of the lookup field in the original table.

Here is a display of the add record:

Lookup fields

Lookup fields.png (24.36 KiB) Viewed 6500 times

When I go back to the browse screen, I see that the values for the unit and violation lookup fields are blank:

Browse screen without lookup data

Browse screen without lookup data.png (9.75 KiB) Viewed 6500 times

On the other hand, if I now click on that row in the browse screen with the missing data, it correctly displays the data that I had just entered and saved.

When I look at the actual data in the table, I see that the primary key and foreign keys are populated, but the values for the 2 lookup fields are blank or null:

Violations table with missing data

Violations table with missing data.png (7.91 KiB) Viewed 6500 times

I have viewed the lookup tutorial a number of times, but can't figure out what I am doing wrong. Here is how I define the 'All' tab for the first lookup field (unit):

Description of 'All' tab for unit lookup fields

Description of lookup field (unit).png (25.83 KiB) Viewed 6500 times

and here is how I define the lookup field in the 'Lookup' tab

Definition of lookup field for unit

Definition of lookup field for unit.png (28.05 KiB) Viewed 6500 times

.

You probably don't want to read the early entries in this post because I was very confused when I started working with Lookup fields. I had a better understanding during the last few entries.

There is another forum post called, "Form not saving data" that seems to describe the same problem but I didn't see a good explanation of what solved the problem.

Thanks,

John

[massiws]

John,
I try to point you in the right direction:
- you say you filled field's values in Violation table with a procedure: have you also filled the related fields in Unit table?
- if you click on the magnifying glass of the two lookup objects, can you open the Unit form to select a new record?

In Violation table you should have records like this:
- vio_building_unit => "150480aaaa5555"

In Unit table you should have records like this:
- unit_id => "150480aaaa5555"
- uni_building_unit => "01-101"

In lookup object > All tab you should have:
- Field Name => "vio_building_unit"

and in the Lookup tab you should have:
- ID Field => "unit_id"
- Code Field => "uni_building_unit"
- Description Field => "uni_building_unit"
- No Description => "Yes"

Are these your settings?

[JohnKlassen]

Max,

Thanks for responding to my questions. I will answer each one of your questions/comments.

First of all when I do an add record while in the violations form, I never enter any data into the unit form/table. I just read the building unit number from the unit form/table.

- you say you filled field's values in Violation table with a procedure: have you also filled the related fields in Unit table? No. I don't have a procedure to load data into the violation table. I just assumed that if I did an add record, it would populate the fields in the violations table. 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. I don't add/update any information in the unit table.

- if you click on the magnifying glass of the two lookup objects, can you open the Unit form to select a new record?. Yes. When I click on the magnifying glass, it opens up the unit form and I can select a record containing the building unit number. It then displays that building unit number in the lookup field in the violations form.

In Violation table you should have records like this:
- vio_building_unit => "150480aaaa5555" 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'.

In Unit table you should have records like this:
- unit_id => "150480aaaa5555" The value in unit_id in the unit table matches the value in vio_unit_id in the violations table.
- uni_building_unit => "01-101" Yes.

In lookup object > All tab you should have:
- Field Name => "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.

and in the Lookup tab you should have:
- ID Field => "unit_id" Yes.
- Code Field => "uni_building_unit" Yes
- Description Field => "uni_building_unit" Yes
- No Description => "Yes" No

I changed the field name in the All tab to 'vio_building_unit' and the 'No Description' to yes in the lookup tab. The following things happened:
- after adding a record, the unit field in the violation edit form is now blank instead of showing a unit number
- the new record does not show in the browse form for violations
- when I look at the violations table, the foreign key value is now in the wrong column. It should. be in vio_unit_id instead of vio_building_unit. If you look at the screen shot below, the first 2 rows were records that were just added after I made the changes to the All tab and Lookup tab. The 3rd and 4th rows came from a spreadsheet of data that was loaded into the violations table. The values for vio_unit_id and Vio_rules_id in the 3rd and 4th rows were populated as foreign keys with a procedure after the data was loaded from the spreadsheet. If you look at the far right column, 'vio_date entered', the value came from the violations form when I did the add record.

Foreign key value is in the wrong column

foreign key value in wrong column.png (23.97 KiB) Viewed 6498 times

The bottom line for me is that I think the 'lookup' fields are working fine except that they don't populate the corresponding fields in the violations table. When I click on 'Add Record', all fields in the violations table are automatically getting updated except the lookup fields.

Do I need a special procedure to update the violations table for the lookup fields?

Here is one more screen shot showing the logic for the violations form. Since the violations form not only has lookup fields but also drop-down lists, I had to use the SQL statement as seen in this form.

SQL for violations form

SQL for Violations form.png (20.99 KiB) Viewed 6498 times

I appreciate any help I can get.

John