Filtering lookup by form datasource

(0) Share

Report

Posted on by Community Member

Hi,

Here is the situation :

I have a form with a grid populated with ProdTable as datasource. This DS has some range for different purpose. I have created a lookup to continue filtering more and more my datasource. Until now, i have no problem, this works very well.

Now in addition, I want to filter my lookup based on my form datasource value. I want the values possible in my lookup are the values possible from data in my form datasource.

Exemple :

Form datasource

Id	Value
1	A
2	B
3	C

Lookup

Value

Form datasource

Id	Value
1	A
3	C

Lookup

Value

Here is my lookup code

public void lookup()
{
    SysTableLookup          lookup;
    Query                   q = new Query();
    QueryBuildDataSource    qbds, qbds2, qbds3;
    QueryBuildRange         qbr;


    lookup = SysTableLookup::newParameters(tableNum(EcoResProduct), this);
    qbds = q.addDataSource(tableNum(EcoResProduct));
    
    qbds2 = qbds.addDataSource(tableNum(InventTable));
    qbds2.relations(true);
    qbds2.joinMode(JoinMode::InnerJoin);
    
    qbds3 = qbds2.addDataSource(tableNum(ProdTable));
    qbds3.relations(true);
    qbds3.joinMode(JoinMode::InnerJoin);
    
// QBDS3.prodId = ProdTable_DS.prodId

    qbds.addGroupByField(fieldNum(EcoResProduct, AVADiametreCirconscrit));
    lookup.addLookupField(fieldNum(EcoResProduct, AVADiametreCirconscrit));

    lookup.parmQuery(q);
    lookup.performFormLookup();
}

Can anyone help me to achieve this ?

Thank you, Stéphane M.

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (2)

Verified answer

Mea_ 60,284 on at

Like (0)

Report

RE: Filtering lookup by form datasource

It's a very strange requirement. You can get current data source query using myTable_ds.query(), copy it, group it by field you want and select all unique values. Then apply range to lookup to show only value you got on previous step.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Filtering lookup by form datasource

Thank's for fast answer :)

I know this is very strange, I said the same when my boss ask me this hahaha.

I have tried something like your solution but I just had problem for applying a query as range, how to achieve this ?

Was this reply helpful? Yes No
Verified answer

Mea_ 60,284 on at

Like (0)

Report

RE: Filtering lookup by form datasource

You do not apply query as a range, you get all distinct values from current query and them apply this values as a range, so it's a 2 step process.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
RE: Filtering lookup by form datasource

Here is my new code based on your suggestion :

public void lookup() { SysTableLookup lookup; Query q = new Query(), q2 = new Query(); QueryRun qr; QueryBuildDataSource qbds, qbds2, qbds3, qbds4; QueryBuildRange qbr; String255 qValue; ProdTable tmpProd; lookup = SysTableLookup::newParameters(tableNum(EcoResProduct), this); qbds = q.addDataSource(tableNum(EcoResProduct)); qbds2 = qbds.addDataSource(tableNum(InventTable)); qbds2.relations(true); qbds2.joinMode(JoinMode::InnerJoin); qbds3 = qbds2.addDataSource(tableNum(ProdTable)); qbds3.relations(true); qbds3.joinMode(JoinMode::InnerJoin); q2 = ProdTable_DS.query(); qr = new QueryRun(q2); while(qr.next()) { tmpProd = qr.get(tableNum(ProdTable)); if (qValue == "") { qValue = strFmt("%1", queryValue(tmpProd.ProdId)); } qValue += strFmt(",%1", queryValue(tmpProd.ProdId)); } qbr = qbds3.addRange(fieldNum(ProdTable, ProdId)); qbr.value(qValue); qbds.addGroupByField(fieldNum(EcoResProduct, AVADiametreCirconscrit)); lookup.addLookupField(fieldNum(EcoResProduct, AVADiametreCirconscrit)); lookup.parmQuery(q); lookup.performFormLookup(); }

Red text = new code
This is working Thanks, but now I need to optimize this because my lookup take something like 2-3 secondes to display values.

EDIT : Not sure if it will be possible to optimize a lot regarding to the query generated

SELECT * FROM EcoResProduct(EcoResProduct_1) JOIN * FROM InventTable(InventTable_1) ON EcoResProduct.RecId = InventTable.Product JOIN * FROM ProdTable(ProdTable_1) ON InventTable.ItemId = ProdTable.ItemId AND ((ProdId = N'OFM-0002534' OR ProdId = N'OFM-0002534' OR ProdId = N'OFM-0008418' OR .................))

Was this reply helpful? Yes No
Mea_ 60,284 on at

Like (0)

Report

RE: Filtering lookup by form datasource

So now you can follow my advice and group by field before going through all records in DS. Then you may try to insert these values into a temp table and join it instead of applying range. Another thing to try is to prepare range values outside of the lookup. Because if you have not changed data then range won't be changed, so no reason to get it each time.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Filtering lookup by form datasource

Hmm ok you just demonstrate that I still have a lot to learn hahaha

I will follow your advice, it seems good, Thank you again sir

Was this reply helpful? Yes No
EM-16011954-0 60 on at

Like (0)

Report
Filtering lookup by form datasource

I've managed to achieve something like that using Query. e.g.

SysTableLookup sysTablelookup; //create a table lookup sysTablelookup = SysTableLookup::newParameters(tableNum(SalesTable), _formStringControl); sysTablelookup.addLookupfield(fieldNum(SalesTable, SalesId)); sysTableLookup.addLookupfield(fieldNum(SalesTable, SalesName)); Query query = new Query(queryStr(SalesTableQuery)); if (fieldControlSalesAgreement.value()) { QueryBuildDataSource qbds = query.dataSourceTable(tableNum(SalesAgreementHeader)); qbds.addRange(fieldNum(SalesAgreementHeader, SalesNumberSequence)).value(fieldControlSalesAgreement.value()); } sysTablelookup.parmQuery(query); sysTablelookup.performFormLookup();

Was this reply helpful? Yes No