Active Record Lookup

Question

Good Day.&nbsp;Hoping someone might be able to help provide me some direction.&nbsp;I have 2 tables&nbsp;&nbsp;One List contains a Items with a goal measurement assigned to a store. This list also contains an Effective Date, Retired Date and a status field which identifies if the Item is Active or Retired based on if there is an End Date.The Other Table is a log that is submitted by each store once a week.&nbsp;I need this table to pull the Active Goal for Socks and Masks when a new log is entered.&nbsp;I also need to make sure when a new goal is added for those items and retire the old goal that the old logs do not update with the most recent goal.&nbsp;ADDITION&nbsp;Alternately - if it could pull the Goal into the Log based on the date of the log being between the effective and retired date of the metric.I hope this makes sense.&nbsp;The Log includes the Store Name&nbsp; as does the Item Goals.Any help is GREATLY Appreciated!Thank you!------------------------------Amber Anderson------------------------------

amberanderson · Answer

If anyone has any other ideas on how something like this might work - or possibly a different set up to get it to work I would be extremely appreciative :)

Thank you in advance!

------------------------------
Amber Anderson
------------------------------

markshnier__you · Answer

I do know how to do this. I will post back tomorrow or over the weekend.

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

markshnier__you · Answer

Can you tell us how many different Items you set goals for at each store. Is it just 3 categories of Socks, Masks and Shirts?

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

amberanderson · Answer

Hi Mark -&nbsp; &nbsp;Thank you so much for your help on this :)Each Store is a Client&nbsp; that we support. The Categories are essentially Metrics that need to be reached.&nbsp;Each Store/Client may have different Categories/Metrics that need to be met.&nbsp; Not All Stores will have a Category that the other store might have. And some of those Categories may get updated at different times than the others depending on the contracts we have.&nbsp;As an Example we typically have about 7-10 standard Categories/Metrics that are normal to use. But we have have a one off&nbsp; item because the store/client is weird and wants to be different (lol).I hope that helps :)Thank you SO much for your help!------------------------------Amber Anderson------------------------------

markshnier__you · Answer

OK, here goes.You will need to have a table called Store Item Goals where the Key field is&nbsp; a field in the format Store-Item.so for example Baxter-Socks. Set that field to be the key field.&nbsp; 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.&nbsp; But now you want this concept of a series of goals that can change from time to time. OK that is no problem.&nbsp;&nbsp;You already have the table which you posted in your question.&nbsp; 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.&nbsp; 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.&nbsp;&nbsp;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.&nbsp;&nbsp;Let's call that field [store item current effective date].&nbsp; Look up that that field down to the store item goals update table.&nbsp;&nbsp;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&nbsp; [store item current effective date].Wait, now we have the store item go record knowing what the current goal is.&nbsp; 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.&nbsp; Look up the cool down to the log table.&nbsp; 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&nbsp; [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&nbsp; [Goal (lookup)]Hooray,&nbsp; 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.&nbsp; 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 ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

Forum Discussion

Active Record Lookup

Related Content

Pipelines: Lookup newest record?

Lookup Field not pulling Data

Average daily active employees calculation

Lookup Field Not Pulling Data

Custom Record Titles