Inject a list (of skills) into a result query (of employees) (e.g. for Multiselects)

Queries and Resources
,
1

Hi,

I have a normal query, which is pulling persons from a database for me. Each person has a set of skills, which are saved in another table.
I would like to display them with a person in each cell and a multiselect, which shows all of their skills. Normally I would do it (the not very performant) way to get all persons, and then get the skills for each person and add it to their object.
Or I get a union of all person and their skills and have a loop congesting them into distinct persons with their skills.

But is there a better way to do it in ReTool?
And if not or if there is: How can I do this in ReTool? Using a Transformer, I recon, but how?
Hope somebody can help... :frowning:

2

@ObiOne
You can use a JOIN on the two tables to get the skills related to each employee and then populate a temp state variable with that information and then refer to it as needed.

1 Like
3

Ok, I can partly follow, because that is how far I got myself:
I have a join like this:

SELECT e.first_name, e.last_name, st.`name`, es.`skill_level`
FROM Employees e
JOIN Employee_Skills es ON es.employee_id = e.id
JOIN Skill_Types st ON st.id = es.skill_type_id

This produces a table where each person is presented multiple times, with each their skills.

First Last Skill Level
Jenny Jolly React 1
Jenny Jolly Scrum 1
Peter Parker Business Analyse 3
Peter Parker Agile Mindset 3
Peter Parker Scrum Master 3
Peter Parker Backend Dev 2
Peter Parker Architektur 1
Peter Parker UX Design 2
Peter Parker UI Design 2
Peter Parker SQL 1
Peter Parker Java 2
Peter Parker Scrum 3

But how do I translate this into a table with a multiselect field, where I can see all the skills this person has, without showing each person mutliple times?

And the even harder question: How do I update it afterwards, if I change a skill?

4

You can query on that data set using Query JSON with SQL
for the first drop down you run select distinct but I would change your initial query to combine the first and last name into a different term like user/developer
like
select e.first_name || ' ' || e.last_name as user....

then when the first dropdown is used/selected you can then query on all the skills from the result of the initial query using Query JSON with SQL

1 Like
5

I do almost this exact same thing with contractors/services performed. Here is how I solved it using SQL Server. If you are using Postgres or something else the syntax will be different, but certainly possible

I use STRING_AGG in my query to concatenate the IDs of the services like so (I also concat the service name for easy display in the table):

select c.*, q.services_performed, q.services_performed_desc, et.employee_type
from contractors c
join (select contractor_id, STRING_AGG(sp.task_type_id, ',') as services_performed, STRING_AGG (task_type, ',') as services_performed_desc
	from contractor_service_performed sp
	join lu_task_type tt on sp.task_type_id = tt.task_type_id
	group by contractor_id) q on q.contractor_id=c.contractor_id
join lu_employee_type et on et.employee_type_id=c.employee_type_id

The result looks like this:

image
image775×198 15.5 KB

I am using a MultiSelect component to display the data:

image
image940×209 8.2 KB

The MultiSelect's data source is the lookup table (lu_task_type) and the secest is how I do the Default value:

image
image413×771 34.2 KB

I recently discovered a new trick with SQL sever that let's you pass back JSON ([how-to] Return JSON from SQL Server to cut down on server calls and improve performance) which is what I would do in the future, but STRING_AGG() works pretty well.

The next part is updating any changes and adding new records.

For that I use a a stored procedure.

First I call it like this:

image
image738×428 47.8 KB

Notice the .join(',') on @services, that makes a comma delim list of the Services Performed IDs to send back to the stored procedure.

And here is the stored procedure:

CREATE   PROCEDURE [dbo].[UpdateContractors]
           @contractor_id integer,
           @name varchar(255),
           @street varchar(255),
           @phone varchar(25),
           @city varchar(255),
           @state varchar(255),
           @zip varchar(255),
           @phone_cell varchar(255),
           @email varchar(255),
           @notes text,
           @employee_type_id int,
           @active bit,
		   @service varchar(255)
AS
BEGIN

	BEGIN TRY
		BEGIN TRANSACTION 
			UPDATE [dbo].[contractors]
				SET [name] = @name
				  ,[street] = @street
				  ,[phone] = @phone
				  ,[city] = @city
				  ,[state] = @state
				  ,[zip] = @zip
				  ,[phone_cell] = @phone_cell
				  ,[email] = @email
				  ,[notes] = @notes
				  ,[employee_type_id] = @employee_type_id
				  ,[active] = @active
			WHERE contractor_id=@contractor_id

			DELETE FROM contractor_service_performed
			WHERE contractor_id=@contractor_id

			INSERT INTO contractor_service_performed (contractor_id, task_type_id) 
			SELECT @contractor_id, value from STRING_SPLIT(@service,',')
		COMMIT
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION
        SELECT ERROR_NUMBER(), ERROR_MESSAGE();
	END CATCH
END
GO

First I update the contractors table, then I delete all related records in the contractor_service_performed table and then insert all of the new services into it. This way I don't need to worry about which were added or removed, I just start from scratch. I enclose all 3 in a transaction so relationships don't break due to an incomplete process.

You certainly could run these three queries within Retool without needing to make a stored procedure, but you lose the transaction capability.

2 Likes