Problem when applying a filter in a grid field displaying related data
Hello,
I hope you can help me.
I have a gried Field with a record Type as the data source (record A), which at the same time is based on a SQL table. Every row has a unique value of its primary key (request_id).
Some of the columns of the grid are displaying values which are not from the main source record, but from another record with a one-to-many relationship.
This second record (record B) is also based on a SQL table. The relationship between them is through request_id. Because of this, I have included record B within the "relatedRecordData" parameter of the "a!gridField". I have stablished as well a limit of 1, and a descending sort field based on the date. This way, for every a_id, I only display the value of the latest row in record B (even if there are more rows with different values).This is working completely find.
My problem comes when I try to add filters within "a!recordData" > "filters" .
The moment I add a filter that affects a field which is from record B, it also shows matches with values from rows that are not the last ones (the ones I am displaying).
For example; I have a column called "status" with different values such as "Started", "Completed" or"Canceled". If I select "Completed" in my multiple dropdown field, it displays rows which visible status is different to "Started", because in the record B, some of the rows for the same request_id have that particular status. This is wrong as I just want the filter to apply to the latest row of record B.
If the data from the record B table looks like this:
| request_id | status | created_date |
| 1 | completed | today |
| 1 | started | 1 day ago |
| 2 | canceled | today |
| 2 | completed | 1 days ago |
| 2 | started | 2 days ago |
| 3 | started | today |
For each request_id, the grid displays just 1 (the one with the most recent date):
| request_id | status | created_date |
| 1 | completed | today |
| 2 | canceled | today |
| 3 | started | today |
but, if i apply the filter status= completed, it displays :
| request_id | status | created_date |
| 1 | completed | today |
| 2 | canceled | today |
This is because reques_id also has status = "completed" in ones of its rows of the the record B table, but this is not what I want. It should only filter those that have status = "completed" in the very last row;
| request_id | status | created_date |
| 1 | completed | today |
I have to add that I have also try to add the filters in the "relatedRecordData" section, but it also did not fix the problem.
What am I missing? It cannot be that an out of the box function as the grid Field with records as a source, and the filters of it are not working as intended...
Thanks for any help.