trying to load a field in one table (all records) with a field in the most recent record in another table

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • In Progress
every day a currency exchange rate record is created
(e.g. date  = 25-May conversion rate = 0.765)

In a separate table I have a bunch of future purchases and we 
want to estimate the total cost of each purchase using the 
conversion rate...
(and some other factors that discount the rate based on how far into the future it is)

I have created a summary field to identify the MAX Record ID, so I know what record
is the right record to choose...and I can see it in the purchase table

but I can not figure out how I fill a field in the Purchase table
with the Field value (ConversionRate) for the record = Max Record ID...

Please tell me what I am missing?
Photo of Jeff QB Questions

Jeff QB Questions

  • 100 Points 100 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
It sounds like you are half way there.

Make a new relationship which is "reverse".  One X Rate has Many Purchases.  But on the Right side of the Relationships use that Summary field you created [Max Record ID#].

Then simply add a "lookup" field on that relationship to bring the Currency Rate  from that Max Record ID# record to the Purchase records.
Photo of Jeff QB Questions

Jeff QB Questions

  • 100 Points 100 badge 2x thumb
I appreciate the reply...I believe I have already done what you are talking about
see relationships

How do you do this explicitly?
Then simply add a "lookup" field on that relationship to bring the Currency Rate  from that Max Record ID# record to the Purchase records.

Where I am still struggling is to to get the 
value of 
table = Forex Rates
row = Max record ID
field = "Forex Rate - 1 AUD Buys X USD"

which is now accessible in PURCHASES via 
Forex Rate-Maximum Record ID#

Stuck into the 
table = Purchases
row = ALL ROWS
field = AUD Today

I cannot figure out what type of field to create
and what formula? to use to get the value
inserted into a field in the PURCHASES table...
I'm sure there is an easy answer, but no sure I am understanding the question.

So every day you enter a new FX rate ina table.

Then you have a table of future purchases.

Are you looking to lookup the most current FX rate automaticalky into the Purchases table and freeze it? Now I am not understanding which FX Rate you are looking to being into the Purchases record.
Photo of Jeff QB Questions

Jeff QB Questions

  • 100 Points 100 badge 2x thumb
OK, there are a couple hundred "futures orders" that are going to be "locked in" at some point in the future.  When I say locked in I mean the (futures price from a stock market ticker and a currency exchange rate).  The PURCHASES table tracks all of these...over time some orders price is locked  (which is basically today's market price and today's ForEx rate).  For this piece I am trying to look up todays ForEx rate and use it in every row of the PURCHASE table to calculate the total cost IF I were to execute the FIXING today.

I suppose snapshot could be helpful?  I can see in a form, editing an existing record in the PURCHASE table the Lookup field (showing a picklist of all ForEx records) and if I manually select the highest RecordID...It then fills in the correct exchange rate in my field in PURCHASES table.
So I can get to the information, but I am not getting a field in PURCHASES that says RECORD #84 is the Latest Record.  I think I need that to make a formula that AUTOMATICALLY fills in the ForEx value from RECORD #84...into the row in PURCHASES I am looking at plus every other row.

This way, I can see the TOTAL position IF I were to execute all of the future orders today.
Photo of Jeff QB Questions

Jeff QB Questions

  • 100 Points 100 badge 2x thumb
I want all 200 PURCHASE table rows to be updated each day when a NEW ForEx value is entered into the ForEx table.  The update will be through updating the Formula field that includes TODAY conversion rate.
I suggest that you make a table called FX Rate Today.  Add 1 record to it even though there are no fields yet.  It will be record ID#1.

In your FX Rates Table make a field called link to Todays FX Rate as a formula numeric field with a formula of 1.  Make a relationship to the FX Rate Today table based on that field.

Then do a summary maximum of the [Record ID#] and call that [Record ID# of most recent FX record].  look that up down to the FX Rates Table.

Then make a summary field of the Maximum (or Minimum or Average) of the FX rate where the [Record ID#] equals the value in the field [Record ID# of most recent FX record]

Great, now that Todays FX Rate Table knows the current rate.

So now just make another Relationship for the Todays FX Rate Table to all your Purchases based on the same technique - a field on the child side of the Relationship with a formula value of 1 and look up the Today's FX Rate.