I need to pull the highest "ID" number from SQL

Question

So I have a SQL table with an ID number, I need Appian to pull the highest number and add 1 to that. 
 
 Thank you

Mike Schmitt · Accepted Answer

Just to clarify, unless you've changed your a!writeToDataStoreEntity call since my earlier comment, it will still fail since you are not providing it with the required dataStoreEntity parameter. 
 
 Also I just noticed you're passing it a parameter named "value s ToStore", but the actual property is the singular, "valueToStore" (no "s" in "value").

Mike Schmitt · Answer

Usually for this I'd just do a standard Query Entity, using a PagingInfo configuration with startIndex and batchSize both set to 1, and a sort configuration set to sort by your ID, Descending... then grab the ID in question from the result. To slightly further streamline the query, you can set it to retrieve ONLY the ID column in the query result, useful if the table in question has more than a handful of columns.

Mike Schmitt · Answer

When writing a new row to a table with the primary key set to auto_increment (which it should in most cases that i know of), you would just leave that field blank in your CDT and, as Peter mentioned, the DB takes care of populating the next available value for you.

Mike Schmitt · Answer

Your a!writeToDataStoreEntity call is not providing a value for the dataStoreEntity parameter, as noted in your error message screenshot. You need to provide one in order for the write to happen correctly. 
 Also as an aside: there's no need to call "map" inside your "cast" calls. You can just pass it a plain dictionary of data and the individual fields will be typecast into the types of the CDT fields. a!map is more for use when dealing with local variables that you either don't really need to cast to a CDT type, or custom dictionaries that don't align with any particular CDT type. This doesn't have anything to do with your error but does contribute to unnecessary code complexity.

I need to pull the highest "ID" number from SQL

Top Replies