Skip to main content
Question

Merging Two Completely Different Tables

  • December 4, 2024
  • 0 replies
  • 312 views
Forum|alt.badge.img+9

Greetings,

I couldn't delete this post so, I am just completely modifying it, for anyone who navigated their way back to this and is thinking, "This isn't what I was looking for".  lol  

I have two tables with completely different data.  I want to create a third table which would be made up of the contents of each record in table1, duplicated for each record in table2, so I can add additional formulas, etc for a report.

table1 structure 
Name -- StartDate -- FinishDate -- Amount

table2 structure
Period -- BeginDate -- EndDate

Desired merger
Name -- StartDate -- FinishDate -- Amount -- Period -- BeginDate -- EndDate --...

Example output
Name1 -- StartDate -- FinishDate -- Amount -- Period1 -- BeginDate -- EndDate --...
Name1 -- StartDate -- FinishDate -- Amount -- Period2 -- BeginDate -- EndDate --...
Name1 -- StartDate -- FinishDate -- Amount -- Period3 -- BeginDate -- EndDate --...
Name2 -- StartDate -- FinishDate -- Amount -- Period1 -- BeginDate -- EndDate --...
Name2 -- StartDate -- FinishDate -- Amount -- Period2 -- BeginDate -- EndDate --...
Name2 -- StartDate -- FinishDate -- Amount -- Period3 -- BeginDate -- EndDate --...
etc...

The true challenge with this is being able to set this up so the merger of the data in this fashion happens dynamically.  Meaning when one or both tables has a record added, modified, or removed, the merged output reflects the changes.  I figure this calls for a relationship, especially for the dynamic aspect of things.  However, I don't know how to set that up, assuming it's possible to do.  If anyone has any suggestions, that would be greatly appreciated.  Thank you in advance.

Forum|alt.badge.img+15
  • DonLarson
  • Quickbase Alumni
  • December 5, 2024

Are you really merging data or does a relationship between the two tables solve your issue?

For example you can have a Project Table and Group Table

In between them you put a Project Groups Table.   

 

 

The information from Projects and Groups is used in the third table but is not actually merged into.   Changing a Project Name automatically updates the Projected Groups related to the Project.

Will that do what you need?

Forum|alt.badge.img+9
  • JamesCarr
  • Author
  • Quickbase Alumni
  • December 5, 2024

Thanks for responding.

The 1st table shows a total amount accumulated over the start and finish date for that record.  The 2nd table shows periods of time that the accumulated amounts may or may not correspond to.  I want to show that Name1 has amounts accumulated before period1 started as well as some accumulated during period1, etc.  I realized after I made this post that my current idea doesn't account for overlaps before the first period begins or after the last period ends.  My example output is what I am going for as a result of putting the data together.

Forum|alt.badge.img+9
  • JamesCarr
  • Author
  • Quickbase Alumni
  • December 5, 2024

Here's a sample output to reflect where I am trying to ultimately get to:

Name1 -- Before Period1 -- StartDate -- Period1 BeginDate-1 -- Amount
Name1 -- Period1 -- BeginDate -- EndDate -- Amount
Name1 -- Period2 -- BeginDate -- FinishDate -- Amount
Name2 -- Period3 -- BeginDate -- FinishDate -- Amount
Name3 -- Period7 -- BeginDate -- EndDate -- Amount
Name3 -- After Period 7 -- Period7 EndDate+1 -- FinishDate -- Amount

Again, StartDate and FinishDate are table1 dates, while BeginDate and EndDate are table2 dates.

Forum|alt.badge.img+15
  • DonLarson
  • Quickbase Alumni
  • December 5, 2024

This should do it

 

Forum|alt.badge.img+15
  • DonLarson
  • Quickbase Alumni
  • December 9, 2024

What are the business rules?   Pipelines are very good at for this sort of simple Many to Many relationships.

Do the Periods all exist prior to the Project creation?  If your Periods are calendar months this is straightforward.   If your Periods are unique to the Project, something like stages of varying lengths this is more complicated.

Forum|alt.badge.img+9
  • JamesCarr
  • Author
  • Quickbase Alumni
  • December 10, 2024

I don't understand why Pipelines are not an option. The data is in QB tables, though. 

I know code pages would be needed for the JS approach. Sounds like code page it is. Thank you for your input. I have been wanting to get some hands on experience with using the API calls in a code page. Time to do some learning. 

Terms & ConditionsAccessibility statement