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 change, but I never modify the record, I must always add a new record for record-keeping purposes.

For example, Part A might have several Forecast records for the same month, such as June 2018, since the outlook for June is always changing.

What relationship structure must I create in order to have a field in the parent record, for instance, such as Next Month's Most Recent Forecast Quantity?    Remember, most recent means most recent for a specific part number for a specific month.
App builders
Relationships

4 Replies

  • 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).