Grid Populated by ExecuteQuery

In this example, the Grid component is a read-only grid that displays transactions from the student's ledger card based on a selected term. To collect the data needed to populate the grid, the ExecuteQuery activity is used. The ExecuteQuery activity gets the requested dataset and loops through each row to populate the grid.

Form Sequence

The form sequence has 3 forms and an End State Form.

Form 1

This form has fields for the student's First Name, Middle Initial, Last Name, SSN, Date of Birth, and Email Address. Each field is bound using the Model property.

Form 2

This form has a Drop-down List component to select a Term.

Form 3

This form has a Grid component with the following properties:

Column Specifications:

Note: The Grid Property Name matches the fields being returned via the ExecuteQuery activity.

The rendered form has a grid with 5 columns populated with data retrieved from the database:

Workflow

Form 1 (State 1)

The workflow begins with a LookupUser activity that returns the SyStudentID, which is a variable of type Int32.

The GetEntity<StudentEntity> activity uses the SyStudentID and returns the StudentEntity, which is an argument of type StudentEntity.

The Assign activity assigns the value studentEntity.Ssn.Remove(1,7) to studentEntity.Ssn. This formats the SSN to display only the last 4 digits. It starts at 1 and removes 7 digits. This includes the dashes (111-11-1111). So that leaves the last 4 of the SSN.

The ExecuteQuery activity uses the following CommandText as InArgument and returns the OutArgument RegistrationBill, which is a variable of type DataSet. The CommandText selects specific fields from various data tables for the given SyStudentID.

STRING.Format("Select Distinct AdTerm.Descrip AS termSelect, SaTrans.AdTermId AS termSelectID from SaTrans join AdTerm on SaTrans.AdTermId = AdTerm.AdTermId WHERE SyStudentID = {0}",studententity.Id)

With the ExecuteQuery and subsequent workflow activities, we are basically retrieving all the terms that are associated to ledger card transactions and populating the drop-down list on Form 2 with those terms so the student can select them. After the student selects a term, Form 3 will populate all the ledger card transactions for that selected term.

The ForEach<DataRow> activity with TypeArgument=System.Data.DataRowfollows the ExecuteQuery activity.

For each item in RegistrationBill.Tables(0).AsEnumerable the following 3 assignments are made:

To	Value	Notes
varList	New NameIdObject	varList is a variable of type NamedIdObject.
varList.Name	item("termSelect").ToString	termSelect is an argument of type String.
varList.Id	CINT(item("termSelectId"))

Following the assignments, the AddToCollection<NameIdObject> activity uses the InArguments Collection=varTermSelect and Item=varList and TypeArgument=Cmc.Nexus.FormsBuilder.Entities.NamedIdObject, where varList and varTermSelect are variables of type List<NamedIdObject>.

Next, the following assignment is made:

To	Value	Notes
myTerms	varTermSelect.toArray	myTerms is an argument of type NamedIdObject[]. varTermSelect is a variable of type List<NamedIdObject>.

Form 2 (State 2)

A LogLine activity captures the following text: Environment.NewLine + "Selecting Term" + Environment.NewLine

An Assign activity assigns MyTermsSelect to Test, where MyTermsSelect and Test are variables of type Int32.

The Next transition to the 3rd form has another LogLine activity with Text=Environment.NewLine + "Entering 3rd Form" + Environment.NewLine.

Form 3 (State 3)

A LogLine activity captures the following text: Environment.NewLine+Environment.NewLine+ "*** The Student " + SyStudentId.ToString + "***" + Environment.NewLine+Environment.NewLine

Next, an ExecuteQuery activity uses the following CommandText as InArgument and returns the OutArgument RegistrationBillData2, which is a variable of type DataSet.

"select SaBillCode.Descrip+' '+'(Pending)' as Descrip, SaPendingCharge.Amount, SaPendingCharge.Type, SaPendingCharge.DateAdded as Date, (select AdTerm.Descrip from AdTerm(nolock) where AdTerm.AdTermID= SaPendingCharge.AdTermID ) as TermDescrip, 0 AS CourseAmount, '' AS CourseDescription from dbo.SaPendingCharge SaPendingCharge Inner Join dbo.SaBillCode SaBillCode (nolock) on SaPendingCharge.SaBillCodeID=SaBillCode.SaBillCodeId inner join AdTerm (nolock) on AdTerm.AdTermID = case when SaPendingCharge.RegAdTermID IS not null then SaPendingCharge.RegAdTermID else SaPendingCharge.AdTermID end where SaPendingCharge.SyStudentID =" + SyStudentId.ToString + "and SaPendingCharge.AdTermId =" + Test.ToString + "Union All Select SaTrans.Descrip, SaTrans.Amount, SaTrans.Type, SaTrans.[Date] as Date, AdTerm.Descrip as TermDescrip, IsNull (SaTransCourseAmount.Amount, 0) AS CourseAmount, AdCourse.Descrip AS CourseDescription From dbo.SaTrans (nolock) inner join AdTerm (nolock) on AdTerm.AdTermID = SaTrans.AdTermID left join saTransCourseAmount on saTrans.saTransID = saTransCourseAmount.saTransID left join AdCourse on AdCourse.AdCourseID = SaTransCourseAmount.AdCourseID where SaTrans.SyStudentID =" + SyStudentId.ToString + "and SaTrans.AdTermId =" + Test.ToString

The following 3 assignments are made after the ExecuteQuery activity:

To	Value	Notes
RowNum2	0	RowNum2 is a variable of type Int32.
Count2	RegistrationBillData2.Tables(0).Rows.Count	Count2 is a variable of type Int32.
myGrid	New SerializableDynamicObject(Count2-1) {}	myGrid is an argument of type SerializableDynamicObject[].

The ForEach<DataRow> activity with TypeArgument=System.Data.DataRowfollows the assign activities.

For each item in RegistrationBillData2.Tables(0).AsEnumerable the following 8 assignments are made:

To	Value	Notes
myGridRow	New RepeaterSerializableDynamicObject	myGridRow is a variable of type SerializableDynamicObject.
myGridRow.DataDictionary("Descrip")	Convert.ToString(item("Descrip"))	Descrip is an argument of type String.
myGridRow.DataDictionary("CourseAmount")	Convert.ToString(item("CourseAmount"))	CourseAmount is an argument of type String.
myGridRow.DataDictionary("LedgerDate")	Convert.ToString(item("Date"))	LedgerDate is an argument of type String.
myGridRow.DataDictionary("TermDescrip")	Convert.ToString(item("TermDescrip"))	TermDescrip is an argument of type String.
myGridRow.DataDictionary("CourseDescription")	Convert.ToString(item("CourseDescription"))	CourseDescription is an argument of type String.
myGrid(RowNum2)	myGridRow	myGrid is an argument of type SerializableDynamicObject[].
RowNum2	RowNum2 + 1	RowNum2 is a variable of type Int32.