9 Replies Latest reply on Oct 1, 2009 1:34 PM by comment_1

    Lookup records based on two fields

    trialuser1111

      Title

      Lookup records based on two fields

      Post

      Hello,

       

      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.

        • 1. Re: Lookup records based on two fields
          comment_1
            

          Ideally, you should have three tables: Companies (one record for each company), Tickers (one record for each ticker), and Holdings. In the Holdings table, you should have one record for each holding in a period, so the fields would be:

           

          • CompanyID (link to the Companies table)

          • TickerID  (link to the Tickers table)

          • Period

          • Quantity

           

          To import your data into the Holdings table, you could import it several times (a column at a time), or import it into a temp table and have a script split it up into individual records. Or, if you can, do it at the source (it's hard to advise without knowing how many columns do you have, if this is a one-time conversion or something that needs to be done periodically, etc.).

          • 2. Re: Lookup records based on two fields
            trialuser1111
              

            Okay I think I'm halfway home here.  I've entered in a few sample holdings using your field designations, and I'm trying to put together a sample layout.  When I call up a Firm (the layout is based on the Firms table), my "Holdings::Shares" field automatically returns the first matching record, and doesn't seem to care what the client ticker is.

             

            I've got Firms linked to Holdings via CompanyID, and I've got Firms linked to Market Data (which handles the Tickers and relevant client info) via Ticker.  What do I need to do in my layout to make sure it the Holdings::Shares field is based off both relationships and not just one?

             

            • 3. Re: Lookup records based on two fields
              comment_1
                

              trialuser1111 wrote:

              When I call up a Firm (the layout is based on the Firms table), my "Holdings::Shares" field automatically returns the first matching record, and doesn't seem to care what the client ticker is.


              You need to filter the relationship by a global field. Best to use another occurrence of the Holdings table for this and link it to Firms so:

               

              Firms:: CompanyID = Holdings 2:: CompanyID

              AND

              Firms:: gTickerID = Holdings 2:: TickerID

               


              • 4. Re: Lookup records based on two fields
                trialuser1111
                  

                I think I got it.  I was halfway through typing out a longer post but in the process I solved my own problem (I have an existing global relationship and I was wondering if I could re-purpose or if I needed two).

                 

                Things look good right now.  But right now I'm only dealing with one holding period.  If I want to go back and add historical holdings (more periods), how can I account for that and have the layout reference the Firm (CompanyID), Client (Ticker), and Period?

                 

                Thanks 

                • 5. Re: Lookup records based on two fields
                  comment_1
                    

                  Add yet another predicate to the relationship:

                   

                  Firms:: CompanyID = Holdings 2:: CompanyID

                  AND

                  Firms:: gTickerID = Holdings 2:: TickerID

                  AND

                  Firms:: gPeriod = Holdings 2:: Period

                   

                  • 6. Re: Lookup records based on two fields
                    trialuser1111
                      

                    Sorry I don't think I explained myself well.  What if I want to show shares over time in a single layout.  For example, if I wanted to be able to call up a firm, and enter the global ticker ID (so far so good) and then see a simple table that shows shares held for Period 1, Period 2, Period 3, and Period 4.

                     

                    If that's the case, would I need to create a field for each Period?  And then what's the best way to organize it on the layout, as a portal? 

                    • 7. Re: Lookup records based on two fields
                      comment_1
                        

                      trialuser1111 wrote:

                      if I wanted to be able to call up a firm, and enter the global ticker ID (so far so good) and then see a simple table that shows shares held for Period 1, Period 2, Period 3, and Period 4.


                      This "simple table" is called a portal. If you don't filter by gPeriod, the portal will show all matching records (i.e. from all periods).


                      • 8. Re: Lookup records based on two fields
                        trialuser1111
                          

                        OK changing the periods to the field names worked and now I can create calculations for quarterly and annual change and show the historical holdings in a portal.  Next question is a little tricky:

                         

                        In this layout I'm building, in addition to showing various pieces of information regarding the shares held by firms of clients, I'd also like to be able to show the shares held by the client peers...  In the Market Data table where client info resides, there are four fields (Peer1, Peer2, Peer3, Peer4) where users can enter in the tickers of other companies for comparison.  How can I have FileMaker lookup the peer shares held by a firm without disturbing the global client ticker already in place for the rest of the layout?  Basically, when I'm looking at the layout for Firm A and I enter "XYZ" into the global ticker field, I want the layout to spit out the shares held for XYZ, its position change (got all that solved) AND the current positions for XYZ's peers ABC, DEF, and QRS.

                        • 9. Re: Lookup records based on two fields
                          comment_1
                             I am afraid you have lost me. Who is the client here - the firm A that holds stock in company XYZ, or the XYZ company?