I'm trying to display a list of project records and allow users to search that list based on related child records in a one-to-many relationship. When I add the joins in an aggregate multiple of the same project records display in the list. I've seen other suggestions to group one of the columns in the aggregate, but that produces many errors in that block because the list block is expecting a record and now the only thing I can choose in the expression editor is the ID attribute.
Before I tried to be able to search by the child record I was passing GetProjectsByIsActive.List.Current.Projects to the list block. Now I can only select GetProjectsByIsActive.List.Current.Id in the expression editor.
How can I get around this?
Hi @Daniel Johnson ,
I'm afraid you won't be able to get passed the many errors you will have to fix, as the output after grouping is of a different data type, and you will have to fix all expressions on your canvas that refer to the old type.
You only have id available at the moment because you probably only have grouped on that. You will have to group on all columns you are interested in.
Let's say you have a product entity, and you want to show in your list on the screen the product name, category, supplier. Let's say you have a sale entity, with a reference to the product (so a one to many, product to sale) The sale has maybe some information like a reference to the customer , or a reference to the salesperson who closed the sale, and you want to allow your users to search on those details when looking for a product.
So you would have an aggregate with sources product + sale + customer + employee, all joined. Giving you a row for each sale, while what you want is a row for each product.
So you can have a party in the filter section, for example comparing a search string to the customers last name, or to the employees last name, or to the customers email adress,...
But then you would use group by on product.id, product.name, product.category, ... and all other product details you want to show on screen. This will make sure you get one row in the resultset per product instead of per sale.
Dorine
Thanks @Dorine Boudry! The requirements kept expanding, but good to know to make sure to ask about this functionality upfront so I don't wind up here again.
It's working as expected after your suggestions.
Hi,
to create one-to-many, refer to this:
https://success.outsystems.com/documentation/11/developing_an_application/use_data/data_modeling/entity_relationships/create_a_one_to_many_relationship/
to search:
https://www.outsystems.com/forums/discussion/53339/search-filter-by-name-and-surname/
thanks
That's working, but the aggregate, without grouping a column, will show multiple of the same record when it has multiple related children, and then display multiple records in the list. I want it to only display one record, but still be able to search by its children.
U need to filter it then