Retrieve data from list based on multiple values

Hi,

I would do a three tiered cascading lookup with the third tier just being a calculated value control which auto populated based on the first two tiers.
The user selects the year, then the code or vice versa and the rate auto populates.

The most important thing is the setup of the lists.
https://community.nintex.com/t5/Community-Blogs/Three-Tier-Cascading-Drop-Downs-Do-Go-Chasing-Waterfalls/ba-p/82985

I managed to find a solution on my own. I wanted to use column concatenation, however, this solution doesn't work if your column type is a lookup. So I dug deeper and found a workaround: I created a Workflow which will save the lookup value into a column of the same list. The column type is set as "Single line of text".

- Here is the link regarding the column concatenation: https://www.c-sharpcorner.com/blogs/concatenate-columns-using-calculated-column-in-sharepoint-2010

- And here is the link with the workaround for Lookups: https://spgeeks.devoworx.com/lookup-field-in-calculated-column-sharepoint/

 

---------------------------------------------------------------------------------------------------------------------------------

Example

 

In your SharePoint list select "List Settings". Then, click on "Create Column". Define a name for your new column (we are going to name it "Lookup value") and set the type as "Single line of text". Save it.

Hide your newly create column because you don't want it to be visible in your List form. To do so, go to you "List Settings", then select the "Content Type". On your list content type, click on the column you want to hide and select "Hidden (Will not appear in forms)".

 

You don't need to execute anything using Powershell like it is mentionned in the link above.

 

After that, you need to create a new Workflow for you list. To do so, click on "LIST" in the SharePoint ribbon, then on "Nintex Workflow". Click on "Create list workflow". A blank workflow opens up. Before we start the workflow, we are going to edit its settings, on how it is going to be executed. Click on "Workflow Settings" and give the workflow a name. Then check the following "Workflow options":

 

After defining the settings, we are goind to create the workflow (see picture below)

Once you selected "Set field value", click on the small arrow and select "Configure". You should have something like this:

Now you need to set the condition. In the first dropdown list, you should select the element from your SharePoint list you created previously (in our case it is "Lookup value"). In the second dropdown list you should select "Value", because you want to save the original lookup's value into your SharePoint column "Lookup value". Finally, in the textbox, you should select the "Item Property" which represents the original lookup (the name of the column in you SharePoint list).

Save your Workflow and click on "Publish".

Now, you start your workflow manually on existing fields, or create new elements or edit existing ones in order to launch it automatically.

 

Now you can create the concatenation between two fields using the value of your original lookup which we have put in the columne "Lookup value". To do so, go to the list settings, then click "Create Column". Set a name and select "Calculated (calculation based on other columns)". In the field "Formula", enter following line: 

=CONCATENATE(Field1," ",Lookup value)

 

Save your column and go back to your list. You will see that the values of two fields will be visible in you calculated column.

 

If you have any questions, feel free to ask.