Pulling record from connected object based on multiple criteria

Help Center Get Answers
1

Hi, 

Forgive me if this has been asked and answered - I couldn't find it based on my search. 

I'm trying to understand if it's possible to create a calculated field based on multiple criteria from a connected table.  Here's a general description:

Each year, team members are assigned to a class and within that, a level.  So, for instance, in 2017 there is an A class and a B class.  Within each class, there are four levels, e.g. A1, A2, A3, A4.  Each one gets a % value.  A1 - 14%; A2 - 4.3%; B1 - 2.2%, B2 - 5.5%, etc.  

 

So I created an Object where I select the relevant year, then the relevant class (A or B) and then the relevant level (1 to 4).  And finally I assign the % value that I want each class/level to have for a specific year. 

In a separate object, I have team members.  Each team member record provides them with a class and level and list the relevant year.

Is there a way to bring the % value, for the correct year, class and level, to the team member object?

Thanks for any advice. And if this isn't possible in Knack, it would be great to get confirmation of that. 

 

 

2

Hi Seth.  I think the below post will help: 

https://support.knack.com/hc/en-us/community/posts/360010413412-How-can-I-do-a-VLOOKUP-in-knack-?page=1#community_comment_360001189372

Cheers,

Ziad 

3

Sunny, really appreciate the thoughtful suggestion.  Trouble is the percentage is subject to change, and it's going to be a different user who sets it than the one that sets class and level.  I suppose what I'm really looking for is similar to the excel VLookUp or Index functions, where I can pull-in data from a table using two criteria (e.g. in this case: look in table 2018, and find the row that is X class, and column that is Y level, and return that number to me).  I did this in Excel, unfortunately doesn't seem like Knack can accomplish this.  

4

Create a Separate Object  With columns "Year", Class, Level and %age  with Formula field ->(Year-Class-Level)

And connect this to Other object and then allow user only to change this field using Dropdown . (Not allow to change Only Year, class Level etc separately). Fill those value using selected Field  

 

And then update Object based upon selected Record .

 

Regards,

Sunny Singla

ssingla1985@gmail.com

+919855089359

 

1 Like