USING A FORMULA FIELD TO DISPLAY CONTINENTS FROM A NATIONS CHOICE FIELD
Does anyone have an easy way of doing this?
Basically I want to allocate each of the 220 nations on my choice field to one of four continents. So if the NATION is France, the formula field shows EUROPE.
All I can think of is an impossibly complex if-then expression. There has to be something more elegant?
I've used SWITCH on equivalent (but shorter) choice lists, but the case numbers don't seem to correspond to the numbers of the values on the list, and I ended up doing it by trial and error. With 220 values that's going to be difficult.
13 replies
-
Why a choice field? Within minutes.. I imported a list of countries by Continent from here..
Then created a dashboard table that had a text box and a view .. and searched countries table by the entered country name..
If you do not want them to "type".. then create a "Search Countries" relationship between Dashboard and Countries.. and let them pick it.
-
Thanks, Michael. I already have a list of countries (this is related to other corporate databases and has specific features including spellings and country vs. territory etc., so I'm stuck with that). I COULD go through the list and paste in EUROPE after all the European countries etc. but that's a bit clunky and I'm looking for a short cut. It's a choice field because the country of every leader we train is a value on his/her profile. So I'm working inside an existing dashboard (LEADERS). Does that make sense?
-
@David... It does make sense. You can still look up the value .. you just need to ensure you have a look up value.. like the country name being spelled the same... or add a hidden country coude field.. etc.... While it will take you a bit of effort.. it may be less effort than a switch statement.. :)
-
OK, so I have a new table called DATA which contains as much country-related information as I want to add. The plan is to assign each existing leader record to one of the 220 country files (N:1). But there are over 1K leader records now. Is there any way I can save time on this by batch-processing the files? I can select all the "Argentina" leaders, for example, but "Update multiple records" doesn't seem to work for calls.
-
@David.. Sure.. assuming they have something in their existing record.. you can either do a for loop from within the console ... or you can add button that only you as admin can see and put the code behind that button..
-
Thanks, Michael - yes, all the selected records would have the country assigned in a separate choice field. But I have no idea where to start on the coding you refer to. What's a for loop? Or what code would go in the button?
-
@David ... I suggest you join one of the online webinars.. you can then share your screen and get input from a panel of experts.. There are also a few youtube channels and blogs...
Here is a link to the manual..
https://ninoxdb.de/en/manual/calculations/reference-of-functions-and-language
Search for Loops ....
-
Thanks, Michael.
-
@David.. If I get time.. I will validate this.. basically .. Assume table Leader has leaders .. and your choice field 'Home Nation'.. Assume your Nation table has all the data you want on it.. and has a column Name.. that matches the text exactly as listed on Leaders.Nation ... Your one time data update script would look something like (again.. not verified.. but close)
for L in (select leader) do
let n:= select Nation[Name = text(L.'Home Nation');
if n then
L.Nation := n;
end;
end;
-
OK, thanks for that. Let me take a look. My daughter has already done about half the list manually! (Fortunately she's a bit quicker than I am.)
-
The following worked for me..
Leaders table has a text field called Nation..
Countries table has a text filed called Country_name.
In my previous code.. I forgot the "first".. so I only get one record.. not an array of records.
-
ohh.. and there is a relation from Leaders to Countries..
-
Well I would never have got there! Many thanks, Michael.
Content aside
- 4 yrs agoLast active
- 13Replies
- 3623Views