Blog Elements

Half Sized Blog Element (Single Author Style)

Half Sized Blog Element (Multi Author Style)

Knowledge Base, List Queries, PowerForms HTML

Master-Detail using the ListAndForm control

This is a step-by-step guide of building a mster-detail form 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 […]

August 13, 2017/by Konstantinos

Knowledge Base, List Queries, PowerForms HTML

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 […]

August 13, 2017/by Konstantinos

Knowledge Base, List Queries, PowerForms HTML

Master-Detail using the ListAndForm control

August 13, 2017/by Konstantinos

Knowledge Base, List Queries, PowerForms HTML

Complex List Query Examples

August 13, 2017/by Konstantinos

Full Sized Blog Element (Big Preview Pic)

Knowledge Base, List Queries, PowerForms HTML

Master-Detail using the ListAndForm control

This is a step-by-step guide of building a mster-detail form 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 :

Code

form.FieldValue(“c_LastName”) + ” ” + form.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 opening 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 :

Code

{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 :

Code

form.FieldValue(“c_ID”) != “”

Another way to accomplish a similar result is to write a small script (javascript) inside the LoadCompleted event that would disable or hide the “Contacts” tab from the form if we are still in a new Customer record:

Code

var id = form.DataItem.ID;
if (pf.IsEmpty(id))
form.HideTab(1);
else
form.ShowTab(1);

August 13, 2017/by Konstantinos

Knowledge Base, List Queries, PowerForms HTML

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

Code

(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 :

Code

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 :

Code

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 :

August 13, 2017/by Konstantinos