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

Question

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.

KevinOBoyle · Answer

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.

Sign up

Login to the community