Formula in Calculated value

[aishuiyer]

I have a Form where I generate some short codes using build string function for marketing team.

In the form I have a control called "IO_Number" which  has its master in list "IO_Number_Master".

I also have a control named Category which has its masters in list "Category_Master"

There are  9 Categories where each has its Corresponding IO Number.

Each year IO Number changes for those 9 categories.

now I want the IO_Number control to show only the IO Number option in the drop down corresponding to the year and category selected.

In Sql we can write the query as:

 Select IO_Number from IO_Number_Master

           where year=year && Category==Category.

now which formula to use in calculated field ?

[chaddavis]

If you're just returning one value from the related lists, you could use the Lookup function inside your calculated fields.

However, if you want a drop down of values returned from the list, you may want to look at the List Lookup control.

[aishuiyer]

Hi Davis, thanks for the reply

I have already used list lookup control for that field, but it is showing all the nine values in the list of that year.

I want the control to show only one value in the drop down which corresponds to the year and category been selected.

Example:1 Category(PCG) -year-IO Number (For PCG), [Like this another 8 categories are there in the list].How do I do this?

[chaddavis]

It would've helped to see the data that's being returned as well, both from your list and in the drop down.

To clarify, am I understanding that you simply want to return 1 value from the list filtered by 2 fields (Category & Year)?

I found the following topics that may be of some help to you:

I cannot get nested lookups working in forms‌

Nintex Forms - lookup with 2 filters‌

Three-Tier Cascading Drop Downs: Do Go Chasing Waterfalls!‌

How to multiple filter list lookup?‌

Dealing with Cascading Lookups in Nintex Forms‌

This topic has been covered in numerous other posts.

[aishuiyer]

 Hi,

this is the list IO_Number and all the Numbers in the IO_Number column comes in the drop down control where as I want only one value to come in the dropdown.

for example. when the user selects Cateory : cooking(PCG) && Year : 2018 then in the IO_Number field I want it to show two values in this case as this Category has 2 values as shown in the image.