Master-Detail using the DataEntryGrid control
The DataEntryGrid control was initially added to provide users with the option to add structured data that would be saved inside a multi-lines-of-text control (using XML). So it is ideal for cases that no external list is required to store the related data.
Since v.2.6.5, the DataEntryGrid contol if its value is bound to a ListQuery, it can manage records that reside inside a separate list. It also has a unique feature :
The data changes are performed in a batch mode, meaning that all changes are applied as soon as we save the master record. It also has another feature :
It allows to insert records in a separate list, event if we are adding a new master record.
For example, we are inserting a new Purchase Order record. Before saving the record, we provide multiple order line items using the DataEntryGrid control. As soon as the users saves the Order record, all related Order-Line-Items will be inserted and get connected to the master record. In order to accomplish that, the control sends all data in a batch mode to the server and if we are dealing with a new master record, it first saves the master record, keeps the ID of that record and uses it to insert all data provided in the DataEntryGrid control.
Here is an example on how to setup that control :
We first create 2 lists (Customers and Contacts).
Customers list columns
Title, Address
Contacts list columns
Title (renamed to Full Name), LastName (single line of text, renamed to “Last Name”), FirstName (single line of text, renamed to “First Name”), Email (single line of text), Customer (lookup on the Customers list)
We first register PowerForms for the Customers form and start customization:
First we should create a list query that will retrieve related contacts for the current customer record :
In our Customer form, we first define a ListQuery that will retrieve only the connected contacts (for the current Customer). The field that connects the 2 list is : Customers.ID = Contacts.Customer, so our query will use criteria on the Customer field of the Contacts list.
And the criteria added is based on the control c_ID which contains the ID of the current Customer record.
Notice that we have cleared the “Ignore Blank” checkbox, since that would retrieve ALL contacts when we were inside a new customer record (ID would be blank in that case).
Moreover, we use a criteria type = “Lookup” since the “Customer” column for which we apply the criteria, is a lookup column and we provide the ID part of its value.
We then add a DataEntryGrid control on the form to hold the contact records.
We set its height to fit our needs and we bind the control to get values from the ListQuery :
We now have to define the fields for the DataEntryGrid.
The DataEntryGrid control supports simple controls (like TextBox, DatePicker, static Combo, Checkbox) but to achieve the best result we could use Existing controls. To do that, we must first create some controls on the form (that will be used to data entry inside the grid), then hide them and use the control names (by selecting “Existing” in the control type)in the configuration of the grid fields.
We add the following controls below :
c_ContactTitle (a textbox control)
c_ContactLastName (a textbox control)
c_ContactFirstName (a textbox control)
c_ContactEmail (a textbox control)
We make the c_ContactTitle control ReadOnly and set to get its value from the c_ContactLastName and c_ContactFirstName controls by using a value formula : FieldValue(“c_ContactLastName”) & ” ” & FieldValue(“c_ContactFirstName”).
We then hide the controls using the appropriate button in the toolbar.
In the Extra tab of the control properties window we then define the fields :
Contact Title
Contact Last Name
Contact First Name
Contact Email
and then we define the Values we want to provide programmatically to each record using the “Values” property of the Extra configuration. There you can define Name/Value pairs to provide default values to columns of the targeted list.
For example you can provide a pair like :
Name = Active, Value = 1
or a calculated pair :
Name = Active, Value = {c_Active}
to get the value from an control of the existing form.
If you want to provide the ID of the current record, instead of using the {c_ID} keyword, you can provide the :
{ParentID} keyword which will work event if the current record has no ID yet (a new record).
You can then use inline editing (by selecting the corresponing property in the extra configuration of the control) or the popup form to add/edit or delete related records.
All changes are kept and applied as soon as the user selects to save the current record.
Master-Detail using the ListAndForm control
This is a step-by-step guide of building a mster-detail form (PowerForms Advanced) using the ListAndForm control.
For this example. we will use 2 connected lists : Customers and Contacts.
Customers list columns
Title, Address
Contacts list columns
Title (renamed to Full Name), LastName (single line of text, renamed to “Last Name”), FirstName (single line of text, renamed to “First Name”), Email (single line of text), Customer (lookup on the Customers list)
We first register PowerForms for both lists. Then we start customizing the contacts form.
This is what we should get as a default customization :
We do some minor customizations to our form :
Change the section columns to 4 and change control positioning:
Then we make the “Full Name” control ReadOnly and we apply a value formula to let its value be composed by the value of the LastName and FirstName controls :
FieldValue(“c_LastName”) & ” ” & FieldValue(“c_FirstName”)
We change the Customer control type from a ComboBox to a LookupPicker (to enhance form loading since the customers list might get long).
Now we start customizing our Customer form. This is the default customization we get when optning the form :
We then add a few records in both lists to assist us during customization.
For our example, we added one Customer and 2 Contacts under that Customer record.
In our Customer form, we first define a ListQuery that will retrieve only the connected contacts (for the current Customer). The field that connects the 2 list is : Customers.ID = Contacts.Customer, so our query will use criteria on the Customer field of the Contacts list.
And the criteria added is based on the control c_ID which contains the ID of the current Customer record.
Notice that we have cleared the “Ignore Blank” checkbox, since that would retrieve ALL contacts when we were inside a new customer record (ID would be blank in that case).
Moreover, we use a criteria type = “Lookup” since the “Customer” column for which we apply the criteria, is a lookup column and we provide the ID part of its value.
We also include the ID of the contact since it is required by the control to identify contact records when editing.
If we press the “Execute” button and we get the correct results, we are ready to add our ListAndForm control on the form.
We then create a separate tab to host our ListAndForm control called “Contacts”.
We place a ListAndForm control there and set its value to be the ListQuery we have just defined.
We change the form size (from the options section) to fit our needs.
This is what we should get from that :
Since the ListAndForm control is actually a separate Form-inside-a-Form, the customization we have already defined for our Contacts form is automaticaly used.
Notice that selecting records from the datagrid, allows us to edit those records in the form below that grid.
Now to define the relationship between the 2 entities so that each time we add a new contact, the parent id is used inside the Customer column of the contact, we should edit the ListAndForm control properties and go to the Extra tab of the control properties and add a Default Value :
{c_ID};#{c_Title}
The above value will replace the {c_ID} with the ID of the current customer record and the {c_Title} with the title so the final value will be something like : 1;#BPC-Components.
If we selected a ComboBox instead of a LookupPicker, the {c_ID} would be sufficient for that value, but since we want the title of the customer to be shown inside our LookupPicker, we should provide it manually (since the LookupPicker does not load records during form loading).
So if we press the NEW button inside the Contacts form, this is what we will get :
The last thing to do is to disable the ListAndForm control when we are inserting a new Customer record (since the Customer is not saved and we have no ID to connect with our contacts list).
One way to do this is to apply a “Enabled Formula” in the ListAndForm control :
FieldValue(“c_ID”) <> “”
Another way to accomplish a similar result is to write a small script (python) inside the LoadCompleted event that would disable or hide the “Contacts” tab from the form if we are still in a new Customer record:
id = dataitem.GetValue(“ID”)
if id == “” :
template.DisableTab(1)
else :
template.EnableTab(1)
or
id = dataitem.GetValue(“ID”)
if id == “” :
template.HideTab(1)
else :
template.ShowTab(1)
Complex List Query Examples
This article contains some more advanced examples on query building :
You can read some basic instructions on how to build list query criteria HERE :
For our examples, we will use a list of Cases having the following columns :
- Title (Single line of text)
- Customer (Lookup to the Customers list)
- StartDate (Datetime)
- Status (Lookup to the Status list)
- Priority (Choice combo)
- Category (Lookup to the Categories list)
- Sub Category (Lookup to the Sub Categories list)
Retrieving Cases where
(Priority = High OR Normal) AND (Status = Resolved OR Canceled)
We are going to use the ListQueries editor to build our list query. Open the designer, go to the List Queries section and add a new List Query. Provide a name, add the site url and list name and add the following criteria :
Priority Equals High (Type = Text, Left Parentesis = Yes) OR
Priority Equals Normal (Type = Text, Right Parenthesis = Yes) AND
Status Equals Resolved (Type = Text, Left Parenthesis = Yes) OR
Status Equals Canceled (Type = Text, Right Parentesis = Yes) AND
The steps to add the criteria follow :
1st :
2nd
3rd
4th
Then press OK to close the criteria editor and test your query results.
We want from inside the Customer form, to query the Cases list and retrieve the cases connected with the current Customer record.
In order to achieve this, we will have to build a List Query and bind it to a ListDataGrid control.
Moreover, we want to filter the cases by Status, so we must provide an unbound combobox that the user will use to filter results.
So we open our customer form and we add a ListDataGrid control and a ComboBox (named c_StatusFilter) to help us filter the list.
For the c_StatusFilter control, provide static values (inside the Lookup Details tab) : Assigned;Canceled;Resolved;Submitted (which are the actual display values of the Status column)
Then we must define the List Query that will provide data to the ListDataGrid.
Open the designer, go to the List Queries and add a new one.
Set the criteria to the following :
Customer Equals {c_ID} (Type=Lookup, IgnoreBlank=No)
Status Equals {c_StatusFilter} (Type=Text, IgnoreBlank=Yes)
The above criteria have the following effect :
If we are opening an existing customer, the c_ID control will contain a value (the ID of the record) and the filter fill be applied. If we are in a new customer record, the c_ID control will be empty but the IgnoreBlank property will force the criteria to be applied and the query will not return any results.
Moreover, the c_StatusFilter combobox will also be used during the query, limiting the retrieved results, but the IgnoreBlank property set to Yes, will exclude the Status criteria when no value is selected in the combobox.
Building the List Query Criteria :
An the complete query :
NOTE : Each criteria item has a separate IgnoreBlank property which should be set according to user needs. The query itself has a separate IgnoreBlank property that is applied to every criteria item inside the query (only if set to True)
Binding the ListQuery to the ListDataGrid on the form :
And we have the final results :
Querying SharePoint Lists and displaying results
Users can execute queries to the Sharepoint Lists and show the result in a DataGrid control. To enter the List Query editor, first enter PowerForms designer mode and select LIST_QUERIES from the drop down menu. The selection menu is highlighted red on the picture below. A form similar to the one below will appear. This is where List Queries are created and found using the editor.
To better understand how to create a List Query, we will try to draw data from an existing list using the editor above. Our example list is called ‘Projects’ and contains, as no surprise, various company projects.
The list contains information such as: the Project’s name, the Customer, an Estimated Start and End Date, the Actual Start and Finish Date, the Active Start and Finish Date, the Baseline Cost and Effort, the project’s Owner and finally, a short description of the project itself.
The Projects list along with some sample data is shown below:
In order to create a list query for the Project’s List, first we enter LIST_QUERIES part of our PowerForms designer and then we press the New button highlighted below:
By Default a new row appears in our available list queries, named ListQuery1, containing the information shown below:
First, we will change the Name to something more suitable. In our case, we will call our new query ‘GetProjects‘.
Then we will enter our Sharepoint’s site on the Site Url and press the Apply button. This causes the form to load the available site lists in the dropdown combobox directly below, named Lists.
Therefore, we enter our site’s url and press Apply. The lists appear when we click on the List menu. We select the list Projects and the All Items view. Ignoring all other parameters, we select the Executebutton. Data loads and is shown on the grid below the execute button.
A lot of information loads on the grid, some of which may not be needed.
There are too many columns loaded by default and in our query, only the ID, the project name, the customer and a short description of the project are required.
To limit the information, we will select to display only those columns mentioned above. To do so, we will press on the Gear Icon next to Columns.
The Setup Attributes form appears. To select data only from the columns mentioned above, we press on the New button as highlighted below. This will in turn creates a new row which will represent one of the columns we’d like to draw data from.
Pressing on the field menu reveals all of the columns contained within our Projects Sharepoint list. Browsing through, we select the field ID and press the Set button. Our action sets the Value field to display the name of our selected list column.
We follow the actions above to select the other three columns we are interested in, Title (Project name), Customer, Description. Finally, we press OK.
Going back to our designer, we select the Execute button to see the changes on our list query’s displayed data.
The information looks acurate, but we’re still not satisfied because the column titles are not what we’d like them to be.
To change the headers, we select the Gear Icon next to Headers:
The Setup Attribute Headers form appears. Selecting a row and typing a new description on the Item box changes that column’s header. Pressing the OK button validates our changes.
Again, pressing the Execute button reloads the information on the grid.
Criteria is used to filter the list query’s output. To add a new criteria, press the blue criteria label or the gear icon to enter the wizard:
NOTE : The same criteria editor can be used when defining criteria inside the “Lookup Details” tab of a lookup control (combobox, lookuppicker, etc).
The Setup Criteria wizard appears.
To start filtering the outcome, press the 
button. A blank Criteria appears having only the default values.
There you can enter multiple criteria combining them with the AND and OR clauses and also use parenthesis to combine criteria together and build complex queries.
For example a query like the following could be build using the available parameters :
(Status = Open OR Status = Pending) AND (Priority = High OR Priority = Normal) AND Customer = ‘C1’
Ignore Blank property
There are some cases where we want to ignore the criteria defined only if the value specified is blank.
For example you have a Country combobox and a City combobox on your form.
You want to filter the City combobox with the Cities that belong to the Country inside the Country combo.
BUT, you want to retrieve ALL cities in case the Country combobox is blank.
In that case, you would have to specify lookup criteria inside the cities combo, that would filter using the Country but you would check the “Ignore Blank” property to display all cities when the country combo is blank.
We’ll try to filter the output by showing only the project with ID=’1′. To do so, we start by selecting the Target column which is ID. We set Operator as ‘Equals’ and Value as ‘1’. Finally, we press OK.
Yet again, pressing the Execute button reloads the information on the grid.
At the Lookup fields (displaying ID;#TITLE) there is a choice to show the selected Value of the lookup by typing in the Attributes field {value:COLUMN_NAME} or the selected Title by typing {name:COLUMN_NAME} instead of COLUMN_NAME that would bring both.
List Query Criteria
List Queries is a common method to load results from a SharePoint list.
List Queris can be specified using the LIST-QUERIES section of the runtime designer or when defining parameters for lookup controls (inside the Lookup Details tab or the control properties editor).
For lookup controls, you can use the following option :
For List Queries, the specific editor contains the same option :
To specify the records you want to retrieve, you have to setup your lookup criteria using the Criteria Editor.
In this criteria editor, you can specify multiple criteria for querying the target list.
For each one, you should specify the following properties :
Represents the list column that you want the criteria to apply.
Every column of the target list is available in a dropdown list. The inetrnal names of the columns are used.
With this property you define the kind of query you want to perform on the target column.
Available options are :
- Equals
- BeginsWith
- Contains
- Greater
- GreaterOrEqual
- Less
- LessOrEqual
- NotEquals
- IsNull
- IsNotNull
In this property, you should type the value that should be used to compare column data with.
The value can be either static or calculated.
For calculated values, you can use control data like the following examples :
{c_ID}
{value:c_Customer}
{name:c_Country}
where the “value:” prefix returns the ID of a lookup control and the “name:” prefix returns the display member of that control.
The Type property defines how the value provided will be handled.
Available values :
- Text
- Counter
- Number
- DateTime
- Lookup
If the “Text” type is used a textual comparison will be performed.
for example a query like the following :
ID BeginsWith 1 (Query Type = Text)
will retrieve results with ID = 1 or 12 or 14, although we are dealing with a numeric data type.
The “Counter” and “Number” types perform numeric comparison.
The “DateTime” type uses Date comparison for available column data.
The “Lookup” type should be used when querying a Lookup column and the ID of the column is provided.
for example :
Country Equals {value:c_Country} (Query Type = Lookup)
assuming that the c_Country control is a lookup control loading country data.
If you want to perform a query on the Title of the country, you should something like the following :
Country Equals {name:c_Country} (Query Type = Text)
Use this property if you want to apply criteria only if the value provided is not blank.
If the value is blank, the criteria will be skipped.
For example, you may want to add a combobox with Cities (c_City) in your form and you want to filter cities be their country which is another combobox on the same form (c_Country)
The tricky part is that you want to load ALL available cities in the cities combobox when the country combo is blank.
In the above scenario, you should add the following criteria in the cities combobox (in the Lookupp Details tab)
Country Equals {value:c_Country} (Type=Lookup, IgnoreBlank=YES)
or
Country Equals {name:c_Country} (Type=Text, IgnoreBlank=YES)
These properties are used to group criteria together and perform complex queries.
For example queries like the following can be performed using these properties :
(Priority = High OR Normal) AND (Status = Resolved OR Canceled)
NOTE : Nested parenthesis are not supported when building complex queries.
The clause is the operator that binds criteria together.
Availble values : AND, OR
Apply Form Validations or Pre-Update actions using external web posts
You can apply validation rules inside your forms using external web pages.
The system can call an external web page and pass the action (preupdate, preinsert, predelete) and the list of the field name/ field value pairs.
This web page should be declared in the Options tab, inside the “Before Update Post Url” property. Absolute url is required.
The final Uri should be something like :
http://servername/page.aspx?action=preupdate&values=name1;#value1|name2;#value2…
If you want to apply validation rules, you could define a predefined prefix (property Passed Validation Prefix) and inside your validation page, return a predefined string that begins with that prefix if no validation error occurs.
Otherwise, the system will cancel the Update and show the returned error message to the user.
NOTE: If the url is external and in a different domain with .xap file, you will have to place the clientaccesspolicy.xml file in site root folder as shown in the instructions Here.