Purpose
Select rows of a table variable according to user input

Solution
We use the match= option of the ReadRow  command.

The example also shows

  • how to build up a dropdown list dynamically
  • how to read the current table row in an InputScript started from a pushbutton
  • how to embed a web page into the screen where the URL is based on the selected table row 

Example
We start from a .csv file containing a list of cities, with country, population and geo coordinates (same as in previous tip "Reading a .csv file":

The user selects a country in a dropdown list showing all countries contained in the .csv file. We then display the cities belonging to this country:

 

In a second dropdown list  "Order by" you can choose the ordering by city name, population or "North to South", here shown for further countries:


Ordered by population



Ordered from Notth to South

When we click on a "city" button, a web page is shown right hand side of our table which displays the current flights via the flight tracking service "flightradar24.com":


Click to enlarge

Video

 

Show video in full screen

GuiXT Script

// create table variable and load content from .csv file
if not V[allcities.rowcount]
  Enter process="cities.initialize.txt"
  Stop
endif

// country selection dropdwon list
DropDownList (2.3,4.1) "allcountries" width=53.7 refer="V[selcountry]" _
 
-noSort process="cities.select.txt"

// display city table
Table (4,4) (30,83) name="cities" fixedColumns=6 _
 
title="&V[cities.rowcount] cities"

Column "CC" size=3 name="countrycode" -readOnly
Column "City" size=20 name="city" -pushButton _
 
process="cities.showflights.txt"
Column "Country" size=16 name="country" -readOnly
Column "Population" size=12 name="population" -readOnly
Column "Latitude" size=10 name="latitude" -readOnly
Column "Longitude" size=10 name="longitude" -readOnly

// display flight tracking control if activatesd
if V[citiesShowflights]
 
Control (3.7,84.9) (28.4,169.9) _
    
progID="&V[citiesShowfligthsURL]" _
    
name="flightradar" -silent -closeOnHide
endif

// "order by" drop down list
Text (30,3) "Order by" size=16 -label
DropDownList (30.1,20.2) "orderoptions" width=30 _
 
refer="V[orderby]" -noSort _
 
process=
"cities.sort.txt"

InputScript "cities.initialize.txt"

// initialize city display

// create structure table variables
CreateStructure V[cityinfo] city latitude longitude country countrycode population

// all cities from .csv file
CreateTable V[allcities] include=V[cityinfo]

// cities seleced by user (a paricular country or *=all)
CreateTable V[cities] include=V[cityinfo]

// load content from .csv file
CopyText fromFile="C:\temp\cities.csv" toText="cities"
CopyText fromText="cities" toTable=V[allcities] delimiter=";"

// sort
Sort V[allcities] orderBy="countrycode,city"

// build country selection

// 1st entry
Set V[line] " =Select a country"
CopyText fromString="line" toText="allcountries"

// 2nd entry: all countries
Set V[line] "*=* All countries"
CopyText fromString="line" toText="allcountries" -appendLine

// build up country dropdown
Clear V[countrycode]

Set V[k] 1
label next_city
if not V[k>&V[allcities.rowcount]]

  // new countrycode?
  if not V[countrycode=&V[allcities.countrycode.&V[k]]]
   
Set V[countrycode] "&V[allcities.countrycode.&V[k]]"
    Set V[line] "&V[countrycode]=&V[countrycode] &V[allcities.country.&V[k]]"
    CopyText fromString="line" toText="allcountries" -appendLine
  endif

  Set V[k] &V[k] + 1
 
goto next_city
endif

// drop down list for ordering
Set text[orderoptions] "1=City name;2=Population;3=North to South"

// default ordering: by city name
Set V[orderby] "1"

Return



InputScript "cities.select.txt"

// select cities for a given country
// ---------------------------------

// clear table variable "cities" (selected cities"
Clear V[cities]

// no country selected?
if not V[selcountry]
  return
endif

// structure for city selection (match= option below)
CreateStructure V[matchcity] countrycode

// fill with value to select (country code)
Set V[matchcity.countrycode] "&V[selcountry]"

// select matching entries in "allcities" and append them to "cities"
Set V[k] 1

label next_city

// all cities?
if V[selcountry=*]
  ReadRow V[cityinfo] table=V[allcities] index=&V[k]
else
  // next matching row
  ReadRow V[cityinfo] table=V[allcities] fromIndex=&V[k] returnindex="k" match=V[matchcity]
endif

// matching row found? then append it to "cities"
if Q[ok]

  // some value formatting
  Set V[cityinfo.population] "&V[cityinfo.population]" + 0 groupSeparator="."
  Set V[cityinfo.latitude] "&V[cityinfo.latitude]" / 10000 decimals=2
 
Set V[cityinfo.longitude] "&V[cityinfo.longitude]" / 10000 decimals=2

  AppendRow V[cityinfo] table=V[cities]

  Set V[k] &V[k] + 1
  
goto next_city

endif

// Now order the result set according to the selected order type 1,2,3
if V[orderBy=1]
  Sort V[cities] orderBy="city"
endif

if V[orderBy=2]
  Sort V[cities] orderBy="population" -descending
endif

if V[orderBy=3]
  Sort V[cities] orderBy="latitude" -descending
endif

// scroll table to top
Set V[cities.stat.firstvisiblerow] 1

// no "flight traffic" display
Clear V[citiesShowflights]

Return

 

InputScript "cities.sort.txt"

// order cities

if V[orderBy=1]
  Sort V[cities] orderBy="city"
endif

if V[orderBy=2]
  Sort V[cities] orderBy="population" -descending
endif

if V[orderBy=3]
  Sort V[cities] orderBy="latitude" -descending
endif

// scroll table to top
Set V[cities.stat.firstvisiblerow] 1

Return

 

InputScript "cities.showflights.txt"

// flight display on
Set V[citiesShowflights] "X"

// Set coordinates
// we need to reformat it since the web page needs a decimal point, not comma
Set V[citiesLatitude] &V[cities.latitude.&V[_tabrowabs]] * 100
Set V[citiesLongitude] &V[cities.longitude.&V[_tabrowabs]] * 100

Set V[citiesLatitude] &V[cities.latitude.&V[_tabrowabs]] / 100 decimals=2 decimalSeparator="."
Set V[citiesLongitude] &V[cities.longitude.&V[_tabrowabs]] / 100 decimals=2 decimalSeparator="."

// set flightradar24-URL
Set V[citiesShowfligthsURL] "https://www.flightradar24.com/&V[citiesLatitude],&V[citiesLongitude]/10"

Return 

Components
InputAssistant + Controls