Auto populate fields based on lookup table

  • Profile Image
    boldnewyork
    Asked on April 13, 2015 at 03:16 PM

    If i have a form with the following filled out and want to reference a lookup table how can this be done?

    See below:

    Split= Y,

    Deal Type = Exclusive

    Sub-Type = CoBroke

    Primary Agent = Test1 

    Primary Agent % = D – see notes below for D calculation ** this is where i need the lookup to Occur**

    Agent 2 = Drop down

    Agent 2% = free text (Max 50) 'X'

    D = ‘lookup value from table’ minus X

    Lookup value from table =   Test1&Bold CoBroke = Value in 3A

    so

    D = 20 - X 

    **LOOKUP TABLE**:

     

     

    1

    2

    3

     

    Name

    Open Split:

    Deal Type = Open

    Bold-Direct:

    Deal Type = Exclusive

    Split = Y

    Sub-Type = Direct

    Bold-CoBroke:

    Deal Type = Exclusive

    (Split = Y

    Sub-Type = CoBroke

    A

    Test 1

    50

    40

    20

    B

    Test 2

    50

    40

    20

    C

    Test 3

    60

    50

    30

     

     

  • Profile Image
    BJoanna
    Answered on April 13, 2015 at 04:20 PM

    Can you please provide us ID of your form where you want to achieve this? And if you can, please explain in more details your request? 

    Not sure exactly what you want to achieve but you can use our conditional logic to make some 'searching'. You can try with conditions "contains" to make a search of sorts.

    http://www.jotform.com/help/57-Smart-Forms-Using-Conditional-Logic 

    Looking forward to your reply. 

     

  • Profile Image
    boldnewyork
    Answered on April 13, 2015 at 06:24 PM

    The form is not yet created, i don't want to create it until I get the answer to my question.  See below for better explanation.  Imagine, a user inputs certain values for the following fields, where:  Field= 'Value',

    Split= 'Y'

    Deal Type= 'Exclusive'

    Sub-Type= 'CoBroke'

    Primary Agent= 'Test1'

    Primary Agent %=  '** this is where I need the table lookup to occur**'

    I need the Primary Agent % 'value' to return the intersection at 3A because the inputs above Primary Agent % are:

    Deal Type = Exclusive

    Split = Y

    Sub Type = CoBroke

    Therefore Primary Agent %=  what is in cell 3A = 20

    The LOOKUP TABLE will be in excel format.  See example below

     

     

    2

    3

     

    Name

    Deal Type = Exclusive

    Split = Y

    Sub-Type = Direct

    Deal Type = Exclusive

    Split = Y

    Sub-Type = CoBroke

    A

    Test 1

    40

    20

    B

    Test 2

    40

    20

    C

    Test 3

    50

    30

     

     

  • Profile Image
    Charlie
    Answered on April 13, 2015 at 10:26 PM

    Hi,

    Unfortunately, we do not have a lookup table, but this is possible using conditional logic. Here's an example:

    IF Deal Type = Exclusive AND

    IF Split = Y AND

    IF Sub-Type = CoBroke

    THEN

    Primary Agent % = 20

    Here's a sample form: http://form.jotformpro.com/form/51028864393965.

    The screenshot below will show how I did mine:

    1. Add drop down fields for the input of your data.

    2. Add the Form Calculation widget to fetch the value you need for each condition.

    3. In your "Conditions Wizard", choose "Update a form field value".

    4. I will then set the rules that will be based on the lookup table that you have.

     

    I hope this helps.

    Kind regards.