Nintex Lookup Function & Repeating Row on SPO

This post is pretty much a follow up from my previous “Web Services in SPD 2013” – HERE. This part of the solution is centred on the user interface for entering multiple entries per list item. The form was built using Nintex forms over SharePoint Online (and works on SharePoint On-premise also). The entire solution was deployed on a team site named “Expenses”.

 1.    External List

 First things first, I’ll be using a list created previously named “Rates” as an external list (shown below in figure 1.1) to lookup values, specifically from the “Target” and “Rate” columns. The “Target” column was renamed from its default “Title” name. This list is permission as “Expenses Owners” with Full Control for administrative users only.

Figure 1.1: Rates list (external list)

2.    The Main List

Secondly, I created a custom list named “Entries” for the expenses entries. This is the main list that users access the form by clicking “Add new item”. This is shown below in Figure 1.2.

Figure 2.1: Entries list (main list)

3.    Building the form

Thirdly, I created the form interface using Nintex Forms. The form contains the columns as shown above in the “Entries” list and additional local form fields that will be used to set values, perform some magic and make the form dynamic as follows;

• Allows the user to Select a “Currency” from an external list using a List Lookup field,
• Populates the “Rate” form field depending on the currency selected via a lookup formula,
• Allows the user to enter the “Local Amount” paid as a local form field,
• Populates the “Sterling” field via a Calculate Value (formula - Local Amount/Rate),
• Allows the user to make multiple entries via a repeating row panel

 3.1       Select a “Currency” from an external list using a List Lookup field

This is a list lookup form field named “Currency”. Using this field, Nintex will look up the “Title” column of the “Rates” external list on the same site as shown below in Figure 3.1.1.

Figure 3.1.1: List Lookup field settings

As you can see above, the name of the local form field is “Currency”, the source list is “Rates” and the list column to extract the values from is “Title” (I’ve renamed it but Nintex/SharePoint understands the default name of a custom list column though the name “Target” will also work).

3.2         Populates the “Rate” field depending on the currency via a lookup formula

The “Rate” field is actually a Calculated Value local form field that contains a formula. There is a structure to the formula. Doing it another way will not work. The formula lookup the “Rates” list, pull the “Title” field, match it or parse Lookup (meaning get its value) the currency lookup value and populate the local form field named “Rate”. Other vital settings are the decimal places and that the formula gets recalculated on view, new and edit modes as shown below in Figure 3.2.1.

Figure 3.2.1: The “Rate” Calculated Value form field settings

3.3         Allows the user to enter the “Local Amount” paid in a local form field

This part is straight forward. The user will enter their amount in the “Local Amount” local form field. It is a single line textbox field and does not have to be connected to a column in the “Entries” list (also a default value doesn’t have to be set) as shown below in Figure 3.3.1.

Figure 3.3.1: The “LocalAmount” form field settings

 3.4         Populates the “Sterling” field via a Calculate Value (Local Amount/Rate)

This is a calculated value field with a runtime function formula which is “LocalAmount” divided by “Rate” to convert it to Sterling. Other settings are the decimal places and that the formula gets recalculated on view, new and edit modes as shown below in Figure 3.4.1.

Figure 3.4.1: The “Sterling” form field settings

3.5         Allows the user to make multiple entries via a repeating row panel

Now, with all the local form fields created, they can now be placed in a Repeating Panel with a “Add Another Expense” link which allows the user to add individual expense that may or may not be the same currency in a repeating row. Additionally, all local fields are populated in runtime.

Figure 3.5.1: The Nintex Expense form with repeating rows

Note: This is a demo so when creating the form, I haven’t left align some of the local form fields.

4.    Rapping Up the Form

There are other fields on the form. They are the fields connected to the columns of the “Entries” list and are self-explanatory. The “Add Another Expense” link at the bottom is exactly what it says on the tin. The “x” to the right of the repeating row allows the user to delete that row. Finally, the “Total” local form field is a Calculate Value with a runtime function formula of “sum(Sterling)” which is the sum of all row entries for the “Sterling” local form field and is connected to the “Total Due” column of the “Entries” list.

Reference: http://ift.tt/2dsbOZB

 

by Larry Saytee via Everyone's Blog Posts - SharePoint Community