Blog Elements

Half Sized Blog Element (Single Author Style)

Half Sized Blog Element (Multi Author Style)

Knowledge Base, List Queries, PowerForms HTML

Multiple Sorting in List Queries

A new addition to PowerForms HTML is the ability to sort by multiple columns in a List Query. Clicking on the Sort with multiple columns presents a text-box which you can use to perform multiple sorting to your list query. As the description says: Use semicolons (;) to separate column internal names. […]

August 13, 2017/by Konstantinos

Knowledge Base, List Queries, PowerForms HTML

Master-Detail using the DataEntryGrid control

The DataEntryGrid control gives you two options. The first option is 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. The second option is to bind the DataEntryGrid control value to a ListQuery, […]

August 13, 2017/by Konstantinos

Knowledge Base, List Queries, PowerForms HTML

Multiple Sorting in List Queries

August 13, 2017/by Konstantinos

Knowledge Base, List Queries, PowerForms HTML

Master-Detail using the DataEntryGrid control

August 13, 2017/by Konstantinos

Full Sized Blog Element (Big Preview Pic)

Knowledge Base, List Queries, PowerForms HTML

Multiple Sorting in List Queries

A new addition to PowerForms HTML is the ability to sort by multiple columns in a List Query.

Clicking on the Sort with multiple columns presents a text-box which you can use to perform multiple sorting to your list query. As the description says:

Use semicolons (;) to separate column internal names. Sorting is by default ascending. You can add a minus (-) sign at the end of the column name to indicate descending sort.

Example:

Category-;Title

will sort the list query by Category descending and Title ascending.

August 13, 2017/by Konstantinos

Knowledge Base, List Queries, PowerForms HTML

Master-Detail using the DataEntryGrid control

The second option is to bind the DataEntryGrid control value 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).

All changes are kept and applied as soon as the user selects to save the current record.

August 13, 2017/by Konstantinos