0 votes

Hi,

Is it possible to define a lookup field on an entity table (for example K_LOOKUPFIELD=10000 for COMPANIES), and to filter the results ?

On CompEdit I need to have a lookup field to choose another company :

  • The field will contain the key of the selected company
  • Only Companies with 'F_GROUPE=1' can be selected, so I need to filter
    the lookup result
  • This field can be used in QUERIES and display the name of the
    selected company (same on the CompEdit form)

I tried but I use two fields...
The first one :
FMEMBREGROUPE, used on the CompEdit form with the definition below
enter image description here

field.LOOKUP_MEMBRE_GROUP{[
    <%RunQuery(id='getGroup', store='1', sql='SELECT K_COMPANY, NAME FROM <#TABLE NAME="COMPANIES"> WHERE F_GROUPE = 1')%>

    <%GetDataGrid(query="getGroup", count=-1, TemplateText="<select name='F_MEMBRE_GROUPE' id='F_MEMBRE_GROUPE'>
        <option value='0' selected></option>
        <#repeat>
        <option value='<#F=K_COMPANY>' <%OnField('F_MEMBRE_GROUPE', value='<#F=K_COMPANY>', then='selected')%> ><#F=NAME></option>
        </#repeat>
    </select>"
    )%>
]}

The second one : V_CENTRAL, used in QUERY and view (on the right) :
enter image description here

update SYS_FIELDS set
NAME='V_CENTRALE',
DATATYPE='A',
DATALENGTH=255,
USAGE='P',
LOOKUPASSOCIATE='=select NAME from COMPANIES cent where cent.K_COMPANY=COMPANIES.F_MEMBRE_GROUPE'
where K_TABLE=10000 and K_FIELD=308

Thanks! :)

asked in Efficy/ Client side by (247 points)

1 Answer

+2 votes

Hello

Yes you can do that. Efficy standard does that for instance for OPPORTUNITIES.MANAGER.

USAGE='R'
K_LOOKUPTABLE=1000 // ACC_ACCOUNTS
K_LOOKUPFIELD=3 // FULLNAME

The select options are rendered with a macro containing a RunQuery and DataGrid, just like you tried it.

answered by (7k points)
1,193 questions
1,459 answers
1,758 comments
327 users