Hi All!

I'm trying for more than a day now to build a calculation in Nintex Form that can check in my "Order" list if a given company have already ordered a given product where the order is still active. In other word people shouldn't be able to order the same product for their company if they already have an open order for the same product. A notification should be pop-up when they try to do so.

Does somebody know if there is a way to use nested lookups or a query with a calculated value field?

I don't want a WF solution as I want to notify people before they submit the request.

My closest half solution to check with an equal calculation and 2 lookups:

1.

equals(lookup("OrderList","ProductName",ProductName,"ID", true),lookup("OrderList","Company",Company,"ID", true))

The problem with this that the equal formula can't handle more than one value, it only looks for the first one.

example: equals(l8,9,12], ]9,10,7]) --> False

                equals(Â8,9,12], ;8,10,7])--> True  

Can we use a formula to return value "True" when any number is matched in both lookups?

2. The other thing I tried is to combine input values and save them when the form is posted so next time I can lookup for them:

Where ProductStatus=ProductName+Company+OrderStatus from already posted items so it can be lookup'd.

lookup("OrderList","ProductStatus",ProductName+Company+OrderStatus,"ID", true)

 

I guess the problem with this that  ProductName and Company values come from other lists (SharePoint lookup fields).

Any other solution is welcome!