Lookup [Qualified Flat] type field how to use as a query condition?

[Former Member]

in the main table ,a field's type is Lookup [Qualified Flat],i need use this field as a condition when i query data,so i need demo.thank you!

[Former Member · Accepted solution]

Hi,

you can use "QualifierSearchDimension" API class to include Qualified lookup field in a query condition, i think you can only include "no qualifier" type field in search condition.

Please see the below sample code, this code only works if no qualifier is a text field.

TableSchema tableSchema = schema.getTableSchema("Main table");

Search objSearch = new Search(tableSchema.getTable().getId());

// Qualified lookup field

FieldId qualifiedLookupField = tableSchema.getFieldId(Schema.Vendors.BANK_DETAILS);

//Gets the schema of the qualified table

TableSchema qualifiedTableSchema = schema.getTableSchema("qualifiedTableName");

SearchDimension searchDimension = new QualifierSearchDimension(

qualifiedTableSchema.getFieldId("No qualifier field"),qualifiedLookupField);

//Gets the lookup value of the corresponding value

SearchConstraint searchConstraint = null;

searchConstraint = new TextSearchConstraint("value",TextSearchConstraint.EQUALS);

SearchParameter searchParameter = new SearchParameter(searchDimension, searchConstraint);

objSearch.addSearchItem(searchParameter);

ResultDefinition resultDef = new ResultDefinition(tableSchema.getTable().getId());

resultDef.setSelectFields(tableSchema.getFieldIds());

RetrieveLimitedRecordsCommand limitingCommand;

try {

limitingCommand = new RetrieveLimitedRecordsCommand(context);

limitingCommand.setResultDefinition(resultDef);

limitingCommand.setSearch(objSearch);

/* Retireve values for all langaues */

limitingCommand.execute();

} catch (SessionException e) {

} catch (ConnectionException e) {

} catch (CommandException e) {

}

if(limitingCommand.getRecords()!= null){

System.out.println("Number of records:"+limitingCommand.getRecords().getCount());

Record [] records = limitingCommand.getRecords().getRecords();

for (int i = 0; i < records.length; i++) {

System.out.println("Record ["i"]: Value "+records<i>.getDisplayValue());

Record rec = records<i>;

QualifiedLookupValue qlv = (QualifiedLookupValue)rec.getFieldValue(qualifiedLookupField);

for(int j =0 ;j< qlv.getValuesCount();j++){

System.out.println("Lookup id:"+qlv.getLookupId(j).toString());

}

}

}else{

System.out.println("no records:");

}

Thanks,

Veeru.

[Former Member]

Hi,

The above code will be helpful in searching where you have a No Qualifie as a test field. If you have a Lookup Flat as a no qualifier, then you need o change the following:

Replace searchConstraint = new TextSearchConstraint("value",TextSearchConstraint.EQUALS);

with searchConstraint = new PickListSearchConstraint("Record Id of the lookup flat record",TextSearchConstraint.EQUALS);

Hope this helps!!

Cheers

Arafat

[Former Member]

Hi Aarfat,

I do not find " PickListSearchConstraint("Record id of lookuptable", TextSearchConstraint.EQUALS)" constructor.

Instead of that ,I used below code

RecordId recordId = new RecordId("R2");

MdmValue requestValue = new LookupValue(recordId);

MdmValue[] values = ;

SearchConstraint searchConstraint = new PickListSearchConstraint(values);

But i did not get expected result, I think this search consider R2 as record id in Qualified Table not "no qualifier" field Value and given all main records which are linked to R2 qualified Record .

I would also like to know why PickListSearchConstraint is not working for "Qualifier No" field which is lookup field.

Cheers,

Veeru.

[Former Member]

Hi Veera,

In your following code, How did you get "R2" value?? Can you send me the code to get that value. I am trying to build an interface for Bulk Upload where i read values from an excel file and create records in Products table where there are lot of lookup fields. I retreive a value from the excel sheet and need to search that value in lookup table to retreive the recordId right? Can you send me that code?

RecordId recordId = new RecordId("R2");

MdmValue requestValue = new LookupValue(recordId);

MdmValue[] values = ;

Your help is really appreciated.

Thanks

Vijay