Need your help with Risk Management Application questions regarding SQL Statements and Parameters

Hi all,

I have started using Wappler a few weeks ago and I am quite new to programming. My aim is to develop a quality management application and I have started with the area of risk management. For this I have developed a database structure with risks, each having some master data like name, possible impact (text) and chances, etc.

I also have created tables for risk scores as risks have to be evaluated over time and impact and probability can change. I was able to develop an overview page with search, deleted, add functionality, and a detailed page for each risk, reached by a link providing the risk id via URL parameter.

At the risk details page, I use the query manager to show the details for each risk as well as the scores.


My first question here is how I can create the INSERT SQL Statement when adding a new risk score that the risk score is calculated automatically into the column risk_score by simple multiplication of the columns risk_probability and risk_impact (e.g. first line 3 x 4 = 12)

I then also want to assign different tasks to a risk to perform risk mitigation.


I have an extra table assigning a task to a risk as my plan is to use tasks also later in the application for other areas, not only risks.
riskmitigation
I was able to create an insert table to add new tasks but I am not sure how I can create a query that creates a new task and at the same time assigns the new task to the correct riskid in the risk_mitigation table using the URL parameter (in the example risk id=7) so that the task shows up in the correct risk as mitigation.
I would appreciate any tips or links to resources or feedback in general. So far I have read a lot of the documentation, watched how-to videos and searched in the forum. It helped me a lot and I was able to solve many other problems this way. I am also a heavy user of LinkedIn Learning for videos on databases and SQL.

Have a good Weekend!

Firstly must say well done for getting this far with no need to post a question here in the Community! Everything you have done looks great, seriously well done @struppihh

Lets see if can help you out a little (I’m in and out of the office so excuse delayed replies my side) .

This post will help explain how to sum. You could then use a hidden input within your form to populate it (set its value with the sum) with the result, then post this hidden input to your database. Below are the methods you can use and links to their functions.

* (Multiplication)

+ (Addition)

- (Subtraction)

/ (Division)

You would be far better off using hidden inputs/variables to do this, and populate them with an onclick dynamic event or such, with the result data obtained from the query. Then post these inputs as normal within your form to insert to the database.

Or alternatively if you want to pass between pages check this post out for some ideas on how to do so.

Hope that helps @struppihh?

2 Likes

@Dave Thank you so much for your quick reply. Your first part of the answer helped me very much and I now can insert the correctly calculated risk score into my database.

Regarding the second question, I will have a look at the information you provided and see if I can manage to solve the problem.

1 Like

@Dave, I made some progress with the resources you recommended. I modified the query that creates the task to pass back the value task_id of the newly created task.

After submitting the form, I now have two text input fields (which I later want to hide, only visible for testing purposes) that show the correct risk_id and task_id, which should be entered into my table risk_mitigaiton assign the task to the corresponding risk.

I have added these to fields in a separate form for testing. I have created a separate server action risks_create_mitigation with a simple insert data query that successfully inserts the two values into the table risk_mitigation when pressing the button.

However, I am searching for a way to submit the second form correctly and automatically when the first form is submitted. So far, I was not successful. I have tried to add a dynamic event when clicking on the first submit button. Selecting to submit the second form. But it did not work. I think the problem might be that the first form has to be submitted to get the new task_id and there might be some overlap. I have also tried to work around this problem with a workflow but could not find a solution that worked. Does anyone have an approach or idea what I could try or maybe have done wrong?

Have you made the second form a Server Connect Form (by clicking Make Server Connect Form while the form is selected) and assigning an Action @struppihh?

@Dave Yes, and if I press submit it works with the second Insert query as can be seen in the result screenshot. Task 27 has been created and successfully assigned to risk 7. It just that this second step of submitting the new form, assigning the newly created task to the current risk_id should work automatically without any user action and that I could hide the input fields so for the user it would be just the one click on the submit button when inserting the new task.

Try adding a Dynamic Action (to the first form) / Server Connect / Success, scroll down to your second form and select Submit… See if that works? You’ll get there in the end, from there on out you will always know how to do it, and will become second nature.

:slight_smile:

Also worth giving the user some form of acknowledgement of their action success with either a nice Toast or Notification. These can be added by simply adding the required Component to the page, and then in the same Success event selecting it to display your chosen acknowledgement.

1 Like

How did you get on @struppihh?

@Dave only can work at nights after work on this, but thanks for checking in. I tried your hint and played around a bit. I came up now with this solution that seems to work well. Thank you so much for your support.

On the first form that adds the task, I added a dynamic event on success with the following actions.

  1. set the value of my text field “task” in the second form to the value output task_id generated by the query which inserts the task into the database. The link on how to pass values between pages helped me here Post

  1. I will then submit my second form which inserts the risk_id and task_id into the second table. The risk_id is provided in a second text field from the query manager. I hide both fields so that they do not show up on the page.

  2. During testing I found out that it would be best to reset both forms afterward to ensure a new mitigation action can be added by the user. (in my first tries the last added data did show up in the form when I wanted to add a second mitigation action)

  3. I then reload my query that selects the tasks assigned to the currently displayed risk and (4.) close my modal which is use to enter the new mitigation action.

  4. I provide a success notification to the customer that the new mitigation action was successfully added.

success

2 Likes

That’s fantastic @struppihh. You have great initiative. Really good work. Very happy you can get on with your next task and put this behind you with a new understanding of what can be done using the Dynamic Events within Wappler. Always feel free to ask further questions if you need to do so. Am happy to have helped. And thank you for documenting exactly what you have done, will go a long way to help others looking to do similar things. Appreciated!

Best of luck with your Project.

:slight_smile:

2 Likes

Great work by all here, I just wanted to pitch in and let all parties know that this was a great help for something I’m busy with that is loosely similar - saved me a heap of time. I look forward to the day I can pay it forward with my own advice to others…

2 Likes