Skip to main content
deepaks0008
Known Participant
February 18, 2021
Solved

Foreach to save value

  • February 18, 2021
  • 3 replies
  • 0 views

In DB i have table  with columns firstnam,emailid and lastname data, I want to show in Dropdown firstanam,lastname and after selecting dropdown value want to query same table to get emailid 

Step 1: get firstname and lastname to concate and store in array for dropdown purpose

local!fn:{index(rule!PTS_QueryUsers(local!selectedDept),"firstName",{})},
local!ls:{index(rule!PTS_QueryUsers(local!selectedDept),"lastName",{})},
local!user:if(isnull(local!selectedDept),{},union(apply(fn!concatenate, merge(local!fn, local!ls)),apply(fn!concatenate, merge(local!fn, local!ls)))),

Step 2: Show firstname and lastname to concate and store in array for dropdown purpose

a!dropdownField(
label: "Assigned Name",
labelPosition: "ADJACENT",
placeHolder: "Select User",
choiceLabels:
a!forEach(
items: local!user, --> FirstNam,LastName
expression: fv!item
),
choiceValues:
a!forEach(
items: local!user, --> FirstNam,LastName
expression: fv!item
),
value: local!selectedUser, --> FirstNam,LastName
saveInto: local!selectedUser,
required: true,
disabled: isnull(local!selectedDept)
),

Step3:  Using  local!selectedUser, to split firstname and lastname to get email id from same table using QueryRule.

Can anyone suggest best way to achieve above? any doubt in my query?

    Best answer by mikes0011

    IMHO you're going about this all wrong.  You're doing 2 duplicated queries just to get the first names and the last names of the user selections; really you should do the users query *just once* and keep it in an array variable, then simply generate your dropdown ChoiceLabels based on the properties naturally contained in that array.  This is much simpler and much more flexible.

    Here is how I would re-write the above, with some of the original code included (and commented out) for comparison:

    a!localVariables(
      
      /*local!fn: {*/
        /*index(*/
          /*rule!PTS_QueryUsers(local!selectedDept),*/
          /*"firstName",*/
          /*{}*/
        /*)*/
      /*},*/
      /*local!ls: {*/
        /*index(*/
          /*rule!PTS_QueryUsers(local!selectedDept),*/
          /*"lastName",*/
          /*{}*/
        /*)*/
      /*},*/
    
      /*local!user: if(*/
        /*isnull(local!selectedDept),*/
        /*{},*/
        /*union(*/
          /*apply(*/
            /*fn!concatenate,*/
            /*merge(local!fn, local!ls)*/
          /*),*/
          /*apply(*/
            /*fn!concatenate,*/
            /*merge(local!fn, local!ls)*/
          /*)*/
        /*)*/
      /*),*/
      
      local!selectedUser: null(),
      
      local!userSelections: if(
        isnull(local!selectedDept),
        {},
        rule!PTS_QueryUsers(local!selectedDept)
      ),
      
      local!selectedUserEmail: if(
        isnull(local!selectedUser),
        null(),
        local!selectedUser.firstName & "." & local!selectedUser.lastName & "@gmail.com" /* or whatever your logic is */
      ),
      
      a!dropdownField(
        label: "Assigned Name",
        labelPosition: "ADJACENT",
        placeHolder: "Select User",
        choiceLabels: a!forEach(
          items: local!userSelections,
          expression: fv!item.firstName & " " & fv!item.lastName
        ),
        choiceValues: local!userSelections,
        value: local!selectedUser,
        saveInto: local!selectedUser,
        required: true,
        disabled: isnull(local!selectedDept)
      )
      
    )

    3 replies

    mikes0011
    mikes0011Answer
    Brainy
    February 18, 2021

    IMHO you're going about this all wrong.  You're doing 2 duplicated queries just to get the first names and the last names of the user selections; really you should do the users query *just once* and keep it in an array variable, then simply generate your dropdown ChoiceLabels based on the properties naturally contained in that array.  This is much simpler and much more flexible.

    Here is how I would re-write the above, with some of the original code included (and commented out) for comparison:

    a!localVariables(
      
      /*local!fn: {*/
        /*index(*/
          /*rule!PTS_QueryUsers(local!selectedDept),*/
          /*"firstName",*/
          /*{}*/
        /*)*/
      /*},*/
      /*local!ls: {*/
        /*index(*/
          /*rule!PTS_QueryUsers(local!selectedDept),*/
          /*"lastName",*/
          /*{}*/
        /*)*/
      /*},*/
    
      /*local!user: if(*/
        /*isnull(local!selectedDept),*/
        /*{},*/
        /*union(*/
          /*apply(*/
            /*fn!concatenate,*/
            /*merge(local!fn, local!ls)*/
          /*),*/
          /*apply(*/
            /*fn!concatenate,*/
            /*merge(local!fn, local!ls)*/
          /*)*/
        /*)*/
      /*),*/
      
      local!selectedUser: null(),
      
      local!userSelections: if(
        isnull(local!selectedDept),
        {},
        rule!PTS_QueryUsers(local!selectedDept)
      ),
      
      local!selectedUserEmail: if(
        isnull(local!selectedUser),
        null(),
        local!selectedUser.firstName & "." & local!selectedUser.lastName & "@gmail.com" /* or whatever your logic is */
      ),
      
      a!dropdownField(
        label: "Assigned Name",
        labelPosition: "ADJACENT",
        placeHolder: "Select User",
        choiceLabels: a!forEach(
          items: local!userSelections,
          expression: fv!item.firstName & " " & fv!item.lastName
        ),
        choiceValues: local!userSelections,
        value: local!selectedUser,
        saveInto: local!selectedUser,
        required: true,
        disabled: isnull(local!selectedDept)
      )
      
    )

    The Expression Guru
    deepaks0008
    Known Participant
    February 19, 2021

    Thanks a lot for reply, it worked,

    mikes0011
    Brainy
    February 19, 2021

    Great, thanks for the confirmation.

    The Expression Guru