Update field value from filtered DP using relationship picker

[LMPexplorer]

Checklist

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

Hosting

• Self
  • Budibase Version: 2.15.7

Describe the bug Hi ! First of all, sorry if I am doing doing something wrong as I am new to Github and it is my first report. Here is my issues : I have a form with a relationship picker (only one item can be picked). My goal is to filter a Data Provider using the field value of my relationship picker. And using the filtered data provider, to update a field (TRS) on my form with a value of another field of the row corresponding. Unfortunately, when i do it, the DP isn't always filtered nicely, sometimes the value doesn't change when i change item. Moreover the TRS field isn't automatically taking the 'Cost' value of the filtered row, it is one step behind. Concretely the behavior is weird and doesn't do the same each time i pick an item in my relationship picker.

To Reproduce Steps to reproduce the behavior: On postgreSQL DB

1. Create a table 'RSS' with two column 'Name' and 'Cost' (=Cout_journalier in my screenshot), with 3 row : Person1,Person2,Person3 and cost : 300,310,250
2. Create another table 'Renta' with one column 'TRS' and add a relationship one RSS to many Renta
3. On design, create a form (Renta / UPDATE) with a relationship picker on RSS and a number field (TRS)
4. Create the actions given in screenshot on the relationship picker ON CHANGE actions
5. Create a Data Provider for RSS with the filter given in screenshot and nest the TRS field
6. Then try on prod to pick different item on the relationship picker

Expected behavior When an item is chosen on the relationship, the TRS field should automatically take the value of the cost corresponding of the row displayed by the data provider

Screenshots

https://github.com/Budibase/budibase/assets/158468320/958bfb65-4826-42b1-bce4-18468bcbba16

App Export Can't get the PostgreSQL DB for now so i can't recreate the issues on a new app

Thanks you for your help ! I hope i am clear enough.

[linear[bot]]

BUDI-7968 Update field value from filtered DP using relationship picker

[melohagan]

Going to close this one off because of this:

1. Create a table 'RSS' with two column 'Name' and 'Cost' (=Cout_journalier in my screenshot), with 3 row : Person1,Person2,Person3 and cost : 300,310,250
2. Create another table 'Renta' with one column 'TRS' and add a relationship one RSS to many Renta

If you want to define relationships in Budibase for SQL tables, those tables need to have a legitimate relationship. You need to add a primary key + foreign key.