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
Discussion posts and replies are publicly visible
Mike is absolutely correct, but I also wanted to check with you - what's your use case for this? I've seen some folks try to do this as a way of "auto-generating" their next ID by adding 1 to this number in Appian. However, there are a lot of edge cases that make this more challenging than it appears. For instance, what if you have a race condition where two users perform this query at the same time? The second user will then end up overwriting what the first user did.
If this is your use case (querying the max ID to define a PK for inserting the next record), I'd highly recommend using the database auto-increment capabilities. Since the database handles this, you won't have to worry about these race conditions at all.
That being said, if you have another use case please feel free to ignore this - I know I personally have come across someone trying to do this and just wanted to warn you from something that may lead to issues later!
How do you get it to write to the database when you do not have a value and the ID can not be null?
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.
I am getting the following error. I have attached the code. I have it create 2 local variables, one for an existing contact and 1 for a new one.
a!localVariables( local!contactExist: cast( 'type!{urn:com:appian:sad_Contacts}sad_Contacts', { a!map( id: ri!existCustomer, locationId: ri!existLocation, contactFirstname: ri!contactFirstName, contactLastname: ri!contactLastName, contactTitle: ri!contactTitle, contactEmail: ri!contactEmail, contactPhone: ri!contactPhoneDB ) } ), local!contactNew: cast( 'type!{urn:com:appian:sad_Contacts}sad_Contacts', { a!map( locationId: ri!existLocation, contactFirstname: ri!contactFirstName, contactLastname: ri!contactLastName, contactTitle: ri!contactTitle, contactEmail: ri!contactEmail, contactPhone: ri!contactPhoneDB ) } ), a!buttonArrayLayout( buttons: { a!buttonWidget( label: "Submit", saveInto: a!writeToDataStoreEntity( valuesToStore: { if( isnull(ri!currentContact), a!entityData(cons!contactDetail, local!contactNew), a!entityData(cons!contactDetail, local!contactExist) ), } ),
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.
Thank you for the tip on the cast. The only value not being provided is the id number.
Here is a screenshot of the values:
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 "valuesToStore", but the actual property is the singular, "valueToStore" (no "s" in "value").
Thank you Mike, that did it. I assumed having the dataStoreEntity in the values would work as it did in another interface but it did not. Thank you again for all your help.
Great, thanks for confirming.