Updating other records on POST

[dan65h]

I’m trying to write a fairly complex (I think) server action that will update a field in some other records (different from the one being sent) when the user posts (update or insert) to it.

The concept is to keep the Priority field unique. If the user sets the Priority on a given record to a value that’s already in the table, I need to increment the value for all the other records.

I have it defined like this:

The first query (check_tasks) does a count of records where the id does not match and the Priority does match. The condition then checks that there is at least one record that matches. The second query (get_tasks) gets the records where the id does not match and the Priority is greater or equal to the POSTed Priority. From there, I’m trying to loop over the results and update the records that meet the criteria.

But it doesn’t work. No errors, just doesn’t do what I think it should. Is there anything obvious or some way of debugging to see where it’s breaking down?

Edit: Forgot to ask the one question. I need to increment the PriorityValue for each loop in the repeat, but can’t figure out how to do it. What is the proper way of doing that?

[Hyperbytes]

Try removing the trailing space in last set value statement, there is a space between the variable name and the braces (guessing you typed that in manually). Only obvious thing i can see

[dan65h]

Actually, I was struggling with that part. Wasn’t sure where to put the “+ 1” and may have a leftover space from something I was trying. Am I correct that I can use the Set Value function and reference a previously defined Set Value name? I’m trying to increment the value for each iteration in the repeat.

I’ve tried the dynamic data builder, but wasn’t sure what option to choose. Do I just put the “+ 1” after the variable name?:

Or, does the Global Name come into play here? Not sure what that’s for or if I did this correctly.

[sid]

This +1 thing is a Wappler bug. It has been reported earlier as well, from what I remember.

To get this working, delete everything in the value field, and type this manually:
{{PriorityValue + 1}}

[dan65h]

Thanks @sid for that info. I tried that, but still not success.

I really don’t know where this thing is failing. Is there a way to debug it step by step? Some break point debugging would be very helpful.

[sid]

What i have suggested is just the set value part. That has worked for me. Could be that you have some other issue.

For debugging, I just disable all the steps, then run the server action repeatedly by enabling steps one by one.

[dan65h]

How can you tell if a given line has been successfully ran and what the values are? For example, in the check_tasks query, how can I see the results of that query?

[sid]

@dan65h

For example, in the check_tasks query, how can I see the results of that query?

Enable Output.

[TomD]

A debugging option I find very useful is see the actual query being executed in the query log. Whether or not this is an option will depend on your set up, but in case it’s relevant, there is an an old thread about this.

(While it’s possible to see the queries within Wappler, using the terminal, as suggested in the thread, I actually prefer to use a text editor on another monitor to display the queries as they’re run.)

[dan65h]

Thanks for those suggestions. I tried enabling Output, but I couldn’t see what that did. Can you tell me how to use it, @sid?

And @TomD, I tried running those commands too, but couldn’t get any results. Where is the query log? Can you elaborate a little on how it works? It looks like a powerful debugging tool, but not sure how to use it.

[TomD]

@dan65h

Where is the query log?

It depends… I’m using MySQL witih WAMP, on a Windows machine. By default the query log is not generated; you wouldn’t want it enabled it on a production database. I added this in my.ini:

[mysqld]
; port = 3306
; enables SQL queries to be logged
general_log = on
general_log_file=C:\wamp64\logs\mysql_query.log

… and restarted the server. I think that’s all that’s needed.

[sid]

@dan65h

I tried enabling Output, but I couldn’t see what that did.

Once you enable output, just call the SA directly in the browser, and see the query result as output.
In case you don’t see anything, it means the condition you have above this query did not evaluate to true. Or, it could be that the query is not returning any rows.

[dan65h]

@sid

Once you enable output, just call the SA directly in the browser, and see the query result as output.

I’ve done that with GET requests, but this is a POST request. How do I do that in a browser?

[sid]

Oh yes. Sorry.
For POST you can use an app like Postman.
Or, you can create a form with Post method in Wappler and see the output in Network tab of browser dev tools.

[dan65h]

I’ve used Postman before so might give that a try.
But I am getting some output now in the dev tools, under network and the Preview tab.
It appears like for starters, it’s not reading the check_tasks query results correctly. I inserted a Set Value step (TaskCount) and set it’s value to the count of the query and turned it’s output on:

And this is the results I’m getting in the dev tools:

As you can see the TaskCount is zero, but if I run the query in SSMS, I get 3 rows.

How do I correctly read the results of the query?

[TomD]

I think you have Debug selected. If so, try deselecting it and try again:

[dan65h]

Yes, I turned Debug off and now getting results! Not sure what Debug is for, but one thing it seems to do is disable the query results.

[TomD]

It outputs the query itself, not the results. It doesn’t work for paged queries. Also, it will show parameters, not actual values. To show the actual query, as executed, you would need to look at the query log.

[dan65h]

I got it to update the first record in the list of records that need to be updated, but then it quits.

My api now, with the condition in question outlined in red:

It doesn’t appear to be updating the second Set Value step after the condition inside the repeat, so the condition is false for each subsequent loop.

Is there some other variable to use so that it gets updated correctly?

[dan65h]

Can someone please help me out on this? I’ve been working on this for days and am still stuck… and can’t find any documentation on the repeat action.

What is the right way to increment the value for each integration of the repeat? That seems to be where it’s failing. It updates the first record, but then doesn’t keep that value for the condition to check against the next record. It compares it with the original value instead.