lookup field: how can I use primary id as code?

[fe.ar]

I have the following tables:

Code: Select all

hive
=======
hive_id (PK)
identifier
type
...

inspection
========
inspection_id (PK)
ins_hive_id
ins_date
...

The relation between hive and inspection is 1:n. For me the identifier is unique for each hive. There is a reason it's not primary key that has nothing to do with the question.

What I want to do is this: When the user adds an inspection, he has to select a hive. So, in the inspection form I have to make the ins_hive_id a lookup field. I don't have and I don't need a code field in the hive table because it makes no sense. The identifier is the code. It goes like this: "I inspected hive a2 at 10/9/200 and found ...".

What I did. I set the lookup fields:

Code: Select all

ID Field: hive_id
Code Field: hive_id
Description Field: identifier
Form to Lookup: frm_hive

It opens the frm_hive, I select the hive, I see the hive's id in the code part of the field and the identifier in the description part. But, when I select to save the record I get the following error:

Code: Select all

Error
An error has occurred while running this query. Please contact technical support and quote error reference: 3f5393f.

The following information is only provided for users logged on as globeadmin.
SQL

INSERT INTO `inspection` (`inspection_id`) VALUES ('1504e033f3e152')

All I need is the user to see only the hive's identifier and the hive_id to be stored in the ins_hive_id field.
Can you, please, tell me what I do wrong?

[admin]

fe.ar,

I'm not sure.

Firstly paste INSERT INTO `inspection` (`inspection_id`) VALUES ('1504e033f3e152') into phpmyadmin and see what the error says.

Steven

[massiws]

fe.ar,

when I start using nuBuilder I was in the same situation.
After several experiments I realize that nuBuilder MUST have the code field in lookup object, and this CAN'T be the ID field (nuBuilder Team, is this right? ).
So I add code fields (autoincrement) in all the table where I need a lookup object.

Fortunately users don't seem so disturbed by his presence, especially for it's autocomplete and search functions!

Hope this helps.

[fe.ar]

Firstly paste INSERT INTO `inspection` (`inspection_id`) VALUES ('1504e033f3e152') into phpmyadmin and see what the error says.

The insert in mysql gives

Code: Select all

#1452 - Cannot add or update a child row: a foreign key constraint fails (`apiary`.`inspection`, CONSTRAINT `inspection_ibfk_1` FOREIGN KEY (`ins_hive_id`) REFERENCES `hive` (`hive_id`))

fe.ar,

when I start using nuBuilder I was in the same situation.
After several experiments I realize that nuBuilder MUST have the code field in lookup object, and this CAN'T be the ID field (nuBuilder Team, is this right? ).
So I add code fields (autoincrement) in all the table where I need a lookup object.

Fortunately users don't seem so disturbed by his presence, especially for it's autocomplete and search functions!

Hope this helps.

It seems that this is the case. I added a description field (named description) in my hive table, so the code is the identifier and description is the description field. But I get the same error again. So I believe it is a design problem. Here is the create tables i use:

Code: Select all

CREATE TABLE `inspection` (
  `inspection_id` VARCHAR(15) NOT NULL,
  `ins_hive_id` VARCHAR(15) NOT NULL,
  `inspection_date` DATE NULL DEFAULT NULL,
  `ins_notes` VARCHAR(250) NULL DEFAULT NULL,
  PRIMARY KEY (`inspection_id`)
);

CREATE TABLE `hive` (
  `hive_id` VARCHAR(15) NOT NULL,
  `identifier` VARCHAR(250) NOT NULL COMMENT 'A unique identifier for each hive',
  `description` VARCHAR(1) NULL DEFAULT NULL,
  `hive_notes` VARCHAR(250) NULL DEFAULT NULL,
  PRIMARY KEY (`hive_id`)
);

ALTER TABLE `inspection` ADD FOREIGN KEY (ins_hive_id) REFERENCES `hive` (`hive_id`);

I hope the sql error and the above help us find the problem.

[fe.ar]

I think i found the problem! I deleted the relation between tables... I have to admit that I didn't expect this to cause a problem.

So, now lookup works using the hive_id as code and the identifier as description. Is there any important reason not to use a primary id as code in the lookup fields?

[massiws]

fe.ar,

you're right!
The relation in MySQL cause your error appear, so now you get no errors when insert a new record.

So, now lookup works using the hive_id as code and the identifier as description. Is there any important reason not to use a primary id as code in the lookup fields?

This could be a reason:

lookup.png

Maybe not important, but I think it's better for end user to see a numeric code inside code field.

This could be done by changing the hive_id data type in MySQL from varchar(15) to integer-autoincrement: nuBuilder support autoincrement id, even if it's discouraged (see http://wiki.nubuilder.com/tiki-index.ph ... uilderDocs).

Max.

[admin]

fe.ar,

I think it depends on how many hives you have.

If its under 30, I would use a dropdown instead of a lookup.

If you have more than this, a lookup will be best but I think you should set the Code field to "identifier" and hide the description field (you don't need to see it twice).

This way you can just type "a2" into the code field (there is no point looking it up).

Steven

[fe.ar]

This could be a reason:

Maybe not important, but I think it's better for end user to see a numeric code inside code field.

You are right, this is not nice neither meaningful for the user. I finally decided to put identifier in both code and description and to hide description.

admin, the number of hives can be 200 and more. Also, I want to make the application multiuser, so someone may have just 10 hives and someone else more than 200. Because every beekeeper knows the identifiers he uses for his hives, the purpose of the lookup is to make sure that he inserts a valid hive. Alternatively, I could use the field as text and check if the identifier is valid in the before save event. Is it possible?

[admin]

fe.ar,

the purpose of the lookup is to make sure that he inserts a valid hive.

This is a perfect reason to use a lookup.

Steven

[fe.ar]

Steven and Max, thank you for your help!