Use virtual column in search ?

[Nicolas Micoud]

Hi everyone,

I've added a virtual column on the lead window table to display the country of the address (SELECT C_Country_ID FROM C_Location WHERE C_Location_ID=AD_User.C_Location_ID)

It works perfectly.

Now, i would like to search for record using a single country (eg : France).

When i open the Find panel, i can select the new column and put 'France' in it.

When i validate my search, it return 0 record.

Is it possible to use a virtual column for searching records ?

If yes, what did i miss ?

If no, virtual column should be hidden on the Find panel.

Thanks,

Nicolas

[Nicolas Micoud]

Just checked the log and here the generated sql (didn't notice the error earlier) :


16:14:42.197-----------> AccessSqlParser.getTableInfo: More than one FROM clause - SELECT AD_User_ID,Password,Supervisor_ID,C_BPartner_ID,Processing,EMailUser,EMailUserPW,C_Greeting_ID,Birthday,C_BPartner_Location_ID,LastResult,LastContact,Title,AD_OrgTrx_ID,EMailVerify,LDAPUser,EMailVerifyDate,NotificationType,IsFullBPAccess,UserPIN,IsInPayroll,AD_User_UU,Salt,IsSalesLead,IsMenuAutoExpand,IsLocked,DateAccountLocked,FailedLoginCount,DatePasswordChanged,DateLastLogin,IsNoPasswordReset,IsExpired,SecurityQuestion,Answer,AD_Client_ID,AD_Org_ID,IsActive,Value,Name,Description,Phone,Phone2,EMail,Fax,C_Location_ID,BPName,BP_Location_ID,C_Job_ID,C_Campaign_ID,SalesRep_ID,LeadSource,LeadSourceDescription,LeadStatus,LeadStatusDescription,Comments,(##) AS X_UserCountry_ID,Created,CreatedBy,Updated,UpdatedBy FROM AD_User WHERE ((AD_User.IsSalesLead='Y') AND  (((AD_User.IsSalesLead='Y') AND (AD_User.SELECT C_Country_ID FROM C_Location WHERE C_Location_ID=AD_User.C_Location_ID)=102))) [95]

[Carlos Antonio Ruiz Gomez]

Yes nmicoud, virtual columns are searchable.

From the select you attached it seems it's broken.

For the "More than one FROM clause" I think you can simply write the
from in lowercase in your virtual column.

But it seems broken as it seems is prefixing the virtual column with the
tablename, this is invalid:
AD_User.SELECT C_Country_ID

This must be considered as a bug.

Regards,

Carlos Ruiz

[Nicolas Micoud]

Jira ticket 2207 created

Regards,

Nicolas

[norber...@multimageweb.com]

Nicolas,

i want to use Virtual Column on report as parameter. what you mean should work ?

are evaluate that  ?

norbert

This e-mail is confidential and may contain legally privileged information. It is intended only for the addressees and may not be reviewed or used in any way by other recipients. If you have received this e-mail in error, kindly notify us immediately by telephone or e-mail and delete the message and any attachments thereto from your system.

[Nicolas Micoud]

I'm not sure as virtual column are made for being used on windows.

From what I understand, you should use a default value like @SQL=SELECT...

Or could you describe the use case ?

Nicolas

[Tomáš Švikruha]

Hi Norbert,

I did some reverse engineering and find out that you can use virtual columns in report parameters, but you have to do some setup in application dictionary
 
   1. Open table AD_Process_Para and change size of column ColumnName to some bigger value (current size is 60, virtual columns in tables can have max 2000 chars so you can change it to 2000 or some number which will be enught for your selects used in virtual columns)
   2. Open table AD_PInstance_Para and change size of this column too (same reason as previous step)

Don't forget to synchronise columns !

   3. Setup virtual column in your Report table as you need (e.g. '(SELECT C_Country_ID FROM C_Location WHERE C_Location_ID=AD_User.C_Location_ID)')
   4. Now, when you are adding Report Paramter, fill ColumnName with same SQL as it is used in your virtual column - do not fill column name as usual! Because in condition has to be compared SQL with value.

Run Cache Reset only just to be sure and enjoy your virtual column in report parameter :)