0 votes

Hello Efficy Team,

I am looking for the best way to filter a multivalue field on acc_account in a category. For exemple, in a category, the possibly to select only user from the group "sales".

We thought of three "simple" solutions :

  • In client side, with a serverscript, dynamically load the acc_account
  • Report the users in an other lookup, with their name and their k_user (I don't like this solution)
  • Edit the "sqlrule" of the multivalue field (but we struggle with the semicolon separator)

The solution provided by Kristof in the project guide about the "form in category" might do the job (because we could set a special definition for this field). Am I correct ?

Have someone another idea ?

Cheers,

Loïc

asked in Efficy/ Client side by (460 points)

1 Answer

0 votes

Hey Loic.

I worked it out and stored the code in the SVN path of the category forms manual svn/Projects/GTA/category_Forms

In the form, use a specific form definition or use the name of the field if it's unique.

macros/FormFieldsEditCustom.txt

field.F_USERS {[
    <%GetEdit('$FIELD$', category="$CATG$", 
        data-ajax-url='dialog?_macro=JsonMultiValueSales&_macrofile=MacroAjax&edithandle=<%GetEditHandle()%>&field=CATEGORY-$CATG$-$FIELD$&detail=<%GetArgument("DETAIL",context=ATTR)%>&detailkey=<%GetArgument("DETAILKEY",context=ATTR)%>',
        data-json='{<%GetField("$FIELD$", category="$CATG$", type="MULTIVALUE", json=T, context=ATTR)%>}')%>
]}

macros/MacroAjaxCustom.txt

JsonMultiValueSales {[<%RunQuery(id="salesUsers", mainquery="true", SQL="select u.K_USER as id, u.FULLNAME as text from <#TABLE NAME=ACC_ACCOUNTS> u
inner join <#TABLE NAME=ACC_GROUPS> g on u.K_USER = g.K_USER2
where u.ISACTIVE='1' and g.K_USER=:p1
order by FULLNAME", param1="%%GetUserKey(usercode='EFF_SALES')")%><%LoadServerJs("custom/functiontags/queryToJson")%>
<%RunScript(queryToJson, queryHandle="%%GetQueryHandle(query=salesUsers)", metaData="false", fields="")%>
]}

serverjs/functiontags/queryToJson.js

/**
 * Converts the dataset of a <%RunQuery()%> into a JSON string
 * <%RunScript(queryToJson, queryHandle="%%GetQueryHandle(query=SalesData)", metaData="false", fields="")%>
 */
function queryToJson() {
    var queryHandle = strToIntDef(Arguments.values("queryHandle"), 0),
        metaData = (Arguments.values("metaData").toUpperCase().charAt(0) == "F" ? false : true),
        fields = Arguments.values("fields"),
        contexts  = Arguments.values("contexts"),
        maxRecords = strToIntDef(Arguments.values("maxRecords"), 0),
        offSet = strToIntDef(Arguments.values("offSet"), 0),
        ds = Efficy.getQueryDataSet(queryHandle);

    return dataSetToJson(ds, metaData, fields, contexts, maxRecords, offSet);   
}

It uses a few new API features from Efficy 11, like the usercode argument of GetUserKey. It also uses Efficy.dataSetToJson to convert the query into JSON.

This should do exactly what you need

answered by (6.8k points)
1,167 questions
1,425 answers
1,717 comments
325 users