Retrieving Count of items from a list

  • 3 September 2020
  • 3 replies
  • 394 views
A
Hi,

 

Looking for a solution for the problem below.

The use case Example: We have a list named grocery as per following table structure. We need to get the count of items which has price less than 10 in the form designer before it is submitted.

 

Item

Price

Mangoes

25

Apple

10

Orange

5

Grape

3

Banana

13

 

Output should be: 2 (as there are only 2 items which has value less than 10).

 

P.S.: Someone suggested using a SharePoint CAML query filtering, and using it with calculated column. Or perhaps use SharePoint column of type “Lookup” and type of “Calculated” column. 

But I am not able to get them or integrate with Nintex forms. Can anyone help?"

3 replies

SimonMuntz
Userlevel 6
Badge +22
Hi,

Create a Calculated Column in SharePoint with the formula:
=[Price]<10
This will create a column that will output Yes Or No depending if the value in the Price column is more or less than 10.

On the form add a Calculated Value control with the formula:
count(lookup("Shopping", "LessThanTen", true, "ID",true))
This will look up a list called Shopping and search for all the entries in the Calculated column which I have called LessThanTen and return an array of ID's which are then counted and displayed.

Note: Even thou the column outputs Yes and No this is actually a Boolean and that is why true is uses in the formula instead of Yes for the filter.

Note2: As the lookup can only return a maximum of 1000 items this function will be limited to that.
Cool Nintex courses: https://learn.nintex.com/
A
Hi Simon, Thanks for your reply. Please note less than ‘10’ above is used as an example. To clarify it is a variable. Depending on what a user enters a value in one of the previous fields within the form, this 10 can be either 15 or 65 or any other number. For e.g.: When user enters 5, It should show the count of grocery items which has price less than 5 and if he enters 15, it should show the count of items which has price less than 15.
SimonMuntz
Userlevel 6
Badge +22
The solution provided counts up how many items match the criteria which was how many items are less than 10. This was done by using a SharePoint column calculated field. As this feild cannot be altered by a form my solution will not work based on your new requirement and I am not sure how you could acheive this. It may not be possible to acheive this solution without Javascript and a Classic Nintex form.
Cool Nintex courses: https://learn.nintex.com/

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings