Lookup not working in Edit Form

[JohnKlassen]

Hi,

I just created my first lookup fields in an edit form. As you can see below, I created lookup fields for 'Unit' and 'Violation'.

Lookup_Fields_in_Add_Record.jpg

This works really well when I am using 'Add Record'. On the other hand, when I am in the 'Browse' form and click on an existing record to go to the 'Edit' form, the 'Unit' and 'Violation' fields do not display their contents.

How do I set it up so that if I click on 'Add Record', the lookup fields work but if I just click on a record in the 'Browse' form, that all of the contents in the 'Edit' form are displayed?

Thanks,

John

[admin]

John,

Check the Field names match those in mysql.

Steven

[JohnKlassen]

Steven,

Let me try to explain it better. When I am in the browse screen, I have 2 options. I can click on a record in the browse screen and display the information for just that record. The other choice is that while I am in the browse screen, I can click on the 'add record' button and get the same form but instead of displaying the record or making changes, I can create a new record. I am trying to use the same form for 'adding' a record and for just displaying the record. The difference is that when I click on 'Add Record', I want to use the lookup fields in the form but when I just click on a record in the browse screen, I don't want to see the 2 lookup fields. I just want the form to display all fields without leaving the lookup fields blank.

The problem I have is the top 2 fields are lookup fields whether I am just displaying a record or adding a record. (Lookup fields are initially blank and don't display the contents of those fields from the browse form.) Do I need to use separate forms for when I want to display a record vs adding a record? If yes, how do I specify that 'Add record' points to a different form than the regular edit form?

Thanks,

John

[zazzium]

you could add a button object below the lookup
and add the openForm function to "On Double Click" for the button

Code: Select all

openForm('the_formID_u_want_to_open','the_lookup_id_value')

eg.

Code: Select all

openForm("15068dacd778e4",$("#unit_id").val());

zazzium

[admin]

John,

I still don't quite understand, do you just want to hide some fields in one case and display them in another?

Maybe some screen grabs might help me understand.

Steven

[JohnKlassen]

Steven,

I hope these screenshots will help display the issue.

In each case I start with the browse form for my violations table.

Violations Browse Form.jpg

At this time, I have 2 choices. I can click on a row and display the edit/display form or I can click on 'Add Record' and add a record. If I click on the first row in the browse form, I get the following results:

Normal results when clicking on row in browse form.jpg

If I go back to the browse form and click on the 'Add Record' button

Violations Browse Form - Choose Add Record.jpg

I get the edit/display form where all fields are blank.

Add Record form with 'Text' fields.jpg

Up to this point, 'Unit' and 'Violation' are defined as 'Text' fields.

Now I change the attributes for the 'unit' and violation' fields to 'Lookup' fields. When I go back to 'Add Record', the form still has all blank fields but the 'unit' and 'violations' fields have magnifying glasses showing they are lookup fields.

Add Record Form with 2 Lookup Fields.jpg

Everything is still fine until I go back to the browse form and click on the first record. Since 'unit' and 'violations' are defined as lookup fields instead of 'Text' fields, you can no longer see the the contents of the first two fields.

click on browse item should not display lookup fields.jpg

Is it possible to use the same edit/display form where if you click on the 'Add Record' button, it uses the 'lookup' fields but if you just click on a record in the browse form, it uses 'text' fields for 'unit' and violation'? Or do I need to create a separate form and separate button like zazzium suggests? If I need a separate form with lookup fields, can I tie it to the 'Add Record button? If yes, how do I do that?

Thanks,

John

[admin]

John,

Sorry for asking for a second lot of shots, I forgot you had already supplied some.

This is what you need to do, for each field, create 2, one Lookup that has the same fieldname as in the table and one Display that has a different fieldname.

and create different Display conditions for each..

Capture.PNG

-hide when new record (Display)

Code: Select all

SELECT IF('#id#' = '-1', '0','1')

-show when new record (Lookup)

Code: Select all

SELECT IF('#id#' = '-1', '1','0')

The SQL for the Display Object will be something like this..

Code: Select all

SELECT CONCAT(unit, ' - ', violation) FROM violation
INNER JOIN  violation_type ON violation_type_id = vio_violation_type_id
WHERE violation_id = '#id#'

Something like that..


Steven

[JohnKlassen]

Steven,

I like your suggestion and I know what to do with the first part.

As you know by now, I am still learning PHP, MySQL and NuBuilder. I need some additional information about the last section of your post. When you say to put the SQL in the 'Display Object', are you talking about the Violations form? If so, where? I need more detail.

Could you also explain the SQL at the bottom of your post? Since there are 2 lookup fields, are you concatenating both of them in the SELECT CONCAT statement? In the INNER JOIN statement, can you explain the different variables so I know what to substitute for them?

I appreciate your help.

Thanks,

John

[massiws]

John,
I read several times your post, and I'm not sure to understood, but I have a similar situation in my project and I don't get any problem.

I have two table: client and request in relation 1:n; the relation is performed with lookup object on the request form.
This is the SQL on request form:

Code: Select all

SELECT * FROM request INNER JOIN (client, request_status) 
ON (req_cli_id = cli_id AND req_rqs_id = rqs_id) 

This is the browse screen:

browseScreen.png

If I click on "Add new record" button I get this:

EditScreenNew.png

If I click an existing record from browse screen I get this:

EditScreenEdit.png

as you can see, all data are displayed on the form, including data in lookup object, so I also may edit this value.


This is the lookup object:

lookupObj.png

No special settings on the object: nuBuilder make all the job!

I hope this helps.
Max

[JohnKlassen]

Hi,

I am finally back to this part of my project and still don't have it working. Once again, I am trying to use the same field in a form as a lookup and as text. I tried Max's suggestion and at first it looked like it worked. Then I tried to add a record and clone a record. I also had a report that uses formvalue to pick up the unit value. None of the above worked until I returned everything to a text field with the original values.

Now I am back to Steven's suggestion and I still have some questions. Here they are:

First, how do you create a text field AND a lookup field that point to the same field in the form. Don't you have to specify one OR the other when you are in the 'All' tab.

Second, I still need an explanation of the SQL that you gave me:

Code: Select all

SELECT CONCAT(unit, ' - ', violation) FROM violation
INNER JOIN  violation_type ON violation_type_id = vio_violation_type_id
WHERE violation_id = '#id#'

To simplify the situation, let's assume that I am only working with the 'building_unit' field in the form. It is 'unit_building_unit' in the 'unit' table and 'vio_building_unit' in the 'violation' table. I will extrapolate from what I learn to use for the other field in the form that I want a lookup for.

Why are you concatenating 'unit' and 'violation'?

Do I have to have a foreign key in the violations table that has the same value as the primary key for the corresponding building_unit in the violations table? If yes, I already have that working.

Assuming that the only fields I need are 'unit_id' and 'unit-building_unit' in the 'unit' table and 'violations_id', 'vio_building_unit' and possibly 'vio_unit_id' in the violations table, how should I write the above SQL statement?

I look forward to your response.

Thanks,

John