AnsweredAssumed Answered

Lookup records based on two fields

Question asked by trialuser1111 on Oct 1, 2009
Latest reply on Oct 1, 2009 by comment_1


Lookup records based on two fields




Having a little trouble getting a new function in my database to work properly.  I would like to be able to create a calculation or looked up number field that returns the shares held by Firm X of Client Y.  (We use FMP10, btw.)  Let me explain a bit more:


I currently have a spreadsheet I'd like to import into my database, but before I do it I want to understand how the relationships will work, since maybe I'll need to reorganize the data before import.  Currently, the spreadsheet lists the holdings for two firms over various quarters.  The headers of the spreadsheet are thus:

CompanyID     Ticker     Q2'09    Q1'09   Q4'08   Q3'08   Q2'08...


So there are a few thousand entries for Firm A (CompanyID = 1), and a several hundred entries for Firm B (CompanyID = 2).  The trouble I'm having is, how can I tell FileMaker to look up how many shares of Client Y (identified by Ticker) are held by Firm X (identified by CompanyID)?  There will be no unique record if just one of these fields is used, but there will be if BOTH are used.  Is there a way to do this?

CompanyID     Ticker     Q2'09    Q1'09   Q4'08   Q3'08   Q2'08...

1                   ABC        1000     2000   4000    2000    0

1                   XYZ        25000   25000  25000  25000   25000

1                   RST        0          0        0         50000   0

2                   XYZ        300000  0        0         0         140000

2                   DEF        4000     4500    5000   7000     10000 


What I'd ultimately like to do is have a layout (based on Firms) that let's people type in a Ticker and see how many shares are held at a pre-defined period in time.  That is, if I'm viewing the layout for Firm A (CompanyID = 1) and I type "XYZ" into a global "Find Ticker" field, it will return 25,000.  Is this doable?  What's the best way for me to (1) set up the relationships; (2) arrange the spreadsheet for import en masse; (3) arrange the new data within my database (new table linked to Firms via CompanyID or additional fields within existing Firms table)?  I've got a pretty basic understanding of relationships, and haven't tried anything this complicated yet.