Reduce a field by 1

Queries and Resources
1

I'm sure this is a really easy one (but can't find it when searching).

Can I refer to the existing field value in the Update query? So that I can subtract one from the field? Any other way of doing this?

retool subtract
retool subtract1920×907 123 KB

2

@jclutterbuck I tried doing what you have presented here.
Since the column is editable in that table, you should be able to update the database with the new edited value but I don't see where you could fire a new event to read the value other than having to write a separate js query or maybe a modal to capture the new value and then set the update query in gui mode to look at the new value in the field in the modal...

3

Thanks @ScottR

Not as simple as I was thinking it might be.

I might try the js query and see how I get on.

What I'm actually doing is adding rows to the database table, the number of times selected in the Retool table (1 is a trivial case but not so for >1). I implemented this in MS Access by adding rows WHERE NoToAdd >0 then reducing all the NoToAdd by 1 and running the Append query again until the Sum(NoToAdd)=0.

I've just thought about trying to do this in the backend (Azure)?

I'm trying to make this as efficient as possible too as many queries will slow down performance. If I use the numbers directly from the table component, it might mean I don't have to save the numbers in the database table (which I had to in Access).

Thanks

4

@jclutterbuck

Hey there :wave:

Just to clarify - are you wanting to add a recently edited value to this query (which you can grab from recordUpdates) or the current value in a selectedRow (selectedRow.data)? Or am I not understanding what you are hoping to do here :sweat_smile:

5

Thanks @lauren.gus

I am actually trying to add records to a table the number of times that has been selected in the table.

So if the table has:
ITEM | QUANTITY
Item 1 | 4
item 2 | 2
item 3 | 1
item 4 | 0

I would get:

item 1
item 1
item 1
item 1
item 2
item 2
item 3

What I used to do (in MS Access) is run a query to add items WHERE Quantity >1, then reduce the quantity by one, then repeat until all the quantities were zero.

I have thought about maybe doing this using a WHILE LOOP in the SQL but my my SQL isn't that good, don't think this is supported under the GUI.

I was hoping to do this efficiently (of course) so multiple read/write queries will be quite slow.

I wondered about doing it all from the table, maybe using JS?

Any ideas? Does this help?

Thanks

Jeremy

6

@jclutterbuck

Ah got it, thanks for breaking that down for me! You should be able to loop in SQL :slightly_smiling_face:

Here is a visual example that should work for your use case using FOREACH:

1668×1304 229 KB