I have an integration with n8n where baserow sends a webhook on table change.
I have a process that updates a product current quantity on changes of my requested items table where we track which products were given out.
The rows.deleted action only sends the row id that was deleted. I only want to update the product that was linked in the deleted row but because I only have an ID and that ID no longer returns any information when querying it I dont know which product I need to update. There is a lot of products and right now the only thing I can think of is to update every product if a row was deleted which is a lot of calls.
Is there an API call to the trashcan or something where I can get the information of the row that was deleted. Would also love a feature request to show the data of the row that was deleted instead of just the id
Unfortunately, we are currently only returning the IDs of the deleted rows. I can add this as a feature request, but I cannot guarantee if or when it will be implemented.
Would you mind sharing additional details about the structure of your tables? I believe that implementing a “Link to Table” field, along with a “formula” or a “rollup” field to tally the linked items, could potentially solve your issue. This approach would ensure that the count is automatically updated using our formula language. However, I would need a better understanding of your specific use case to determine if this solution would be a good fit.
Thanks for the reply. Would be amazing if I could do this with formulas and I am still very new to Baserow and I could use this in other places without using n8n.
I have 5 tables (I have more but going to make it a simple scenario)
- last count datetime
- last count qty
- current qty (what I update through n8n right now)
- id (auto number)
- Request Items (many to one request)
- id (auto number)
- request (link to table) (only 1 request per row)
- product (link to table) (only 1 product per row but no way to limit that yet)
- date (formula from request)
- … some more purchase related fields
- Purchase Items
- purchase (only 1)
- products (only 1)
- date (formula field)
webhook on Request Items table on create/update/delete
my n8n basically gets the products from the updated items. then looping products 1 by 1, pulls product information, pulls request items where date is greater than or equal to product last check date. Then do the same for purchase items. Lastly does the math and then updates the product table field current qty with the updated qty. Not perfect but works. Possible for some race conditions but its not a crucial field and I gave them a button to update all product quantities right before doing a new product count to get an exact current qty.
I also have an appsmith form with scanning and table for easy request creation that will create the request and then create all the request items.
The issue comes in when they can go into baserow which we want but then could delete a request item. When that happens I get a row id but since I dont know which product so I cannot run my automation to update the quantity for that product. Only option right now is to update all products which can get out of hand.
assuming you have the reverse link also in the
Products table for
Requested Items, you can use a formula like:
sum(filter(lookup('Request Items', 'Quantity'), lookup('Request Items', 'Product') = field('Name')))
In this way, we’re creating a number field that sums all the
Quantity for all the lines where the
Product matches the
Name of the current row in the Product table.
If you want, I can invite you to the workspace I’ve created for this to play around with the tables. I’ll need you to send your email (in private) in that case.
This count will be automatically updated every time something change in the
Request Items table.
Thanks for taking the time to create the formula.
The issue with this is that it includes all rows in related requests. But I need to have only fields that have a date greater than or equal to the product field.
That being said I see the filter function here and I am sure I can make it work. I dont have the reverse lookup because this can grow to very large values over time. I dont know how that will affect baserow. Do you have any insight into if this will be an issue if there are 1000’s or 100,000’s in the reverse lookup in the future?
Yes you’re right. Currently, a link table field does not scale well with thousands (or even hundreds) of relations for each row.
We have to improve on this in the near future, but, in my opinion, using reverse relationships and formulas should be the way to keep consistency between different tables without external automation that can be affected by concurrency problems, network errors, etc.