[BUDI-7375] Relationship picker incorrectly renders selections, and not all records show in the dropdown

[kpturner]

Checklist

• I have searched budibase discussions and github issues to check if my issue already exists

Hosting

• Cloud
  • Tenant ID: monito

Describe the bug
A relationship picker does not render selections properly, and does not provide all the available rows for selection

To Reproduce
Created a relationship between two simple tables - country and currency. A country can have multiple currency records associated with it. The data is held in a country_currency table that just contains the primary key of country and the primary key of currency. The data is correctly rendered in the grid in the "data" section of the designer - for example:

But within the application where a relationship picker is used, USD shows as %5B'USD'%5D

Furthermore when you use the picker to choose other currencies, only a handful of the total records appear in the dropdown.

Expected behavior
All selections should be rendered properly and all available records should appear in the relationship picker.

Screenshots
See above

App Export
I don't think I can, because the export would contain the secure credentials to the data source, and without the data source, there would be no data to test with. I am more concerned about what it is about the data that could cause this, as it looks completely normal in the DB and is handled correctly by the "data" section of the designer.

What I can do is simply send the entire postgres schema via email if need be. If it is data related that would help diagnosis.

Desktop (please complete the following information):

• OS: iOS (mac)
• Browser Chrome
• Version 115.0.5790.114 (Official Build) (x86_64)

_{From SyncLinear.com | BUDI-7375}

[kpturner]

But why does the limit cause the data to be rendered incorrectly? Surely that cannot be right?

An application level filter would be no use here. A filter that can be applied by the user might work.

A 100 row limit makes the relationship picker pretty useless in this case. Are there out of the box alternatives?

[melohagan]

Hey @kpturner

I may have mis-understood the issue. Are you talking about the Relationship Picker component in the Design section, or is this issue about selecting relationships in the Data section?

[kpturner]

I'm talking about the relationship picker you'd add as a component to your application(s). That's the one that renders badly. The one that you get by default in the "data" section on a grid seems fine (in terms of what it renders). The issue I am seeing is on a picker that selects currency codes so naturally there will be a lot more than 100, and the one that looks wrong is USD (which will obviously be way down the list past 100)

[melohagan]

Thanks for the clarification @kpturner

Is it possible for you to add a filter to the relationship picker? Something like:

So in this case it is assuming you have selected a country already in a form, and are returning only the currencies that are available in that country. Would that ever be more than 100?

Also if you cannot share the app export, you can share the SQL schema (only metadata about the tables are shared, i.e. column names and types): https://docs.budibase.com/docs/troubleshooting-sql

[kpturner]

The problem is with creating a new country - when you need to select its currencies from an unfiltered list. Admittedly we do not create new countries very often 😂

[kpturner]

Even when editing a country you need to be able to select from the entire list of currencies. The only time I can think of a filter being useful is for display only - but then it's not really a "picker".

[kpturner]

I have emailed the schema dump to team@budibase.com

[kpturner]

@melohagan This seems to have gone quiet and it is becoming a bit of a showstopper. What happened with the schema dump I sent?

[melohagan]

Hey @kpturner Have you tried using an options picker with a data provider source instead? https://docs.budibase.com/docs/option-picker#using-a-data-provider

[kpturner]

Hey @kpturner Have you tried using an options picker with a data provider source instead? https://docs.budibase.com/docs/option-picker#using-a-data-provider

It would have to be a multi-select picker to be useful - but then that does not like the data type for the field.

[melohagan]

Hey @kpturner Have you tried using an options picker with a data provider source instead? https://docs.budibase.com/docs/option-picker#using-a-data-provider

It would have to be a multi-select picker to be useful - but then that does not like the data type for the field.

The multi-select picker has the same ability to use a data provider as its source. You can also always manually enter a field name.
Then when you do your Save Row action you would also Add Column to ensure you save the extra info that was not a part of the schema.

[kpturner]

Yes but to initialise it with existing values I would presumably need to convert the data for the "worker" field first. I will try it. What format does the data need to be in? I will try a comma delimited list.

[kpturner]

Of course this is a pretty horrific work around as the data comes from (and has to be updated to) a relationship table that just has the keys of the two files. It is not just a column in a table that can be sorted out on the save event. Nightmare to fix something that should work anyway.

[melohagan]

Of course this is a pretty horrific work around as the data comes from (and has to be updated to) a relationship table that just has the keys of the two files. It is not just a column in a table that can be sorted out on the save event. Nightmare to fix something that should work anyway.

It's not trivial, but I don't think it'll be a nightmare.

1. Add your data provider, and nest your multi-select.
2. Select the data provider as the options source.
3. Set the label column to the primary display, set the value column to the _id column of the table.

[kpturner]

That bit is the easy bit. When editing an existing country I still have to:
a) Preload the picker with the currencies already selected previously (from the country_currency relationship table)
b) Rewrite the data in the country_currency table when the country row is saved

[melohagan]

Shouldn't be too bad. For part a assuming you have the value column set to _id then you should be able to bind the relationship field to the default value of the multi-select without any transformations required.

Part b, in your Save Row action you will Add Column, select the relationship field, and set the value to your multi-select form field binding.

[kpturner]

I didn't follow that at all
:(

Assume I have a country_currency relationship table and a currency table
I am updating a country table
and I also need to rewrite the country_currency table upon save
Which table loads the picker? Presumably the currency table? So I do not get how it knows which ones to pre-pick?
Or, how it would use that on a save to repopulate the country_currency table.

Would it be possible to document an example with based on the schema I sent?

[melohagan]

Had a look at this, and the Many-Many joining table definitely makes this more difficult.

What's needed is the search functionality that can be found in the relationship picker of the data section. That way even if the options are limited to 100, the search could still be used to scan the whole table.

[kpturner]

I have is successfully loading the picker at least by setting the default value to
return $("Repeater.country.additional_currencies").map((ac)=>ac.primaryDisplay).join(',')
The problem is to find the least hacky way of repopulating country_currency from the result of the users choices.

[kpturner]

Given that the relationship field raw value seems to be something like this:
[{"primaryDisplay":"AWG", "_id":"%5B'AWG'%5D"}, {"primaryDisplay":"EUR", "_id":"%5B'EUR'%5D"}, {"primaryDisplay":"USD", "_id":"%5B'USD'%5D"}]
presumably that is what I have to reset it to on the save to get BB to do its own thing with the relationship table?

[kpturner]

I got this working by creating a worker multi-select field that I initialise (default value) with JavaScript

return $("Repeater.country.additional_currencies") ? $("Repeater.country.additional_currencies").map((ac)=>ac.primaryDisplay).join(',') : ''

Then on the Save Row I update the relationship column additional_currencies with Javascript also (because that way it is an array and not a string)

return $("Form - Update.Fields.additional_currencies_worker")

[melohagan]

As discussed, this does affect the default value for the relationship pickers. This is an auto-generated table block:

You can see that customers are present. Now click on a row:

Customers is null instead of 'Debra' because only 100 rows are selected:

[melohagan]

Having a look now at adding the search, and I do have it selecting an initial value by doing an initial search of the field value. Just working through a few other edge cases for the search as well

[andyburgessmd]

Is there likely to be any new work on this?
I have the issue where in an update form the value shown in Relationship Picker component shows as null as the value its matching is not in the first 100 results from the relationship table.

[melohagan]

Hey @andyburgessmd

Thanks for bringing our attention to this one, I can confirm it to be a regression:

Initial value

After scroll pagination loads the value

[rmeekers]

What's the status on this? With me there are only 20 records showing in the picker and a search only searches in those 20. In order to select the right value, I have to scroll down in the picker in order to load all the records in the picker, and only then I can search for the one I need. This is quite useless off course.

[melohagan]

Try version 2.21.2