Lookup with Two Filters + Pulling Previous List Item


   Just wondering, is there a neat way of applying a two-step filter? That is, first filter by a user-selected item then filter by a secondary filter?


Context: I'm creating a master inventory list of multiple items. The user must select from a list of potential items. When a new shipment arrives, but the same inventory #, I want the form to autopopulate with the previous inventory #, rather than have the user type in the text.


To pull the previous inventory #, I'm basing it on this nifty formula: lookup("List","ID",max(lookup("List","Item Child Count","0","ID",true)),"ColumnYouWantOnPreviousItem") from here.

However, I realize that it'll pull the most recent item, regardless of what the item is, which would lead to an inaccurate inventory #.


I then created a hidden calculated column on Sharepoint that merges both the Item Name together with the ID #. However, I'm stuck now as to how to modify the above formula to account for the item name.

Is there an ootb solution, or any pointers as to which direction I should go? Hoping to avoid jQuery and other javascript functionalities, as I have no knowledge.





I realized that "MediaName-ID"-1 would be inaccurate, as the ID value may not exist. Additionally, creating multiple views where only the relevant item shows would be a bit... cumbersome?

Thanks for any hints/tips!!!

0 Kudos
1 Reply

Re: Lookup with Two Filters + Pulling Previous List Item

In order to more adequately answer this, I would need some more details about the list being queried. 


Are the items that the user is selecting terms that are already being gathered from the list you'd like to query? I'm not entirely clear on that. 


Can you take a screenshot of the list being queried (edited or otherwise), or provide a mockup table of the columns / structure so that we might better understand the data in question? 


Again, this doesn't have to be real data, it would just be helpful to see more of the form (in action) and some of the list being queried. 




0 Kudos