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
Solved! Go to Solution.
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.
This is the list i'm trying to lookup the values from
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)
I also tried your suggested formula but still shows nothing
lookup('Call Rerouting Master','ID',parseLookup(Country,false),'Current Carrier')
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.