Reduce a field by 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?

@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...

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).



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 ( Or am I not understanding what you are hoping to do here :sweat_smile:

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 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?




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: