Filter from using Field Rule Lookup

[johnkoh]

Hi Colleagues,

I want to know how to create a variable for look-up filter.

I understood the basic, Look-up table field = current_table_match_field

I have the "Colors" table which has the "Series_id" and a Series_id has many colors and names.
I have the "PartAvail" and this one has the "Series_id" field.

I made a Products form with One("partgroup") to many("Products") structure. The child table(products) nor "partGroup" do not have the "series_id" field. When I enter on the Color_id field from Products table, I want to see only the color_id and name which matches with a variable(vSeries_id) but I could not able to create the variable(vSeries_id) with xBasic.

How can I call and bring the Series_id in the PartAvail table which match with current "PartGroup_id" into Products table?

I reviewed, copied and modified with "Dynamically Filtered .., http://www.learn alpha.com/filtered/filtered.htm " However, I need your help.

I attached the Sample Database for this question.

[Davidk]

Re: Filter from using Field Rule Lookup

I think this is what you want to do. I defined a global variable in the OnFetch of the form... set to the first 5 characters of the Partavail_id. This is assuming your value structure always has the color_id set as the first 5 characters of this number. If not, things can be changed to go look up the colorId from the table.

In the Table Lookup field - set a filter expression where Series Id is equal to the variable.

My drive structure is different than yours so I had to drop and add tables like color... sorry. You'll need to put them back in place for your setup.

[johnkoh]

Re: Filter from using Field Rule Lookup

David,

Thank you for your help. This method is working fine but ...

I think this method has the potential problem because If the PartAvail_id does not contain the Series_id.

Just for study about the xBasic, How do I get the Series_id which match with current form (or Table) from PartAvail table?

This is my precede code.

Base on the "Products_group" set table, using Field Rule Events.

(1) Open the Products table
When I open the current form, Get the current value of the PartAvail_id

(2) Open the PartAvail table
Find the Series_id which matched with the above Variable Field
Set the result value to vSeries_id.

and I wrote as

From Products_group set table, OnWroteField of the Group field.
dim tblg as P
dim shared vPavailId as c

tblg=table.get("partgroups")
vPavailId=tblg.partavail_id

on Products->Color_id field, OnEnterRecord Events.
dim tbla as p
dim shared vPavailId as c
dim shared vSeriesID as c
dim shared vFndSeries as c

tbla=table.open("partavail")
vFndSeries=lookup("partavail","partavail_id="+quote(vPavailId),"Series_id")
vSeriesID=vFndSeries
tbla.close()

The above script does not work. I think I missed something

[Davidk]

Re: Filter from using Field Rule Lookup

I believe this might be simpler...

In the form OnFetch...

dim shared vPavailId as c
vPavailId = :Products:partavail_id.text
varColorId = lookup("partavail", "partavail_id="+quote(vPavailId),"Series_Id")

define a variable varColorId as Global for the form. The field lookup is based on this variable so the filter in the table doesn't need to change.

[johnkoh]

Re: Filter from using Field Rule Lookup

David,

Thank you. This is a great solution and I am out of the problem.

[johnkoh]

Re: Filter from using Field Rule Lookup

Hello All,

How do you get(or address) the current field value form FieldRules Events?

The Form Events(OnFetch) is not working for my case because the script fires too late(?). My conditional look-up filter does not recognize until save a data entry.

I wrote as on Job_name field on OnWroteField FieldEvents from the Field Rules.

dim vProject as c
dim vCustomerId as c

vProject=project_id.value
' or dim t as P
vProject= t.Project_id

vCustomerId= lookup("contracts","project_id="+quote(var->vProject),"Customer_id")

I have the A5 Error messages on this scripts.

I could not get the value of Project_id field and the conditional Filter "Customer_id=vCustomerID" does not work.