Issue
I am new to using AppSmith and am having trouble finding examples or answers to my questions. I need to retrieve the largest number from a database column, and use that number to create a unique and sequential ID for a new member in the database. I have tried using the MAX SQL function, but need more guidance on how to incorporate it into my solution.
Resolution
You can use a JS function such as the following to find the highest number from your column:
function(){
var data = get_clientlastid.data.map(o => o.client_id);
var lastid = Math.max(…data);
var newid = lastid + 1;
return newid;
}
()However, if multiple users insert data at the same time, it can lead to race conditions - they can both get the same "next" number before the record is inserted, leading to duplicates. To avoid this, you could either let the DB handle the auto-increment (most SQL databases have built-in incrementing keys), or if you need to control the number manually, you can make your “find next id → insert id” process safe from duplicates by wrapping it inside a stored procedure with a transaction, then call the stored procedure in Appsmith.