Collecting Data from Multiple Child Fields in a Single Field in Their Parent

Collecting Data from Multiple Child Fields in a Single Field in Their Parent

I am not a developer, and I am sort of fumbling my way through using this platform.  There's something that I want to do, which seems like it should be simple, but I haven't been able to figure out an approach.  In a one-to-many relationship, I want to collect data from one field in the children records (let's say its their first name), into a text field of the parent record.
So, I would want a field in the parent record that shows the parent has 3 children -- "Mike, Paul, Tom."  I know that this data would be "display only," and that you couldn't click through to the children, but it would be helpful to see this on one of the screens I am creating.  It occurs to me that this could be done in at least two ways. 1) By updating the field in the parent anytime one of the child records is changed, or 2) through some sort of a query at the Preparation stage of showing the web screen.  I'm also guessing it needs some kind of a "For Each" loop.  However, I've tried both approaches with no luck.   I can't seem to be able to access the data in the child table in a way that lets me copy it into the parent field.  I'm sure I am missing some of the steps here since I really don't understand what is going on behind the scenes. I don't even know which approach is better -- I'm assuming it's cleaner to create the data through a procedure "on the fly," since it's guaranteed to be up to date, but I'm not sure if that willl make the screen take too long to load.

Any help or direction would be much appreciated! Thanks!
Hello Joey

I would go to option 2, definately. On a relationship data model, you want to keep the relations, instead of duplicating the information on "parent" records, a part of some expcetions when performance of million entries is key, and aggreating/duplicating data is essential for best performance. I don't think this is the case here.

So you should need:
  • a simple query, which queries the PARENT and the CHILDREN entities. If they have a relationship, automatica joins will be added to the query when you add both entities. Add an input parameter of PARENT type so you can get the PARENT and their CHILDREN.
  • Add a ForEach cycle after the query, and iterate over the query, and for each CHILDREN.Name, concatenate on a Text variable
  • Show the variable on the screen.
This is as basic as it gets, and assumes you're showing just one PARENT. Depending on the use case you need, one may need to tweak.

Can you share a sample espace with what you're trying to achieve, and we can work from there.


Miguel Simões João

Thanks for the reply.  I am actually showing a list of many "parents", and I want to be able to include a list of their children as one concatenated field in the list.  I think that makes things a little more complicated.  I can easily create the query that joins the parent and children tables and looks something like:

ParentID   ChildID   ParentName    ParentAddress            ParentPhone              ParentPhone
1                  1            Bob                   123 Main Street             555-555-1212              Tina
1                  2            Bob                   123 Main Street             555-555-1212              Sally
2                  3            John                  345 Pine Street             301-123-1212              Robert
2                  4            John                  345 Pine Street             301-123-1212              Doug
2                  5            John                  345 Pine Street             301-123-1212              Annie

But on the screen, I want to display the data as:

ParentName    ParentAddress            ParentPhone           Children
Bob                   123 Main Street             555-555-1212           Tina, Sally
John                  345 Pine Street             301-123-1212           Robert, Doug, Annie

I'm not sure how to create a variable "on the fly" in the query to collect the list of children -- customn SQL?


Hello Joey

Thanks for sharing the details.

As with many things, the number of solutions to this problem are several. I can suggest some of them, each with it's own advantages and disadvantages.

Solution 1: Use a webblock to show the children

Instead of querying the parent and the childrens at once, you can query the parents, and list them on a table records, an in the table record, have a webblock that accepts the parent ID, and queries and displays the childrens in line using a for each cycle and an expression. This is the easiest solution to implement, but not exactly the most performant nor scalable, since it executes a lot of queries (one per parent +1).

Solution 2: Build the parent list with the agregated children in the screen preparation

After querying both the parents and the children, iterate with a foreach cycle the resulting query, and while the parent ID remains, append the child name. When the parent ID changes, use the ListAppend to add the record into a local variable list, that will feed the table records This is a little more complex because it executes the aggregation on the application side in logic, but it's much more performant, since it executes much less queries. The ListAppend will need to use a record type definition of the PARENT entity plus a TEXT structure to hold the aggregated children names.

Solution 3: Aggregate the Children on the database

Change the query to advanced query, and build a query that will automatically aggregate the children names per parent. This is not simple, and it will most likely require using sql cursors. You can use the output directly in the table records, but it will not be very Agile, since maintaining the query will be costly, and it may not bring performance benefits.

Solution 4: Add the children names on the parent entity

This is the solution you've referred, and although it simplifies the query to fetch the data and listed on the table records, it will also increase complexity on the inserts, updates and deletes. Depending on the number of parents and children, this solution might be considered if all others are too slow for a large data set.

In the end, it's always a compromise between performance and maintenance scalability.

Hope this information helps.


Miguel Simões João