Foreign Key based on SQL and Concatenated Field

Talk about foreign keys

Moderators: ne_moj, zewa, vadimako, ne_moj, zewa, vadimako

dynsight
Expert
Expert
Posts: 99
Joined: Mar 3rd, '11, 03:05

Foreign Key based on SQL and Concatenated Field

Postby dynsight » May 31st, '12, 02:12

I have a table with 3 fields itemID, itemnumber, itemdescription

Now, I want to use this as a foreign key source when adding or editing a record... however I want to use the data like this:

Code: Select all

SELECT tbl_item.item_id as iValue,
   concat(tbl_item.itemnumber,"-",tbl_item.description) as description
FROM tbl_item
order by tbl_item.itemnumber asc


Thus my array would be

ivalue=>description

for the entire table. Unfortunately, it does not seem that can base a foreign key field on this concatenated field..

I also tried using an enum field, and using the source as the database array, but every time I tried, it crashed.

Ideally the drop down result would be:

Suggestions?

administrator
Site Admin
Site Admin
Posts: 5992
Joined: Jan 7th, '09, 23:18
Contact:

Re: Foreign Key based on SQL and Concatenated Field

Postby administrator » May 31st, '12, 07:42

Use "enum" field with 'source'=>$your_array

$your_array - may be any array you want to create in following format:
$your_array = array(key1=>value1, key2=>value2)


Return to “Field type: "foreign key"”