Cannot apply filter to picker (cascading dropdown filter doesn't work)

[sdsys-ch]

Hi,

not posting as Issue yet because I might misunderstand sth here.

External postgres DB and running self-hosted docker with budibase version 2.32.0.

I came upon this when trying to filter a form field based on the value of another form field in the same multi-step form:

I wanted to filter the birthplace based on the value of the birth country. Both are many-to-one rels: country of birth goes to countries(id) and place of birth to cities(id). Since cities contain +150K entries, filtering the place of birth based on the previously chosen country makes sense.

This is not possible though, since the form field value is URI encoded (e.g. %5B1%5D where it should be 1 (int)). Recognising a potential array representation I also tried return $("new individual.Fields.country_of_birth")[0] with no solution, though an array wouldn't make sense here since the column is defined as int, not int[].

I've recreated a new budibase app with a new database for testing purposes. Created the database and content with two tables, simple many-to-one rel via foreign key in people to countries(id). Defined relation in postgres and created one screen with a form to add a new entry. Then edit the action for the on change property of birth country to prompt the user with the binding of the form value. Same result. Also tried triple binding {{{ Field Value }}} to no avail.

I'm attaching the poc app export here.

poc_rel_form_value-export-1726300041467.tar.gz

And here's the sql for the tables:

CREATE TABLE countries (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code CHAR(2) NOT NULL,
    continent VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create the people table
CREATE TABLE people (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    date_of_birth DATE,
    country_of_birth INTEGER REFERENCES countries(id),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Grant privileges on tables to the user
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO buditest;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO buditest;

-- Insert sample data into countries table
INSERT INTO countries (name, code, continent) VALUES
('United States', 'US', 'North America'),
('Canada', 'CA', 'North America'),
('United Kingdom', 'GB', 'Europe'),
('France', 'FR', 'Europe'),
('Japan', 'JP', 'Asia');

-- Insert sample data into people table
INSERT INTO people (first_name, last_name, date_of_birth, country_of_birth, email) VALUES
('John', 'Doe', '1990-05-15', 1, '[email protected]'),
('Jane', 'Smith', '1985-08-22', 2, '[email protected]'),
('Alice', 'Johnson', '1992-03-10', 3, '[email protected]'),
('Pierre', 'Dubois', '1988-11-30', 4, '[email protected]'),
('Yuki', 'Tanaka', '1995-07-07', 5, '[email protected]');

There are some similar issues around.

The workarounds for some don't make sense to me, though. And if there was a fix, it doesn't seem to catch? Maybe the recent sql relationship code update re-introduced something or is this unrelated?

EDIT: Ok, I think I can't update the second form field via a filter on change of the first. The filter is only applied on form loading apparently. I've managed to set the filter value correctly like so:

function convert(fieldValue) {
  let result = String(fieldValue);
  
  // remove '%5B', '%5D', '["', and '"]' and cast to int
  result = result.replace(/%5B/g, '')
    .replace(/%5D/g, '')
    .replace(/\["/g, '')
    .replace(/"]/g, '');
  
  return parseInt(result, 10);
}

const value = $("new individual.Fields.country_of_birth");
const countryId = convert(value);
return countryId;

but it doesn't filter 'after the fact'. Will have to look into building the form differently for this to work.

The gist of the question remains though: Why doesn't $("new individual.Fields.country_of_birth") return an int vs URI-encoded array string?
/EDIT

I appreciate any input before posting this as an issue.
Thanks!

[sdsys-ch]

Ok, after reading up on some of the relationship picker docs, I found that

(cf. docs)

That makes sense for the array representation of the returned value but why is it a string and not iterable in my case?

Also, trying to get a deeper understanding of this, I imported the app export from the Cascading dropdown filters tutorial. The "Try it out!" doesn't work for me, nor does importing the example app locally ('Cities' doesn't filter based on 'Country' value).

So either something is broken or there's something seriously wrong with my setup?

For completeness:

{
  "browser": {
    "language": "en-GB",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/128.0.0.0 Safari/537.36",
    "platform": "MacIntel",
    "vendor": "Google Inc."
  },
  "server": {
    "budibaseVersion": "2.32.0",
    "hosting": "docker-compose",
    "nodeVersion": "v20.17.0",
    "platform": "linux",
    "cpuArch": "x64",
    "cpuCores": 2,
    "cpuInfo": "Intel(R) Xeon(R) Silver 4110 CPU @ 2.10GHz",
    "totalMemory": "3.7916183471679688GB",
    "uptime": "235 day(s), 17 hour(s), 59 minute(s)"
  }
}

with postgres:15.5 running behind caddy proxy.

[sdsys-ch]

After working on something else for a while, coming back to this I can't figure out how to make this work.

Maybe my previous messages weren't very clear and if yes, I'm sorry for that.

I'm simply trying to filter one picker based on the value of another. At first, I tried with a multi-step form block in which two fields (Country, City) come from a postgres db. In postgres, the columns are foreign keys (one-to-many) pointing at countries(id) and cities(id) respectively. The relationship has been created in each of the tables within Budibase:

and the cities-table has a column country_id (that itself is a foreign key to countries(id), a relationship that isn't encoded in budibase but where I don't believe this should matter?) that can be used for filtering.

When setting a filter on the City field:

every time the Country picker's value changes, it should only show cities where the filter applies, correct?

And that's what isn't working (in preview or published).

Since it's a multi-step-form, I've tried different approaches but can't get it to work unless I use two nested data providers in a form and two option pickers (one for countries, the other for cities). The data provider then gets filtered based on the value of the Country picker's field value and onChange-actions on the country field reset the cities value as well as refresh the city data provider.

I've also followed the Cascading dropdown filters Guide to the letter and for me it doesn't work.

While debugging this a little, the value from the Country field is being used in the javascript filter expression (confirmed typeof and value via console.log()) and the filter's POST-request to the search API endpoint is triggered for each onChange-event, e.g.:

POST to https://budibase.domain.tld/api/ta_ff9231d8b99c44e3b88d5c2e584993a2/search with json payload

{"query":{"string":{},"fuzzy":{},"range":{},"equal":{"1:country_id":30},"notEqual":{},"empty":{},"notEmpty":{},"contains":{},"notContains":{},"oneOf":{},"containsAny":{}},"bookmark":null,"limit":100,"sort":"city","sortOrder":"ascending","sortType":"string","paginate":true}

results in correct response:

{
    "rows": [
        {
            "_id": "ro_ta_ff9231d8b99c44e3b88d5c2e584993a2_7a5c32219b87492d9889220896b62af4",
            "_rev": "1-006aa83690b1ae6037cfd6e83043f50e",
            "city_id": 530,
            "city": "Tartu",
            "country_id": 30,
            "tableId": "ta_ff9231d8b99c44e3b88d5c2e584993a2",
            "Country": [
                {
                    "_id": "ro_ta_faccfeaf58a449cc934d18f79075eed6_dd921b75956749a89d26ccc7dda2c44a",
                    "primaryDisplay": "Estonia"
                }
            ]
        }
    ],
    "totalRows": 1,
    "bookmark": "g2wAAAABaANkABFjb3VjaGRiQDEyNy4wLjAuMWwAAAACYQBif____2psAAAAAm0AAAAFdGFydHVhnGpq",
    "hasNextPage": false
}

But the picker never gets reset/ updated with the correct select options, instead showing all cities.

I could also observe that on page load, the field value for Country is NaN but gets set correctly upon first selection.

Also: The filter action seems to be called twice for every onChange-event (at least the javascript function is called twice as per console.log()-output) as is the search-endpoint:

1st call payload:

{"query":{"string":{},"fuzzy":{},"range":{},"equal":{"1:country_id":17},"notEqual":{},"empty":{},"notEmpty":{},"contains":{},"notContains":{},"oneOf":{},"containsAny":{}},"bookmark":null,"limit":100,"sort":"city","sortOrder":"ascending","sortType":"string","paginate":true}

2nd call payload:

{"query":{"string":{"999:city":null},"fuzzy":{},"range":{},"equal":{"1:country_id":17},"notEqual":{},"empty":{},"notEmpty":{},"contains":{},"notContains":{},"oneOf":{},"containsAny":{}},"bookmark":null,"limit":100,"sort":"city","sortOrder":"ascending","sortType":"string","paginate":true}

I've tried to make this a little clearer, hoping that someone can shed some light on this.

In the meantime, I would appreciate if anyone could try the example app from the guide and let me know if it works for them. I would really appreciate.

Thank you!

[sdsys-ch]

Ok, this has been known since 2024-06-09 and is an open bug. Closing and monitoring.

[sdsys-ch]

This has been fixed for me with #13893 and tutorial Cascading dropdown filters is working as expected. Thanks!