2. PHPRunner General questions
  3. Using lookup wizard with WHERE issue
This topic is locked

Using lookup wizard with WHERE issue

 		10/31/2006 12:30:16 PM
PHPRunner General questions
D
 Dale author

Hopefully this will be an easy one.

Using Ver 3.0 !!
When using the lookup wizards I have come across a small problem with my app.

I have an invoice record witha field named TYPE. This is a picklist build with the lookup wizard to the inventory records.
I create an invoice, use the select to lookup the inventory item. All is good. No issues.

So I have an invoice with Red wine selected for the inventory TYPE. Save the record. No issue.
On the inventory record I have a flag for Discontinued. (checkbox)
I go back to the lookup wizard and use the where clause to use only inventory items NOT discontinued.

Then I go flag the Red wine as discontinued.
Go create a new invoice, select the TYPE and the picklist is perfect. All items that are discontinued do not show up in the list. The Red wine type is not there. Perfect.
Now the issue.

If I open the old invoice with the selected Red wine, the picklist says, please select. The Red wine was discontinued after the invoice was created so of course the current picklist has NO Red Wine in it to match what was saved earlier.
Question is, how can I alter the picklist function to show what is there, even if the value is not in the current picklist.
Of course if I wish to change the TYPE field, it makes sense to show me what is not discontinued.
This off course is the same when using the dropdown is dependent on. If the built picklist does not match the data value it shows Please Select. Put I should stay focused on just the simpler picklist using the where clause. A solution will help in a number of my record types.
If anyone can point me to a previous post or to what I could do with the picklist function to solve this issue.
T
 		thesofa 10/31/2006

I have made a few assumptions, please tell me which are wrong

1 I assume you are opening the old invoice to mark it as paid? or similar

Why not view it as a View type of record, rather than in the edit fields, then just create a different PHPR View to show the current state of the invoice using joins to show the details, without the WHERE clause added. and allow the edit function to update the feilds you want to change?
PS, I cannot count well, I have made one assumption!
D
 		Dale author 11/1/2006

Hi thesofa,
Thanks for your response.

Your one assumption is correct. We are looking at the older record to update fields. But, another reason this happens is due to the fact that a couple of my tables are imported on a weekly basis. Thus the data for this one particular field is not allways that same as in the created picklist.
We open the record and poof, "Please Select" is showing as the choice in the picklist. We need to know what is in the data before we can edit it to the appropriate picklist value.
So the question comes back to, how do I have the picklist function show the value that is currently in the database table even if it does not match a picklist value?
I suppose I will have to dig into the function myself, I was just hoping to get a "Do this, to resolve your issue response".
Thanks again for your input.
T
 		thesofa 11/1/2006

Hi thesofa,

Thanks for your response.

Your one assumption is correct. We are looking at the older record to update fields. But, another reason this happens is due to the fact that a couple of my tables are imported on a weekly basis. Thus the data for this one particular field is not allways that same as in the created picklist.
We open the record and poof, "Please Select" is showing as the choice in the picklist. We need to know what is in the data before we can edit it to the appropriate picklist value.
So the question comes back to, how do I have the picklist function show the value that is currently in the database table even if it does not match a picklist value?
I suppose I will have to dig into the function myself, I was just hoping to get a "Do this, to resolve your issue response".
Thanks again for your input.



Again I make an assumption in that you will not be storing the actual value of the selected item in the table, but just an index value, i.e. Red Wine is number 357 in the list so the table stores 357.

Is this correct, if so, it will always look for 357 in the list.

If you are actually storing "Red Wine" in the TYPE field, change the edit as type to text box.

This will bollix your Add fields, but save the project as a slightly different name and then you have to fiddle a bit, look back in the last 3 weeks posts and Alexy has answered a similar question where he made 2 projects and mixed the files from one and the other, I think it was about having a horizontal print view but a vertical print out.

HTH
Alexey admin 11/2/2006

Dale,
you coan apply some modifcations to PHPRunner files to make Lookup wizard work as you need.

Open commonfunctions.php file in c:\Program Files\PHPRunner3.1\source\include folder.

Find BuildSelectFunction there and this snippet inside it:

##if @f.m_strWhere##

$LookupSQL.="where ".##@f.m_strWhere##;

endif

Replace it with:

##if @f.m_strWhere##

$LookupSQL.="where ".##@f.m_strWhere##;

if($mode==MODE_EDIT)

$LookupSQL.=" or ##@f.strLinkField s##=".make_db_value($field,$value);

endif


We'll include this feature into the next PHPRunner update.
D
 		Dale author 11/2/2006

Thanks Alexey
Hate to flog a dead dog here, but any suggestions for the version 3.0
The project was built with a lot of mods to the templates that are not usable in the 3.1
Appreciate the help.
D
 		Dale author 11/4/2006

Bump, as they say.

Any chance of getting this to work with 3.0
Alexey admin 11/7/2006

Dale,
there is no easy way to implement this in PHPRunner 3.0
You can edit GetLookupData function in generated include\..._functions.php file to modfify WHERE clause.
D
 		Dale author 11/7/2006

Thanks Jane for the response.
I will post the mod if I can get it working.
Great support.