Help with Select, IF - Update Else - Insert based on one Text field and dropdownlist

[Thore]

Hi

As a newby to ReTool and Javascript I need some help.

I use MySql to store my data and I have some tables like tblTask, tblComments and tblProducts where tblProduct has fields ProductCodes and ProductID

I have two different cases I need help with. Hope that there are anyone that can help a new beginner with this.

Case 1:
I have two tables in my db "tbltask and "tblproduct" where i whant to find the ProductID in tblproduct based on a textfield input and insert the returnd productID to tblTask.productID )

a. find the productID from tblproduct
b. insert the product ID to tblTask

What I'm thinking here:
SELECT ProductID
FROM tblProduct
WHERE ProductCode = {{Text_ProductCode.value}}
IF (ProductID >0)
Insert tblProduct.ProductID to tblTask.ProductID
ELSE
Return 'Product does not exist and can not be linked to Task'
;

I need help with how to use SELECT and then UPDATE in Retool. If somebody can help with a user guide for dummy's?

Case 2:
I have one text field "Text_ProductCode" and one picklist "Sel_Explain"

a. check if productCode is register from before else add
b. If productCode exist, check if it has the same explainID, if not update

What I'm thinking here:
SELECT CommentID, ExplainID
FROM tblComments
WHERE ProductCode = {{Text_ProductCode.value}}
IF (CommentsID >0) -- Productcode exist in the table, this will prevent duplicate rows for the same product code
[
IF (tblComment.ExplainID = {{Sel_Explain.SelectedItem.ExplainID}})
[ 'ProductCode already exist with the same explain, please reuse this.'
]
ELSE
[
Update tblcomments.explainID with {{Sel_Explain.SelectedItem.ExplainID}}
]
]
-- Insert new record
INSERT INTO tblComments (UserID, CreatedDate, ProductCode, ExplainID)
VALUES ({{UserID}}, {{moment().format('DD.MM.YYYY HH:mm:ss')}},{{Text_ProductCode.value}},{{Sel_Explain.SelectedItem.ExplainID}}) ;
;

I need help with how to use SELECT and UPDATE and Insert in Retool with IF and Else.

If somebody can help with a user guide for dummy's?

-Thore

[MiguelOrtiz]

Hi @Thore

For case 1, one way of achieving it is with 2 different queries. The first one you already have, (let's call it getProduct) i.e.

SELECT CommentID, ExplainID
FROM tblComments
WHERE ProductCode = {{Text_ProductCode.value}}

On your transform results section you can change it to an array with return formatDataAsArray (data)

You can trigger two different events on success (see the "Event Handlers" section on the bottom of your query settings).
a) The first one for the hypothesis that productID is either 0 or null by adding {{self.data[0].id <= 0}} to the "Only Run when" field. You can trigger a modal showing an error message to the user, or whatever you would like to do
2) The second one, in case of id being higher than 0, to trigger a second query.

For this second query, I would recommend using the GUI so that you can select:

• table: tbl.Task
• Key value pairs product.id = {{getProduct.data[0].id }} / any other key value pairs you need.

So in summary, you are separating your actions with different queries. The first one to find the data input which then triggers different actions based on the result. These actions could be either warning messages via a component, or another query, like an insert one.

I'm not sure I understand case 2 completely, but it seems to me that the logic is the same. You create one query to to find commentId and explain ID and from there trigger success events that match your conditions.

Hope that makes sense

[Thore]

Hi @MiguelOrtiz

As I'm new to retool can you help with example screen shot of the 1 case?

the second case is more complex.

1. First you need to check if the product code exist to determent if you need to updated the current record or if you need to create a new record.
   a. if it does not exist then create new Record
   b. if it exist then you need to check if the ExplainID is the same.

• If it is not the same, then update update.
• If it exist, present a Warning that no update, exist already.

So I will assume that I need to use Select, Update, Insert or Warning.
where you also have use "Last updated Date" for update query and "CreatedDate" if insert query

Some form of code:

Get_ProductCode: -- Checks if ProductCode exist
SELECT CommentID FROM tblComments
WHERE ProductCode = {{Text_ProductCode.value}}

Get_ProductCode_Explain: -- Checks if ProductCode has the same ExplainID
SELECT CommentID FROM tblComments
WHERE ProductCode = {{Text_ProductCode.value}}
AND ExplainID = {{Sel_Explain.SelectedItem.ExplainID}})

-- I want to prevent creating duplicate ProductCode records in by tblComments.

-- Insert if Get_productCode = 0
{{GetProductCode.data[0].CommentID === 0
?
INSERT INTO tblComments (ProductCode, ExplainID, CreatedDate, CreatedBy
VALUE ({{Text_ProductCode.value}}, {{Sel_Explain.SelectedItem.ExplainID}},{{moment().format('DD.MM.YYYY HH:mm:ss')}}, {{UserID}})
:
-- Update if Get_productCode > 0
Get_ProductCode_Explain.data[0].ExplainID === Sel_Explain.SelectedItem.ExplainID
?
'Warning: ProductCode is registered with same Explain'
:
UPDATE tblComments (ExplainID, LastUpdateDate, LastUpdateBy)
VALUE ({{Sel_Explain.SelectedItem.ExplainID}},{{moment().format('DD.MM.YYYY HH:mm:ss')}}, {{UserID}})
WHERE ProductCode = {{Text_ProductCode.value}}

As I mention I'm new and have some experience with PHP from my school days .

So if you have like example with screen shots that will help me.

Many thanks

[MiguelOrtiz]

HI @Thore,

Of course. So see below main set up which includes two queries, one to get data and one to insert.

So you can see:

1. I transform the results to an array for ease of use (you don't need to do this but I find it helpful
2. I have added two event handlers on success. On this first screenshot you can see the first one, which is configured like this:

Note that I'm refering to self and this will return all of the variables from the query's state. You can see that in this case there are no results, so I have conditioned this event to cases when length is equal to 0.

Similarly for the second event handler:

Which will trigger my insert query:

As you can see, using the GUI you can easily map your columns to any value within your app.

Does that make sense?

[Thore]

Hi @MiguelOrtiz
It works for me as you described, thank you.

Question related to the warning. When I do stuff in my App I get the Warning all the time. So it looks like Retool is running all my Get_... query's each time I do something.

Is there a way to only run query's when triggered? like selecting a tab, then it run Get_list and if I select a row then it run Get_data?

-Thore

[MiguelOrtiz]

So this happens because the query would run every time one of the variables referenced in the query changes (or you interact with the component in some way).

To change this you can go to your query settings / Run Behavior and change it to Manual:

Then you can trigger your query with a button or any other trigger event from your app.

I'm glad this is working for you!

[Thore]

Thanks for all your help.

Run Behavior is a drop down field on self hosting version I have.

-Thore

[Tess]

Hi @Thore The ui that @MiguelOrtiz has is a new update that shipped after 3.33 , but the functionality is the same