How to insert multiple records using an editable grid in Appian

Hi, Appian Developers! I hope you all are doing well and safe. Today we are going to learn to insert multiple records for customer in a database table named tbl_customer using an editable grid in Appian. I would clear in the starting, you must be certified with Appian Associate Developer, so it will be easy for you all. First I will tell you the whole requirements and what we are going to implement here.

Requirement:

Let’s consider, that we have a tbl_customer in the Appian cloud database. And this table has the following fields as

·      customerID
·      customerName
·      emailID
·      phoneNumber
·      profilePicture
·      description
·      createdBy
·      createdDate
·      isActive

You can see the below screenshot for how does your table looks like.

Now, we want to insert multiple customer details in one go instead of adding them one by one. It means we will add the multiple customer data from an interface using an editable grid in Appian.

e.g., Customer1, Customer2, Customer3, and Customer4 will have their own CustomerID, CustomerName, emailID, and so on. By implementing this, we are not required to add the n number of customer details one by one. So, without wasting our time, let’s switch to Appian Designer Console. As I am using the free community edition of Appian, I have my own Appian Designer console. You can also request your instance by clicking on this link https://community.appian.com/

 

I am creating a new application named “Practice for Appian” as below screenshot.

As I have checked the box in yellow highlighted in the above screenshot.

It will create some default folders and groups for me. This feature is available in only Appian version 21.4 release or later. Now click on the save button and you will see your application is created with default objects with folders and groups as below.

First, we will create our Data Store for our application which helps to establish a connection between our database and application. To create the Data Store, please click on the new button and select Data Store as below screenshot.

You must enter your datastore name and description as below.

Now click on Create button and choose your security for datastore object and click on the Save button as below.

Note: Once your CDT is created, do not forget to verify and publish your Data Store.

As we have already created our database table, so now we will create our CDT. To create a CDT, please click on the New button and select Data Type as below screenshot.

We will create our CDT using "Create from a database table or view" and select your Data Source name and table as below.

Now you must enter your CDT name and select your Data Store which we have created as above. Please see the below screenshot for your reference.

Once your CDT is created, now we will create a Constant for our entity as below.

Both Datastore and CDT have been created, now we will create our interface using an editable grid. To create an Interface, please click on the New button and select Interface as below.

Please enter your Interface name below.

Now click on Create button, and you will see the below window.

Now switch to expression mode and paste the below code into your Interface.

	a!formLayout(
	label: "Add Multiple Cutomer Data",
	contents: {
	a!gridLayout(
	totalCount: count(ri!items),
	headerCells: {
	a!gridLayoutHeaderCell(label: "Customer Name"),
	a!gridLayoutHeaderCell(label: "Email ID"),
	a!gridLayoutHeaderCell(label: "Phone No."),
	a!gridLayoutHeaderCell(label: "Profile Picture"),
	a!gridLayoutHeaderCell(label: "Description"),
	/* For the "Remove" column */
	a!gridLayoutHeaderCell(label: "")
	},
	/* Only needed when some columns need to be narrow */
	columnConfigs: {
	a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight: 3),
	a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight: 3),
	a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight: 3),
	a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight: 2),
	a!gridLayoutColumnConfig(width: "DISTRIBUTE", weight: 3),
	a!gridLayoutColumnConfig(width: "ICON")
	},
	rows: a!forEach(
	items: ri!items,
	expression: a!gridRowLayout(
	id: fv!index,
	contents: {
	a!textField(
	label: "customer name " & fv!index,
	value: fv!item.customername,
	saveInto: fv!item.customername,
	required: true
	),
	a!textField(
	label: "email " & fv!index,
	value: fv!item.emailid,
	saveInto: fv!item.emailid,
	characterLimit: 50,
	required: true,
	requiredMessage: "Please enter purchaser email id!",
	validations: if(
	or(
	isnull(trim(fv!item.emailid)),
	len(trim(fv!item.emailid)) > 255,
	length(split(trim(fv!item.emailid), " ")) > 1,
	count(split(fv!item.emailid, "@")) <> 2
	),
	"Please enter valid email id",
	with(
	local!localPart: split(trim(fv!item.emailid), "@")[1],
	local!domainPart: split(trim(fv!item.emailid), "@")[2],
	if(
	or(
	length(split(local!domainPart, ".")) < 2,
	contains(split(local!localPart, "."), ""),
	contains(split(local!domainPart, "."), ""),
	not(
	isnull(
	stripwith(
	lower(local!domainPart),
	"abcdefghijklmnopqrstuvwxyz1234567890-."
	)
	)
	),
	not(
	isnull(
	stripwith(
	lower(local!localPart),
	"abcdefghijklmnopqrstuvwxyz1234567890-._+'&%"
	)
	)
	)
	),
	"Please enter valid email id",
	""
	)
	)
	)
	),
	a!textField(
	label: "phone number " & fv!index,
	value: fv!item.phonenumber,
	saveInto: fv!item.phonenumber,
	refreshAfter: "UNFOCUS",
	characterLimit: 10,
	required: true,
	requiredMessage: "Please enter purchaser mobile number!",
	validations: {
	if(len(fv!item.phonenumber) = 10, {}, ""),
	if(
	like(
	fv!item.phonenumber,
	"[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]"
	),
	"",
	"Please enter 10 digits mobile numbers only"
	)
	}
	),
	a!fileUploadField(
	target: cons!PFA_PROFILE_PICTURE_FOLDER,
	maxSelections: 1,
	value: fv!item.profilepicture,
	saveInto: fv!item.profilepicture,
	required: true,
	requiredMessage: "profile picture is required",
	validations: {
	a!localVariables(
	local!invalidExtensions: difference(
	upper(fv!files.extension),
	{ "PNG", "JPG" }
	),
	if(
	length(local!invalidExtensions) > 0,
	"Attachments must be images. Remove: " & index(
	fv!files,
	"name",
	wherecontains(
	local!invalidExtensions,
	upper(fv!files.extension)
	),
	{}
	),
	""
	)
	),
	if(
	or(fv!files.size > 300000),
	"Attachments may not exceed 300KB. Remove: " & index(
	fv!files,
	"name",
	where(fv!files.size > 300000),
	{}
	),
	""
	)
	},
	buttonStyle: "SECONDARY",
	buttonSize: "STANDARD"
	),
	a!textField(
	value: fv!item.description,
	saveInto: fv!item.description,
	characterLimit: 100,
	required: true,
	validationGroup: "main"
	),
	/* For the Removal Column*/
	a!richTextDisplayField(
	value: a!richTextIcon(
	icon: "close",
	altText: "delete " & fv!index,
	caption: "Remove " & fv!item.customername,
	link: a!dynamicLink(
	value: fv!index,
	saveInto: {
	a!save(ri!items, remove(ri!items, save!value))
	}
	),
	linkStyle: "STANDALONE",
	color: "NEGATIVE"
	)
	)
	}
	)
	),
	addRowlink: a!dynamicLink(
	label: "Add Customer",
	value: 'type!{urn:com:appian:types:}PFA_Customer'(
	createdby: "Admin",
	createddate: now(),
	isactive: "Active"
	),
	saveInto: {
	a!save(ri!items, append(ri!items, save!value))
	}
	),
	rowHeader: 1
	)
	},
	buttons: a!buttonLayout(
	primaryButtons: a!buttonWidget(
	label: "Submit",
	saveInto: a!writeToDataStoreEntity(
	dataStoreEntity: cons!PFA_DSE_CUSTOMER,
	valueToStore: ri!items
	),
	submit: true
	)
	)
	)

view raw AddMultipleCustomerData hosted with ❤ by GitHub

Once you pasted the above code, you will see what your interface looks like as below screenshot.

Wow, Congratulations!!! Our interface is ready to insert the multiple customer data into our database table tbl_Customer.

Let's add a few customer details to the form and click on Submit button as below.

Congratulation...!!! 💓💓💓

Successfully, we have inserted our 5 customer details into the database table. As you can see from the below screenshot for our table record.

Note:

I have created two constants, one is to save the customer profile picture, the constant type is a folder, and the second is for CDT, the constant type is the Data Store Entity. And Please add one rule input as items to your interface.

I have used a!writeToDataStoreEntity function on Submit button in the interface to save the customer data into the database table.

Please see my below objects for the application.

I hope, this blog will help you to understand the use of an editable grid within the interface.

Thank you so much for your time.

Please comment if you have any issues regarding the same.

Happy Coding! ✌💓