Forum Discussion

ArchiveUser's avatar
ArchiveUser
Qrew Captain
8 years ago

How to combine multiple rows from a child table with a row from a parent table

I have 2 tables in my app, Projects & Events. Projects is the parent table and Events the child which contains multiple events for each Project.

I need a report or table which contains 1 row for each project along with all of the associated fields / value from the child table also. We've just moved from an access database so essential we were creating union queries to perform this. Is this possible in Quickbase?

  • You will need to make the report of the Events off the Events table and then decide if you want to just sort by project or sort and group by project. 
  • Thanks for the reply. Agreed that's how I have the report set-up currently and I have it grouped by project but that gives me a project with a long list of events, I'd like to have all the events on one line so they can then be filtered easily
  • If you have a limited number of events for each project and you just want to float up say the event name of the first 5 events up to the project, there is a way to do that using reverse relationships. Is that what you are after?
  • Her is an answer I posted to a similar question

    Reverse Relationship setup

    The way to do this is with as many "reverse relationships" as you guess will be enough to cover off 99% if the situation.

    So, let talk about Operators. One Job has many Operators. Make a summary field on that relationship of the Minimum of the Record ID# of Operators. Call it [Record ID# of Operator 1].
    Then do a lookup of that down to the child Operator table as we will need it later.

    Then do a new Relationship where one Operator has many jobs. For the reference field (the field on the right side), use that new Summary field called [Record ID of Operator 1]. Get rid of the extra fields that get created on the left side of the relationship (Add Operator and that report link field). Do a lookup from the Operator down to Jobs and bring in the Operator Name and call it Operator 1.

    Wonderful, the Job now knows the first Operator.

    Now, make a duplicate of that summary field, but in the duplicate add the extra condition that the [record ID#] is greater than the [Record ID# of Operator 1]. Call it [Record ID# of Operator 2]. Do a lookup of that field back down to the Child Operator table as we will need it later.

    Now, in making that duplicate, conveniently, you will find that it duplicated the reverse relationship so now do a lookup in that relationship of the operator name called it [Operator 2]. Wonderful again, we now have Operator #2's name on the Job Record.

    Then make another duplicate of that summary field and just keep going, each time changing the filter so that the record ID# is greater than the previously highest record ID of Operator.

    In the end say you collect the names of the first 4 operators on a job, so then just string them together in a formula like

    List ("\n",
    [Operator 1],
    [Operator 2],
    [Operator 3],
    [Operator 4])

    and put that on the Jobs report