2. PHPRunner General questions
  3. Disable / limit lookup table values
This topic is locked

Disable / limit lookup table values

 		3/1/2014 5:51:31 PM
PHPRunner General questions
S
 Stucco author

Hi,
In my application, over time, certain lookup table values are deprecated. For example, maybe it was possible to send a package to a city, but now it is not anymore. I would like to remove that city, so that it is not accidentally selected.
If I restrict the query to remove the deprecated options, then the historic records appear as ids instead of the text value.
How are others handling this?
Sergey Kornilov admin 3/3/2014

Here is what I think you can do.
Setup 'View as' type of this field as 'Custom' and pull the data from lookup table manually.

$value=DBLookup("select DisplayField from LookupTable where LinkField=".$data["LookupFieldFromTheMainTable"]);


Basically you are performing the same SQL query but not restricting it to the actual data set.
W
 		wildwally 3/5/2014

I have run into similar situations where I have created tables that I use to populate drop down fields for my users. To maintain my historic data yet control what is still available in the future I utilize an additional field to flag active or not. This could be done with a check box using 1 or 0 or true or false, the choice is yours. Then you just add a simple the condition to your look up.
example:
Table ship_type
ID

Ship_Type_Name

Est_Days

Active
Still use the ID as the field value with user seeing the name, but filter on the Active. Then your list will still show the data correctly.
S
 		Stucco author 3/5/2014



I have run into similar situations where I have created tables that I use to populate drop down fields for my users. To maintain my historic data yet control what is still available in the future I utilize an additional field to flag active or not. This could be done with a check box using 1 or 0 or true or false, the choice is yours. Then you just add a simple the condition to your look up.
example:
Table ship_type
ID

Ship_Type_Name

Est_Days

Active
Still use the ID as the field value with user seeing the name, but filter on the Active. Then your list will still show the data correctly.


That is exactly what I do today, however when they remove an option as 'active' it then shows up as the ID in all views, instead of the value, which makes billing and historic accounting very difficult.
W
 		wildwally 3/5/2014

Can you provide a little more detail in where the data is coming from and what is looking at it?
S
 		Stucco author 3/16/2014

Say for example I have the following structure.
I'm creating orders, an order has a vendor filled by a lookup table. My tables look something like this.



CREATE TABLE IF NOT EXISTS `vendors` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) DEFAULT NULL,

`visible` tinyint(4) NOT NULL DEFAULT '1'

)
CREATE TABLE IF NOT EXISTS `orders` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`vendor_id` int(11) DEFAULT NULL

)


Right now I have the following code in the WHERE configuration of the lookup table " visible = 1".

This means that only visible options are available to pick from.

Occasionally, a vendor will no longer be available to use, and to prevent errors that have to later be reconciled I'd like it not to be pickable from the list. That is the point of the visible = 1. Unfortunately, when viewing historic records, with vendors that are no longer visible, they are displayed as an integer id instead of the textual name.
Maybe I need to do a custom "View As" to read the name directly from the database. Or I could split the add vs edit vs view screens and how they handle the lookup table.
Thanks!