I want to do this
get value from multiple select as newRefs;insert into table1 ('name') values ('pete');SELECT LAST_INSERT_ID() as newUserId;for each (currentRef in newRef) { insert into lookupTable1 (userId,ref) values (newUserId,currentRef);}
Actually there is a quicker way to do this with MySQL/MariaDB as noted here https://dev.mysql.com/doc/refman/8.0/en/insert.html
What do I need to do this?Do I use Data Store Entities, Record Types, etc.?I can't be the first one that want to do something like this.
Discussion posts and replies are publicly visible
I am not sure I understand what you try to do. Can you add a bit more details please. What did you try in Appian?
Sure.I have a multi select drop down, where the value of each drop down is the primary key of a table.
As well, I have a 'New Item' form which contains various inputs/controls.
I need to have Appian first create the new item, then get the primary key of the record that was just inserted and finally I need to add to a lookup table the primary key of the new record and the values from the multi-select dropdown.
That is part 1 adding new items.
Part 2 is similar, but it's for editing items with the possibility of the selected values in the multi-select changing when updates occur.
I know how to do all of this in a tradition programming environment (C#, Java, etc.) which only takes me about 30 minutes to do.Maybe in Appian I need to setup a MySQL/MariaDB Function/Stored Procedure?
Your first screenshot looks good as will work. At least if there is no error in the DB when inserting the data.
Your second screenshot does not work. pv!record is the data that you want to store to the DB. The data coming back from the DB is ac!RecordsUpdated.
I suggest to remove the config from the second screenshot and make sure that the most simple config works. Just follow the examples (https://docs.appian.com/suite/help/22.4/Write_Records_Smart_Service.html#examples).
This is really dead simple, it just takes a bit of getting your head around it and forget all the Java and C# relics.
Okay, let's talk about this step from the sampleI have thisAnd ItemRecord does not have the primary key field or value.In the documentation they say that the person variable has both the primary key field and value.I can easily accept that I'm wrong, but it would seem that when I follow their example I don't get the expected result.
Maybe a stupid question, but how does your record data model look like? Does it include an auto-generated primary key field?
Here's the table structure, as you can see the primary key is an AUTO_INCREMENT fieldDoes this need to be enabled as well
If so then Appian is doing extra work behind the scenes then it needs to.MySQL/MariaDB is perfectly capable to both keeping track of the auto increment field type and doing the addition when it needs to.
You will need to enable that as well. As I already said, you will need a bit of time and trial/error to adapt your way of solution design to Appian. Think of Appian being the heart of your app, and the DB becomes just a dump data storage.
There does not seem to be a way to add a sequence to a record type which has been created from a MySQL/MariaDB table. I don't have any to choose from and what I found on the Appian site was information about Oracle when first creating the record type.Maybe I have to use Data Stores Entities somehow?
Do not go back to Data Stores. Records are the way forward. Is this a synced record?
Where are you on your Appian voyage? Did you attend some of the online trainings? Did you implement the tutorials (https://docs.appian.com/suite/help/22.4/Tutorials.html)?
The record is synced.I have gone over the Appian Academy +> Appian Developer => Learning Path, not too recently and I've done a number of other courses on academy.appian.com
This SHOUD be pretty simple, but for whatever reason this is not working.I'll go back to what I said previously, this appears to be a BUG.This is pretty darn silly, 15+ hours just on something like this.I thought that one of the benefits of Appian/Low Code was quick development.I'm pretty sure that had I used SpringBoot for the backend and React for the frontend development that this could be done in that same 15+ hours.
Given that I'm using the free community edition in the cloud there is little I can do to see what is actually happening in the background.I guess I'll look into creating a Stored Procedure/Function in the MySQL/MariaDB database providing Appian can execute them.
And, according to one or two posts on this site one should not use a stored procedure to write data to a database using Appian as this could result in extra database writes.seehttps://community.appian.com/discussions/f/process/12806/is-there-any-way-to-call-a-stored-procedure-having-some-input-parameters-that-updates-writes-to-db