Forum Discussion

JamesCarr's avatar
JamesCarr
Qrew Trainee
2 months ago

Merging Two Completely Different Tables

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.

reports
  • DonLarson's avatar
    DonLarson
    Qrew Elite
    2 months ago

    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?

  • JamesCarr's avatar
    JamesCarr
    Qrew Trainee
    2 months ago

    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.

  • JamesCarr's avatar
    JamesCarr
    Qrew Trainee
    2 months ago

    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.

    • DonLarson's avatar
      DonLarson
      Qrew Elite
      2 months ago

      You will have two records, the first and last, that are not related to a Period to match your format.

      • JamesCarr's avatar
        JamesCarr
        Qrew Trainee
        2 months ago

        In your example, is there a way to auto populate all of the related projects and then populate the related periods for each project, or does that have to be done manually?  

  • DonLarson's avatar
    DonLarson
    Qrew Elite
    2 months ago

    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.

    • JamesCarr's avatar
      JamesCarr
      Qrew Trainee
      2 months ago

      Unfortunately, Pipelines are off the table, otherwise, I believe I would be good to go.  I don't have any background on the periods but they are not consistent. Period1 is for 1 month, then the next period is the rest of the year, then the next 2 periods cover a year each, but then the next two periods each cover half a year (6 whole months). 

      Definitely complicated but, turns out the output just has to have the designated details but does not need to look exactly like that. The only thing I can think of is try to use JavaScript to loop through the records and generate everything but, I am not that skilled with it yet. I would love some suggestions, if you have any. 

      • DonLarson's avatar
        DonLarson
        Qrew Elite
        2 months ago

        Those are definitely not standard periods.  When the Project is created, you will need to create them for each Project. To use JS you will have to build a Code Page.  QB will not let you put it into any of the fields in the tables any more.

        Why not Pipelines?   They are expensive to use for external integrations but they are free (at the moment) for QB to QB.  

        You could have the Project Manager create the Periods manually, but that might result in a User revolt.

  • JamesCarr's avatar
    JamesCarr
    Qrew Trainee
    2 months ago

    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.