Page 1 of 1

problems in query to database

Posted: Wed May 08, 2019 5:22 pm
by 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

Re: problems in query to database

Posted: Wed May 08, 2019 7:20 pm
by 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:
(1.25 KiB) Downloaded 22 times

Re: problems in query to database

Posted: Thu May 09, 2019 9:46 am
by 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

Re: problems in query to database

Posted: Thu May 09, 2019 10:05 pm
by 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?

Re: problems in query to database

Posted: Fri May 10, 2019 3:16 am
by 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

Re: problems in query to database

Posted: Fri May 10, 2019 8:58 pm
by 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?

Re: problems in query to database

Posted: Sat May 11, 2019 10:52 am
by mohamad
Dear Amos, I'm deeply grateful for your kind reply
Your solution was excellent.