Active Record Lookup

OK, here goes.

You will need to have a table called Store Item Goals where the Key field is  a field in the format Store-Item.
so for example Baxter-Socks. Set that field to be the key field.  Let'S call it [Store-Item (key)]

Make a drop down field for the store and the item (either from relationships or simple multiple-choice fields).

Make a formula field called [Store-Item (formula)]
List ("-", [Store],[Item])

Make a form rule that is set to fire all the time (uncheck the checkbox) to ensure that when the Key does not match the formula change the Key field to the formula.

That will ensure that when you enter new store item goals that the Key field is correct.

Now in a normal situation I would just have you create a numeric field on that record and you would enter the goal.  But now you want this concept of a series of goals that can change from time to time. OK that is no problem.  

You already have the table which you posted in your question.  call that table Store Item Goal updates.

Make a relationship to the store item goals table where one store item goal has many store item goal updates.  Let the system create a reference field for you but then change that reference field to a formula that will calculate the key field of the store item goal record.  

Then do a summary on that relationship looking for the maximum affective date subject to the restriction that the Effective date is less than or equal to today.  

Let's call that field [store item current effective date].  Look up that that field down to the store item goals update table.  

Now do a summary maximum of the goal value I either numeric value of the store item update goal where the effective date matches the  [store item current effective date].

Wait, now we have the store item go record knowing what the current goal is.  How we can make a relationship from store item calls down to your weekly log table and of course you will have a formula field there which will calculate the key field of the store item goal.  Look up the cool down to the log table.  But let's name this field [Goal (lookup)]

That will be a live look up of the goal except you want to preserve the history so we need to make a similar field called  [Goal] And at the bottom of its fields properties we will set this to be a snapshot field and initialize it on the look up value  [Goal (lookup)]

Hooray,  The log now has a frozen snapshot of whatever the current goal was at the time the log was entered.

I suppose the one loophole in the system is that the log tables will capture the goal record on the date they are entering the log. So they do need to get their logs all input on time.  For example if they are two weeks late and entering their logs then that the "old" log will be measured against the current goal.

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------