How can I do a "VLOOKUP" in knack?

Help Center Get Answers
1

I know this question has been asked already, but there was no reply and the threads are a year old.  

 

I have a case where I want the user to select TWO fields in their input form (those fields relate to a diff object), and based on their choice, a number comes back which can be used in a calculation.

 

Object 1: Market Data

CONTRACT          GBP/USD           GBP/EUR        GBP/JPY  ....

SPOT                         xxxx                   xxxx                xxxx

+1MON                      xxxx                   xxxx                xxxx

+2MON                      xxxx                   xxxx                xxxx

+3MON                      xxxx                   xxxx                xxxx

 

Object 2: Some Calculation

When creating a new entry, the user gets has to select an Instrument (GBP/USD, GBP/EUR etc) and the Contract (Spot, 1 Month forward, etc) from the Form.  The number that corresponds to that choice is then used in a calculation. 

 

Can anyone please suggest a way to do this?

 

Thanks! 

2

Hi Nic,

Thank you so much for the detailed reply!  That's a clever way to overcome this limitation.  It must be said however, it is a bit long and convoluted.  I hope a simpler vlookup type function is added to Knack in the future.  

Thank you once again. 

Cheers,

Ziad 

3

Hi Ziad,

Here's how you can currently achieve this in Knack:

Contracts object

Fields

Contract Name - Stores the name of a contract (SPOT, +1MON)

Currencies object

Fields

Currency Name - Stores the name of the currency exchange combinations (GBP/USD, GBP/EUR)

Contract Currency Rates object

Fields

Contract - Connection field pointing to the "Contracts" object

Currency - Connection field pointing to the "Currencies"

Rate - Number field storing the currency rate for a specific contract / currency combination

 

Settings

Display Field - Currency

We want to make sure that when selecting a contract currency rate from a dropdown, we can select using the currency field.

Entries object

Fields

Contract - Connection field pointing to the "Contracts" object

Contract Currency Rate - Connection field pointing to the "Contract Currency Rates" object

Amount (optional)  - A number field used to apply the currency rate to

Total - An Equation field that looks up the Contract's currency rate and multiplies it by the amount.

The "Total" equation is where you do the "VLOOKUP" to pull in the right value. You can learn more here: https://support.knack.com/hc/en-us/articles/226583228-Equations#access-connected-fields

"Add Entry" form

To tie this all together, we need to set up a form view to add a new entry record and let us make the cascading selection. You can follow the tutorial here if you want more details: 

https://support.knack.com/hc/en-us/articles/226589108-Create-Dynamic-Dropdowns-in-Forms

 

The "Contract Currency Exchange" input needs a "Show" option that shows us records connected to the Contract selected in the form:

So in the end you'll have a form that looks like this:

Where these two available options are the records in my "Contract Currency Rates" object:

Hope that helps! Let me know if you need any clarification here.