Forum Discussion

AndrewS's avatar
AndrewS
Qrew Trainee
3 years ago

FULL JOIN 2

My firs topic wasn't resolved, so will try to provide more info about my Tables and Relationships:

'Budgets' has many 'Budget lines' (connected thru 'Estimate' field)
'Estimates' has many 'Budgets' (connected thru 'Estimate' field)
'Estimate Lines' has 'Estimate ID' and could be connected to the 'Budgets Table',
but
'PO Lines' does not has 'Estimate ID'

'Categories' has many 'Estimate Lines' and 'PO lines' (to assign the category)

so, I would like to have Summary and Chart report that will include data from 'Estimate Lines' and 'PO Lines' tables.

'Estimate Lines' and 'PO Lines' tables synced to the QuickBooks

will appreciate any help...

------------------------------
Andrew S
------------------------------
Relationships
reports and charts

4 Replies

Sort By
  • MichaelTamoush's avatar
    MichaelTamoush
    Qrew Captain
    3 years ago
    Not sure about your particular scenario, but in the past when I was summarizing data from two tables, I would do it in the following way.

    1. Come up with a field to summarize on. For example, in my use case I had Payments and Receivables, each with a specific date. I wanted to summarize using the date field so, that is what i mean by come up with a field to summarize on.

    2. Using my example, I wanted to summarize by month. So I created a new table, but changed the key field to one called MonthYear. Then I manually created a bunch of records (012021,022021,032021...etc one for every possible month/year for the next decade or so.)

    3. On both child tables, created a formula field that extracted the month and year from the date I was interested in.

    4. Create a relationship, MonthYear Table has many Estimate Lines. Month Year Table has many PO Lines. Your related field you specifically choose your month/year formula in the child table. Now you can create summary fields on the parent to look at the data from both tables.

    Not sure if this is what you were getting at though. If it is, you can use anything to summarize. Maybe your child table has salespeople assigned to each record. Your parent key would be everyone sales persons name, and you would relate to the name on the child.

    ------------------------------
    Mike Tamoush
    ------------------------------
    • AndrewS's avatar
      AndrewS
      Qrew Trainee
      3 years ago
      Thanks for the Reply Mike,
      but my child tables don't have equal ID's, actually this is a reason, why I try to use FULL JOIN.

      In the SQL full join means

      table 1 fields A B C

      table 2 fields D E F

      FULL JOIN TABLE 3 WILL HAVE FIELDS A B C D E F




      ------------------------------
      Andrew S
      ------------------------------
      • BlakeHarrison's avatar
        BlakeHarrison
        Qrew Captain
        3 years ago
        In Quickbase, reports are generated on a single table, though you can use the API to query different tables and combine that information into a single report using a Code Page. For a standard report in Quickbase, you will have to create a 3rd table where you can aggregate those records.

        ------------------------------
        Blake Harrison
        bharrison@datablender.io
        DataBlender - Quickbase Solution Provider
        Atlanta GA
        404.800.1702 / http://datablender.io/
        ------------------------------