8 Replies Latest reply on Oct 24, 2011 1:50 PM by LWayne_1

    Filtering a Related many table in a one to many relationship

    LWayne_1

      Title

      Filtering a Related many table in a one to many relationship

      Post

      I am new to FileMaker Pro but I am familiar with MS SQL.  I have two tables StockMaster and Prices with a one to many relationship.  On a periodic basis, less say weekly, I would like to calculate the current market value of stocks by multiplying the shares in the StockMaster table against the price on a certain day in the Prices table, the many table.  I have tried a global date field in the StockMaster table and set up a relationship using symbol in each table and second using the global date field in the StockMaster table with a PriceDate field in the Prices table.  However I receive all records from the Prices table instead of just the records that match the global date field. Can anyone point me in the right direction to return only the records for one days prices?

      A filtered portal in my case will not work with what I am trying to accomplish.  Thanks for any help.

        • 1. Re: Filtering a Related many table in a one to many relationship
          philmodjunk

          What you described with your global field should work.

          Create this relationship:

          StockMaster::Symbol = Prices::Symbol AND
          Stockmaster::GlobalDate = Prices::PriceDate

          This matches the current record in StockMaster to the first record in Prices that matches the stock symbol and the date entered into the global date field. The key here is to have both pairs of fields defined in one relationship.

          A calculation field defined in StockMaster can then compute number of shares times Price:

          NumberOfShares * Prices::Price

          • 2. Re: Filtering a Related many table in a one to many relationship
            LWayne_1

            PhilModJunk -

            Thanks for your response.  I have both pairs of fields described as you have outlined in my relationship.  In the relationship graph when I duplicate the StockMaster table (306 records) and relate it to the Prices table (21,957 records) I receive 306 records.  I expect to receive 60 (the number of records that match both the PriceDate and Symbol).  When I duplicate the Prices table (21,957 records) and relate it to the StockMaster table (306 records) I receive 21,957 records.  Again, I expect to receive 60 (the number of records that match both the PriceDate and Symbol).  An ideas on what I am doing wrong?       

            • 3. Re: Filtering a Related many table in a one to many relationship
              philmodjunk

              Don't duplicate the table occurrence. Just drag from StockMaseter::Symbol to Prices::Symbol, then drag from Stockmaseter::GlobalDate to Prices::PriceDate in the same occurrence box. You can double click this relationship line to see that you have two pairs of match fields defined in the same relationship and it should look pretty close to what I put up in my last post here.

              • 4. Re: Filtering a Related many table in a one to many relationship
                LWayne_1

                PhilModJunk -

                Thanks for your resonse.

                 

                When I click on the relationship line I see:

                StockMaster::Symbol = Prices::Symbol 

                AND Stockmaster::GlobalDate = Prices::PriceDate

                 

                It has to be something pretty simple I am doing wrong.  I tried exactly what you said, but when I look I receive 100% of the records not just the filtered ones.  Any ideas on what it could be? 

                 

                • 5. Re: Filtering a Related many table in a one to many relationship
                  philmodjunk

                  Since the relationship looks right, we'll need to look at the design of your layout.

                  On what table occurrence is your layout based? (This will be the occurrence selected in "show records from" in Layout Setup...)

                  You indicated that a "filtered portal will not work". Does that mean you do not have any portal here or did you set up a portal based on this relationship?

                  • 6. Re: Filtering a Related many table in a one to many relationship
                    LWayne_1

                    Hi -

                    Thanks again.

                    What I was trying to do was to create a layout that would show only the filtered records.  I could filter the original layout in a portal but I preferred having the layout filtered so only the records I want are showing.

                    I think I have a wrong understanding of how a new Table Occurence and Layout work, probably because I keep thinging back about how it worked in SQL.  It must be in the design of my layout, however I not sure how to communicate it thru the forum.

                    • 7. Re: Filtering a Related many table in a one to many relationship
                      philmodjunk

                      Records from which table, the stocks or the prices? Your layout can only be based on one or the other and then your relationship controls what data from the other table is displayed here.

                      Here's one possible interpretation of what you have requested:

                      Keep the relationships as written, start with a layout that specifies StockMaster in Show Records From. Add the PriceDate and Price fields from Prices to this layout. You can arrange your records in a single row in the body, shrink the body down to just that one row tall and view the data in view as list mode, or you can use view as table mode. Either way, you can see multiple records listed in rows with both the stock symbol, the price and the price date. (Added that last field just to make it easy for you to see that this works.)

                      Specify the desired date in your global date field.

                      Enter find mode while on this layout and enter an * into the Prices::Price field. Click Perform Find.

                      You should now see the records you expected to see.

                      What you have done is tell FileMaker "Find every record in StockMaster that has a related Prices record." Your relationship specifies that there will be a related record in prices for each record in StockMaster if both the Stock Symbols and the dates match.

                      This same type of Manual Find can be performed in a script and you can include other criteria besides the existance of a related Price record.

                      A working knowledge of SQL can definitely mislead you when you first get started in FileMaker. Filemaker accomplishes most of the things you can do in SQL in a very different manner. Often this approach is simpler, but in many cases, the reverse can be true.

                      If you've done some Visual Basic Programming where you assigned the results of a query to a record set object, it may help to view each table occurrence as a record set object where you can control the result set assigned to it by performing finds, go to related records, show all records and several other options in the records menu. Just as each record set object can have its own sort order and current record, so does each table occurrence.

                      What you will find truly odd is that the only way you can access the found set, sort order, current record of a given table occurrence is to go to a layout that refers to it in Layout setup | show records from. This "layout context specific" set up in FileMaker makes some very simple operations even simpler, but in turn complicates more involved tasks where you need to manipulate multiple found sets of records.

                      Here's a tutorial on Table Occurrences that you may find useful... Tutorial: What are Table Occurrences?