Return multiple lookup values in form


Badge +6

I have a list named Network Carrier Master with the following columns: Country, Network, Current Carrier, Current Rate

I also have a form with similar fields: Country, Network, Current Carrier, Current Rate. The first 2 fields (Country and Network) are lookup controls and they work as I expect (the lookup lists are separate from the above list). Now I want the other 2 fields; Current Carrier and Current Rate to work such that once the user has selected Country and Network, a lookup function will search the above list and return Current Carrier and Current Rate. In other words

SELECT Current Carrier, Current Rate

FROM "Network Carrier Master"

WHERE Country ="NamedControl_Country" && Network = "NameControl_Network"

I tried using Lookup function but did not return desired results. Here's a snippet of my form

205955_pastedImage_9.png


4 replies

Userlevel 5
Badge +14

you can achieve that with cascading lookups

roughly it might look like

- place a lookup control on form and connect it to Contries list (name it eg CountryCtrl)

- drag&drop network field control from 'Network carrier master' list on form. configure it to be filtered by CountryCtrl control. name it eg. NetworkCtrl

- place calculated value control and configure its formula like:

lookup('Network carrier master','ID',parseLookup(NetworkCtrl,false),'CurrentCarrier')

- place another calculated value control and configure its formula similarly to get current rate value

see these examples how to get cascading dropdowns working.

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

How to filter a look up column in nintex forms 

Badge +6

This is the list i'm trying to lookup the values from

206028_pastedImage_1.png

206032_pastedImage_7.png

And my lookup formula

lookup("Call Rerouting Master", "ID", Country, "Current Carrier")

When I preview the form, nothing is displayed on the field (Calculated value)

206031_pastedImage_6.png

I also tried your suggested formula but still shows nothing

lookup('Call Rerouting Master','ID',parseLookup(Country,false),'Current Carrier')

Userlevel 5
Badge +14

I understood from above country and network fields are of lookup type in list.

with this setup it is not going going to work with cascaded lookup.

if you have to stick with country and network to be text fields, then I'd recommend to create calculated field in the list that will concatenate these two fields into one, eg. "South Africa_MTN". name it eg. CountryNetwork

then create calculated value control on the form that will do just the same with lookup selections, like

parseLookup(CountryCtrl,true) + '_' + parseLookup(NetworkCtrl,true)

name it eg. CountryNetworkCtrl

finally, add calculated value control with a formula like

lookup('Call Rerouting Master','CoutryNetwork',CountryNetworkCtrl,'Current Carrier')‍‍‍

to get carrier and rate values

however, using lookup fields would be much more robust and effective solution.

if you still have chance to change the design I'd recommend to implement it this way.

Badge +6

This worked! Thanks

Reply