I have been using a simple search on a form to load up data from UD02.
When I apply a where clause to it and I am using the Base Search the correct reulst are returned. If I change the default search to a BAQ search and modify the where clause to reflect the returned columns names from the BAQ. The where clause is ignored.
Does anyone have any suggestions as to why this would be, and how to resolve it?
I’ve taken a search (pardon the pun) at other examples and posts about the topic here, but nothing has sprung out at me, although the post that comes up a few times from Mr Gomez ( http://trigemco.com/call-a-custom-quick-search-2/ ) appears to be broken.
@Chris_Conn, Here you go. I’ve put in comments to help explain,
I started with a simple search then populated the where with the currency code from the current record in the PO form and filtered by the inactive flag.
private void SearchOnUD02AdapterShowDialog()
{
// Wizard Generated Search Method
// You will need to call this method from another method in custom code
// For example, [Form]_Load or [Button]_Click
DataRow edvPOViewRow = edvPOHeader.CurrentDataRow;
bool recSelected;
//string whereClause = string.Empty;
string whereClause = "ShortChar02 = '" + edvPOViewRow["CurrencyCode"].ToString() + "' and CheckBox01 = false"; //This works when you don't set the quick search to default
//string whereClause = "Currency = '" + edvPOViewRow["CurrencyCode"].ToString() + "' and Inactive = false"; //Using this line and comment out above and setting quick search to default does not.
MessageBox.Show(whereClause);
DataSet dsUD02Adapter = Ice.UI.FormFunctions.SearchFunctions.listLookup(this.oTrans, "UD02Adapter", out recSelected, true, whereClause);
//everything works correctly below this line
if (recSelected)
{
DataRow adapterRow = dsUD02Adapter.Tables[0].Rows[0];
// Map Search Fields to Application Fields
EpiDataView edvUD01View = ((EpiDataView)(this.oTrans.EpiDataViews["UD01View"]));
DataRow edvUD01ViewRow = edvUD01View.CurrentDataRow;
if ((edvUD01ViewRow != null))
{
if (edvUD01ViewRow["Key4"].ToString() != string.Empty)
MessageBox.Show("You cannot replace an existing FEC, please delete then add a new one");
else
{
edvUD01ViewRow.BeginEdit();
edvUD01ViewRow["Key4"] = adapterRow["Key1"];
edvUD01ViewRow["Number01"] = adapterRow["Number01"];
edvUD01ViewRow["ShortChar01"] = adapterRow["ShortChar01"];
edvUD01ViewRow["ShortChar02"] = adapterRow["ShortChar02"];
edvUD01ViewRow["Number02"] = adapterRow["Number02"];
edvUD01ViewRow.EndEdit();
}
}
}
}
I changed the default search on the UD02 form in the Quick Search dialog. Posting the steps for completeness
The BAQ in quick search returns column Names “Currency” and “Inactive” The default search returns column names ShortChar02 and Checkbox01… Using the code in its current state (using ShortChar01 in the where clause and having the Quick Search set as default appears to ignore the where clause. Reversing the scenario i.e. using the where clause with “Currency” and not setting the Quick search as default will through an error as the it appears to parse the where clause, because the column names don’t exist in the output.
I hope that explains everything.
I think one of the posts I looked at mentioned using a named search instead. I have not tried this. I’m assuming you make a new named search from either the BAQ search or Quick Search and set that named search to default.
Ideally my end goal is to show the search popup with all the correctly labelled columns instead of the UD table column names and filter the search by currency and the inactive flag
I didn’t read the whole thread, so if I missed something, sorry.
But did you change the label in the extended properties on the UD fields? I think that should change the label on the column head for the search. That’s what I did for UD10 (and others)
Thanks for your reply @Banderson. Your post prompted me to recheck, and I did have labels setup for the fields, but my Currency Field was labelled “Foreign Currency” so I changed that to “Currency” but no change in the behavior.
Interestingly when I look at the BAQ I see the alias names of the column as UD02_Key1 etc.
This is my current where clause:
string whereClause = “Currency = '” + edvPOViewRow[“CurrencyCode”].ToString() + “’ and Inactive = false”;
When I revisit this I’ll have to look at some other way to do the search, my thought was doing it this way would allow me to manipulate the where clause to prevent the user from being able to select records that were already on the PO as well, saving some duplicate check validation.
Do you column headings in your code need to be “UD02_ShortChar02” etc. I don’t think you can just use “ShortChar02”. (or does the adapter do that for you???)
I thought I’d tried that previously, I just tried again changing the relevant fields in the where to UD02_ShortChar02 and UD02_CheckBox01. No change unfortunately. Anyway like I said when I rework this post a solution.
Just to revisit the Mechanics of what I am trying to acheive here
On the PO form the user clicks Ctrl+F or selects New FEC from the New Menu. It adds a new row to a grid which is bound to UD01, a search dialog pops up and on clicking search should show a list of records that match with the currency on the PO and are not inactive. Selecting a record updates the data in the row on the grid and allows edit functionality of the amount this PO field.
string whereClause = "ShortChar02 = '" + edvPOViewRow["CurrencyCode"].ToString() + "' and CheckBox01 = false"; //This works when you don't set the quick search to default
//string whereClause = "Currency = '" + edvPOViewRow["CurrencyCode"].ToString() + "' and Inactive = false"; //Using this line and comment out above and setting quick search to default does not.
I assume you have criteria set on the quicksearch?
I dont even think you would need a quicksearch for this. You can just pass the where clause to the normal search no?
Hmm looks like had some caching issue with a named search. I have finally got rid of that and using UD02_ etc in the where clause I am getting an error. So I am going to alter the where clause again and see what happens.
Ok so that functions correctly however the base search returns a mass of columns that don’t match with the labels setup in Extended properties. How do I get around that? The reason I wanted to use a BAQ/quick search was so I could get just the rows I needed and have the column names display correctly and if I was to use a named search I could just have the search form popup without the criteria.
