I have a situation where an orders-related list needs to lookup prices from another list, where the prices have a "Valid from" and "Valid to" columns.
There are two requests:
1. A one-to-many relationship between the order "header" and order "items".
2. Applying "automagically" the prices based on the order date, where the price in the lookup list has the order date sit between one of the possible date spans for "valid ..."
The date spans per item-price are non-overlapping.
Being new to SharePoint and Nintex I am guessing I'll need three lists at least (the "order header" list, the "order items" list and the "item prices" list) -- and that's about all I can figure out for now.
I am using SharePoint 2013 (sadly or otherwise).
Any and all help will be greatly appreciated.
I do not fully understand your task but just want to add a note that the LookUp() pulls up only 'single item' from a list and is not a one-to-many relationship, unless there is and I am mistaken.
A quick google pointed me to this page https://community.nintex.com/t5/Community-Blogs/Create-Parent-Child-items-using-this-Nintex-forms-wo... it may give you some hints?
Thanks for replying. Unfortunately, your link doesn't show its content (something about invalid parameters), but, yes, I have found other one-to-many relationship suggestions, which seem a bit too convoluted. I was hoping there's something simpler.
Yeah, I agree my initial post is not very clear (now that I am reading it a day later).
For now, let's forget about the one-to-many relationship.
The more important issue is the filtering of values available on a multi-select list/dropdown, based on some criteria. In my "master" (header) list, I have a Lookup field, which is connected to another list. I should be able to select one or more values from that lookup list, but I want these to be filtered (reduced) based on some form of rule.
This rule could be an additional column in the lookup list (as per this article: https://community.nintex.com/t5/Nintex-for-SharePoint/Multi-select-List-Lookup-Control-connect-to-a-...), but could also be filtered based on some value in the master form.
In my particular case, I want the master form to contain an "order date" field. The lookup list would contain four fields/columns: "product code", "price", "valid from" (a date) and "valid to" (a date).
The lookup list will contain multiple pairings of "product code" and "price", but they will all have different "valid from" and "valid to" dates. These date spans will be non-overlapping (unique) for any given "product code" and "price" pairing.
In my master form, after I enter the "order date" and select a "product code" when I drop the "price" lookup field to select it from the lookup list, I would like to see only one price, related to the "product code", where the "order date" is >= "valid from" date and is, at the same time, <= "valid to" date.
In this way, I will always have only the price that was valid for the particular product on the order date associated with that particular order, even if I add another price entry for the same product later on. As this new price will be valid for a different date window, it will not change/affect the original order created in a different date window.
This is usually known as a Dimension Type 2 implementation.
@inventif .....Please see my response in below post, I hope it will work for you !!!
Thanks, Kunal, I can see there are some elements I could use, although I was hoping for something with less coding. Worth knowing you've provided this solution.