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.
Lookup not working in Edit Form
-
- Posts: 148
- Joined: Wed Dec 05, 2012 4:56 am
Lookup not working in Edit Form
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'.
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
I just created my first lookup fields in an edit form. As you can see below, I created lookup fields for 'Unit' and 'Violation'.
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
-
- Posts: 148
- Joined: Wed Dec 05, 2012 4:56 am
Re: Lookup not working in Edit Form
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
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
-
- Posts: 84
- Joined: Mon Jul 04, 2011 12:52 am
Re: Lookup not working in Edit Form
you could add a button object below the lookup
and add the openForm function to "On Double Click" for the button
eg.
zazzium
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
-
- Site Admin
- Posts: 2781
- Joined: Mon Jun 15, 2009 2:23 am
- nuBuilder Version: 4.5
- Been thanked: 1 time
Re: Lookup not working in Edit Form
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
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
-
- Posts: 148
- Joined: Wed Dec 05, 2012 4:56 am
Re: Lookup not working in Edit Form
Steven,
I hope these screenshots will help display the issue.
In each case I start with the browse form for my violations table. 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: If I go back to the browse form and click on the 'Add Record' button I get the edit/display form where all fields are blank. 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. 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. 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
I hope these screenshots will help display the issue.
In each case I start with the browse form for my violations table. 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: If I go back to the browse form and click on the 'Add Record' button I get the edit/display form where all fields are blank. 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. 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. 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
-
- Site Admin
- Posts: 2781
- Joined: Mon Jun 15, 2009 2:23 am
- nuBuilder Version: 4.5
- Been thanked: 1 time
Re: Lookup not working in Edit Form
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..
-hide when new record (Display)
-show when new record (Lookup)
The SQL for the Display Object will be something like this..
Something like that..
Steven
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..
-hide when new record (Display)
Code: Select all
SELECT IF('#id#' = '-1', '0','1')
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#'
Steven
-
- Posts: 148
- Joined: Wed Dec 05, 2012 4:56 am
Re: Lookup not working in Edit Form
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
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
-
- Posts: 503
- Joined: Thu May 24, 2012 2:08 am
- Location: Milan, Italy
- Contact:
Re: Lookup not working in Edit Form
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:
This is the browse screen:
If I click on "Add new record" button I get this:
If I click an existing record from browse screen I get this:
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: No special settings on the object: nuBuilder make all the job!
I hope this helps.
Max
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 lookup object: No special settings on the object: nuBuilder make all the job!
I hope this helps.
Max
-
- Posts: 148
- Joined: Wed Dec 05, 2012 4:56 am
Re: Lookup not working in Edit Form
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:
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
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#'
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