Help with multiselecting and showing, Default source: {{table1.selectedRows.ID where role= 'admin'}}

App Building
,
1

Gold is to update 5 fields in Mysql table "Project" based on multiselecting in a table.

Following fields I want to update in the table "Project":

  • AdminUserID (num) - only one AdminUserID can be added
  • EditorUserID (num) - only one EditorUserID can be added
  • EditiorUserNames (char) - Many names can be added
  • ViewerUserID (num) - only one EditorUserID can be added
  • ViewerUserNames (char) - Many names can be added

If more then one Editor is selected then EditorUserID shall be 0
If more then one viewer is selected then ViewerUserID shall be 0

User have a list of projects showing in a table "table_projects".
User have a button for each projects "add users" that opens a Modal
Example of the modal::

image
image1214×458 43.2 KB

I have 3 input fields that shall show the names selected based on Role and separate users with semicolon or other symbols.
Example

image
image1035×83 2.79 KB

Update button is disabled if user select more then 1 Admin, 3 Editors and 10 Viewers.

How can I use WHERE clause in the Default value with semicolon as separator when more the one is selected?

As I'm not a developer and new to retool I do not know how to do this in ReTool, MySQL or JavaScript. I have some Experian with PHP.

Is there anyone out there that can help me solve this?

2

@Thore You can use:

SELECT *
FROM your_table
WHERE id = {{table1.selectedRows.ID}}
AND role IN ('admin', 'editor', 'viewer');
3

Hi @ZeroCodez

This will only list users with the roles. I need something with CASE and GROUP_CONCAT based on the selection done.

UPDATE project SET AdminUserID = CASE (
WHEN count({{table_users.selectedRows.Role==='Admin'}})=1
THEN {{table_users.selectedRows.ID}}
end CASE)

UPDATE project SET editorUserID = CASE (
WHEN count({{table_users.selectedRows.Role==='Editor'}}) =1
THEN {{table_users.selectedRows.ID}}
end CASE)

UPDATE project SET editorUserName = CASE
WHEN count({{table_users.selectedRows.Role==='Editor'}} IN (1,2,3)
THEN GROUP_CONCAT({{table_user.selectedRows.user}} SEPARATOR " ; ")
end CASE)

UPDATE project SET viewerUserID = CASE (
WHEN count({{table_users.selectedRows.Role==='viewer'}}) =1
THEN {{table_users.selectedRows.ID}}
end CASE)

UPDATE project SET viewerUserName = CASE
WHEN count({{table_users.selectedRows.Role==='viewer'}} (1,2,3)
THEN GROUP_CONCAT({{table_user.selectedRows.user}} SEPARATOR " ; ")
end CASE)

4

Solved in Defult value: {{list.selectedRows. where role='edit'}}?