Retrieve/get value from another record with multiple criteria

[jc606]

Hi,

I just started using FM yesterday (I used to use MS Access) and I got stuck. I have a table with data on stocks (e.g., price, date, ticker). For example:

12/31/2010 AAPL 300.25

1/31/2011 AAPL 325.73

2/14/2011 AAPL 350.11

I have three questions.

1) If i'm setting up a new calculation field, how do I retrieve a value from a field within the same table and using two criteria? For example, for the 3rd record (2/14/2011) above, let's say I want to get the price value where date=12/31/2010 and ticker=aapl which would be 300.25. So, for the 3rd record, the field would show 300.25.

2) Continuing with question 1, let's say I can get that value (300.25) which shows up on the 3rd record and now I want to run a calculation of (price of 2/14 - the value we just got) / price of 2/14. This provides the % return of 2/14 from 12/31. What's the step to make this part happen?

3) Lastly, is this an inefficient way of setting up, organizing, or calculating data?

Thanks in advance.

JP

[comment]

how do I retrieve a value from a field within the same table and using two criteria?

Usually, by defining a self-join relationship using two pairs of matchfields.

is this an inefficient way of setting up, organizing, or calculating data?

Hard to say without knowing your purpose. Instinctively, I'd say you should have a parent table of Stocks and a child table of Prices, related by StockID or by TickerSymbol.

[jc606]

Can you or someone else elaborate on the first part (defining a self-join relationship)? Where and how can I do that?

[comment]

See:

http://www.filemaker.com/11help/html/relational.11.10.html#1028163

and:

http://www.filemaker.com/11help/html/relational.11.7.html#1027907

[jc606]

Sorry, I'm having trouble understanding self-joins. I tried a tutorial, but I still don't seem to grasp the steps that I need.

Basically, i want to do the following:

table name:data

date ticker price value1

12/31/2010 AAPL 300.25

1/31/2011 AAPL 325.73 300.25

2/14/2011 AAPL 350.11 300.25

12/31/2010 CRM 115.22

1/31/2011 CRM 120.15 115.22

Where "value1" supplies the price for the related ticker for the date 12/31/2010.

Can you or someone else tell me the specific steps I need to take? Thanks again.

[comment]

I am afraid I still don't understand the task you present. Where is the date 12/31/2010 coming from? Will it be the same for all records in the table?

[jc606]

my table has three fields:

Date

Ticker

Price

And its currently filled with all that data. With prices of various stocks at different dates. What I'd like to do is compare the price of a stock at one date to the price at another date. So, I was thinking I would create a new field to show/get the price of each stock for a specific date (e.g., 12/31/2010). So, If i'm looking at a record of AAPL at 1/31/2011 and it's price, then the last (new) field would show the price at 12/31/2010 (which would be pulled from another record).

[comment]

Yes, obviously you would need a second date field to hold the second date... but you haven't answered my question: are you picking this date arbitrarily or will it be calculated (e.g. end of previous year), and will it be the same for all records in the table - i.e. if you have picked 12/31/2010, then ALL prices will be compared to the price (of the same stock) on 12/31/2010?

[jc606]

Sorry about that. I will be picking the date, so all prices in that column would show prices for the related stock as of 12/31/2010.

[comment]

First, define a new field gDate (type Date, global storage).

Next, define a self-join relationship of the Prices table as:

Prices::Ticker = Prices 2::Ticker

AND

Prices::gDate = Prices 2::Date

Finally, define a calculation field (result is Number) =

( Price - Prices 2::Price ) / Price

In order to display the comparison price, just place the related field Prices 2::Price on your layout (make it non-enterable so that it cannot be modified accidentally).

[jc606]

Thanks for that! I got it to work. Thank you for being patient with me. I really appreciate it.