[mohamad]

Dear Experts,
I want to query from my dynaform at the dorpdown property to get the member groups. I used the codes below in sql part of dropdown:
<membersGroup type="dropdown" required="0" readonly="0" mode="edit" btn_cancel="Cancel">
<![CDATA[SELECT U.USR_UID, U.USR_USERNAME from (USERS U left join GROUP_USER GU on U.USR_UID=GU.USR_UID) left join CONTENT C on
GU.GRP_UID=C.CON_ID where C.CON_VALUE='my group']]>
<en>Members of a particular Group</en>
</membersGroup>

Unfortunately, nothing appears in dropdown.
I was wondering if you could help me to solve it.
Kind Regards

[amosbatto]

The SQL query is correct, but you are using XML code from a classic process. That XML will only work in version 2.X or if you have imported a classic process from version 2.X into version 3.X. Also remember that the group name is case sensitive.

If you are using a BPMN process in version 3.X, then here is an example that you can use:

Select user from group.json

(1.25 KiB) Downloaded 244 times

[mohamad]

Thank you very much,
I use the code below and it works for me, however, I do not know why are there the repetitive names . I mean, in my database, there are 2 repetitions for each name!!! In example, there are 2 (105) and 2 (101).
My code is correct, because I checked phpmyadmin, and this situation was in that table!

SELECT U.USR_UID, U.USR_USERNAME FROM (USERS U LEFT JOIN GROUP_USER GU
ON U.USR_UID=GU.USR_UID) LEFT JOIN CONTENT C
ON GU.GRP_UID=C.CON_ID WHERE C.CON_VALUE='my group'
How can I remove the repetitive names? Would you please help me?
Kind Regards

[amosbatto]

What version of PM are you using?
In some versions of PM, the classic Dynaform designer executes its JavaScript twice displayed inside the designer, but this doesn't happen when running a case. Have you tried it when running a case?

[mohamad]

Dear Amosbatto,
I am using PM 3.3.4 (windows).
My code was not javascript, I used SQL. As I mentioned, this problem is in my database, too. The names are repetitive, and each name is emerged 2 times. If you check my image which is attached with this post, you can see my problem. In database's table, there are 2 USR_USERNAME and 2 USR_UID.
Yes, when I run a case, this problem is available, too.
Of course, I'll try my code in another version of PM.
Kind Regards

[amosbatto]

Try this query:
SELECT DISTINCT U.USR_UID, U.USR_USERNAME FROM (USERS U LEFT JOIN GROUP_USER GU
ON U.USR_UID=GU.USR_UID) LEFT JOIN CONTENT C
ON GU.GRP_UID=C.CON_ID WHERE C.CON_VALUE='my group' AND C.CON_CATEGORY='GRP_TITLE'
GROUP BY U.USR_UID

Does that fix it?

[mohamad]

Dear Amos, I'm deeply grateful for your kind reply
Your solution was excellent.