Dropdown from values of a database field

Question

I have an issue related to the data filtering. I have a Google Drive table to store data, and I want to show one field of this data source in a dropdown to make a filter by this field (Country).

The problem is that this dropdown filter it's only showing the countries that appears on the current page of the list. For example, if in the first page appears one country (Thailand) on the dropdown I'll only see Thailand.

If we move to the second page of the list we have another two countries (Spain and Portugal) and then the dropdown will only show Spain and Portugal. What I really want is a dropdown which shows all the countries, no matter if they aren't on the current page, but I don't know how to fix it.

This the the configuration of the Country Selector:

In the help, it's said we should use @datasource.model.fields.COUNTRY.possibleValues, but if I use this paramater as Options, nothing is displayed in the selector.

I have spend a lot of hours trying to fix this issue and I don't find the solution, and I would like to check with you if it's an issue or I'm doing something wrong...

Could you help me?

score 5 · Accepted Answer · answered Feb 22 '18 at 17:46

You are using the same datasource for your dropdown and table and by #distinct()#sort() you are filtering items that are already loaded to browser (opposed to the whole dataset stored in database).

You need to have a separate datasource for your dropdown. There are at least three techniques to do this:

Possible values

You can predefine allowed values for your Country field and use them to populate drop down options both in create form and table filtering @datasource.model.fields.Country.possibleValues as you mentioned in question:

Create model for countries

By introducing dedicated related model for countries you can get the following benefits:

normalized data (you will not store the same country multiple times)
you'll be able to keep your countries list clean (with current approach there is possibility to have the same country with different spellings like 'US', 'USA', 'United State', etc)
app users when they create new records will be able to choose the country they need from dropdown (opposed to error prone typing it every time for all new records).
your dropdown bindings will be as simple as these:

// for names
@datasources.Countries.items..Names

// for options
@datasources.Countries.items.._key

// for value
@datasource.query.filters.Country._key._equals

Create Calculated Model

With Calculated Model you'll be able to squeeze unique country values from your table. You server query script can look similar to this:

function getUniqueCountries_() {
  var consumptions = app.models.Consumption.newQuery().run();
  var countries = [];

  consumptions.reduce(function (allCountries, consumption) {
    if (!allCountries[consumption.Country]) {
      var country = app.models.CountryCalc.newRecord();
      country.Name = consumption.Country;
      countries.push(country);
      allCountries[consumption.Country] = true;
    }
  }, {});

  return countries;
}

However with growth of your Consumption table it can give you significant performance overhead. In this case I would rather look into direction of Cloud SQL and Calculated SQL model.

Note:

I gave a pretty broad answer that also covers similar situations when number of field options can be unlimited (opposed to limited countries number).

Dropdown from values of a database field

1 Answers1