Forum Discussion

BrettLyon's avatar
BrettLyon
Qrew Trainee
8 years ago

How do I make a parent record present within fields specific summaries of a child record groups?

I have a parent table called Part Numbers. I have a child table called Forecasts.    Forecasts are predictions of how many parts might be ordered for each future month.  Forecasts data can cha...
App builders
Relationships
KevinO_Boyle's avatar
KevinO_Boyle
Qrew Cadet
8 years ago
You might have the right table structure. You want to create a summary field using the max function on record ID to determine the most recent forecast - then push that field back to the child table as a lookup. Create a formula checkbox that compares the lookup field to the record ID; if they equal, you have the most recent forecast record.

You might need 3 tables:
1.Parent-parent where key field is item
2. Parent where key field is YYYYMM|item
3. Child where key field is record ID.

Table 1 is your item master
Table 2 contains the most recent forecast in any given month
Table 3 contains all forecasts

You�ll want to use global app variables to automate the switching between months (since most forecasting processes are either 12 month forward rolling or fiscal year based)

Lastly, you have the alternative of using Quickbase actions for record keeping purposes. Your table 3 would be populated by Quickbase action, and the user would enter data in table 2. (You could avoid the goofy circular lookup method described earlier)

This is high level but I�ve built a boatload of forecasting apps in Quickbase- if this didn�t help, clarify and I�ll see what I can do.
  • AviSikenpore1's avatar
    AviSikenpore1
    Qrew Trainee
    8 years ago
    Kevin is a subject matter expert on forecasting in Quickbase :) 
  • BrettLyon's avatar
    BrettLyon
    Qrew Trainee
    8 years ago
    Thank you so much Kevin, that is almost exactly the direction I was going in - but still no luck unfortunately!  No matter how I seem to arrange things, when I go to create a summary field in Table 2 to summarize Table 3, I get an error that QuickBase does not allow summarizing relationships in which the reference is a lookup field.

    My guess is I am still doing something wrong.  Here is a basic break down to see if it helps.  FYI I use the first day of the month to represent the entire month, seems to make it easier.


    Table 1 - Master - Key: Item #

    Table 3 - All forecasts entries - Key: Record ID #

        Record 1 - Item 1 - For 03-01-18 - Rcvd 1/1/18
        Record 2 - Item 1 - For 03-01-18 - Rcvd 1/10/18 
        Record 3 - Item 1 - For 03-01-18 - Rcvd 1/20/18
        Record 4 - Item 1 - For 04-01-18 - Rcvd 1/1/18
        Record 5 - Item 1 - For 04-01-18 - Rcvd 1/10/18 
        Record 6 - Item 1 - For 04-01-18 - Rcvd 1/20/18
        Record 7 - Item 2 - For 03-01-18 - Rcvd 1/1/18
        Record 8 - Item 2 - For 03-01-18 - Rcvd 1/10/18 
        Record 9 - Item 2 - For 03-01-18 - Rcvd 1/20/18

    Table 2 - Key: Item|Date

        Item 1|03-01-18
        Item 1|04-01-18
        Item 2|03-01-18
        Item 2|04-01-18

    The relationship works fine in that it links everything together as it should.  It seems like it would be easy from this point forward - make a "max" summary between Tables 2 & 3 and be done with it - but QB doesn't allow that kind of a summary.  I have tried different approaches or workarounds, but every time I think I am getting close, I hit the same roadblock.

    Any ideas, my wise friend?!  Thank you so much for taking the time to help me.  I am that lone wolf in the IT room going crazy over something that none of my peers understand!

    Thanks,
    Brett
  • BrettLyon's avatar
    BrettLyon
    Qrew Trainee
    8 years ago
    The solution was relatively simple: instead of trying to reinvent the wheel when getting the error "QuickBase does not allow summarizing relationships in which the reference is a lookup field", I just embraced it and created a workaround by creating a second field that mirrors the lookup field (but isn't a lookup).