Hi,
I am trying to set up a lookup / list array which looksup a supplier table based on the users selection and show all the unique ranges available in that table.
I have tried using a conditional lookup i.e if 'Qil_Supplier' field says '1' use 1.dbf if '2' use table 2.dbf etc but it also needs to take out any duplicates in the lookup for the field 'range'. The lookup only needs to show one field in it, the ranges available, but as each table has approx 68,000 records in them showing all the different ranges (43 in all)
about 1600 times (not good). I need to only show each different range once and select that range to go into a single seperate field for later use.
After failing to find a way to filter out the duplicates in the lookup I then tried a UI_GET_LIST_ARRAY set up on the on arrive of a field on a form please see code below.
------------------------------------------------------------
dim number as n
dim shared response as c
dim ranges as c
dim rangesselect as c
dim LISTBOX(200) as c
dim LIST as n
dim FLAG as n
FLAG = 0
LIST = 1
tbl = table.current()
If ISBLANK("tbl.Qil_Supplier") then
ui_msg_box("ATTENTION","Please select SUPPLIER first",48)
goto end
Else
End if
table_name = UT(tbl.Qil_Supplier)
tblranges = table.open(table_name, file_ro_shared)
tblranges.fetch_first()
number = tblranges.records_get()
LISTBOX(1) = tblranges.range
FOR COUNT = 1 to number
rangesselect = tblranges.range
FOR IER = 1 to LIST
If rangesselect = LISTBOX(IER) then
FLAG = 1
End If
NEXT IER
If FLAG = 0 then
LIST = LIST + 1
LISTBOX(LIST) = rangesselect
End If
FLAG = 0
tblranges.fetch_next()
NEXT COUNT
tblranges.close()
response = ui_get_list_array("Please select a range", 1, "LISTBOX")
tbl.Qil_Range = response
tbl.Qil_Range.resynch()
if response = "" then
goto end
Else
end if
end
end:
end
------------------------------------------------------------
This does what I want except - yes you have guessed it - it takes at least 5mins to run through the table and A5 halt appears locking up the form which is not acceptable.
Help - Is there a simple method via a filtered lookup or is my script not efficient.
Many thanks for all who try.
Mike Lyon
I am trying to set up a lookup / list array which looksup a supplier table based on the users selection and show all the unique ranges available in that table.
I have tried using a conditional lookup i.e if 'Qil_Supplier' field says '1' use 1.dbf if '2' use table 2.dbf etc but it also needs to take out any duplicates in the lookup for the field 'range'. The lookup only needs to show one field in it, the ranges available, but as each table has approx 68,000 records in them showing all the different ranges (43 in all)
about 1600 times (not good). I need to only show each different range once and select that range to go into a single seperate field for later use.
After failing to find a way to filter out the duplicates in the lookup I then tried a UI_GET_LIST_ARRAY set up on the on arrive of a field on a form please see code below.
------------------------------------------------------------
dim number as n
dim shared response as c
dim ranges as c
dim rangesselect as c
dim LISTBOX(200) as c
dim LIST as n
dim FLAG as n
FLAG = 0
LIST = 1
tbl = table.current()
If ISBLANK("tbl.Qil_Supplier") then
ui_msg_box("ATTENTION","Please select SUPPLIER first",48)
goto end
Else
End if
table_name = UT(tbl.Qil_Supplier)
tblranges = table.open(table_name, file_ro_shared)
tblranges.fetch_first()
number = tblranges.records_get()
LISTBOX(1) = tblranges.range
FOR COUNT = 1 to number
rangesselect = tblranges.range
FOR IER = 1 to LIST
If rangesselect = LISTBOX(IER) then
FLAG = 1
End If
NEXT IER
If FLAG = 0 then
LIST = LIST + 1
LISTBOX(LIST) = rangesselect
End If
FLAG = 0
tblranges.fetch_next()
NEXT COUNT
tblranges.close()
response = ui_get_list_array("Please select a range", 1, "LISTBOX")
tbl.Qil_Range = response
tbl.Qil_Range.resynch()
if response = "" then
goto end
Else
end if
end
end:
end
------------------------------------------------------------
This does what I want except - yes you have guessed it - it takes at least 5mins to run through the table and A5 halt appears locking up the form which is not acceptable.
Help - Is there a simple method via a filtered lookup or is my script not efficient.
Many thanks for all who try.
Mike Lyon
Comment