2 Replies Latest reply on Mar 31, 2012 4:12 AM by beverly

    Pull values from related table based on value.


      Hello I am a newbie and need some help.


      I am working on a database with stock and prices. Table A is the stock itself and contains symbol, name, last price, and price date. Table B is the actual historical prices and contains symbol (how the relationship is conected), price, and as of date.


      I would like to enter a date in table A and have it pull the corresponding price from table B. Ultimatley i will have a check box in table a to either enter a specific date or get the most recent prices.


      One small issues is that a lot of these stocks will not be priced regularly and not on a consistant basis.


      Any help would be appreciated.



        • 1. Re: Pull values from related table based on value.

          1. IMHO, if you're going to have a table of Prices, then all prices - including the latest one - should be in that table.



          2. If you add a global gDate field to the Stocks table and define a second relationship as:


          Stocks::Symbol = Prices 2::Symbol


          Stocks::gDate = Prices 2::Date


          the related Prices 2::Price field will show the price on the date entered into gDate.



          3. If you modify the above relationship so that:


          Stocks::gDate ≥ Prices 2::Date


          and sort the related records from Prices 2 by Date, descending, the related Prices 2::Price field will show the price on or before the date entered into gDate.

          • 2. Re: Pull values from related table based on value.

            Research 'Filtered Portal'. You can change what is seen based on a value set/unset. The Invoices starter solution has a contacts layout with a tab panel showing similar contacts based on name or city, for example. A value list uses radio buttons, but a checkbox could be used.


            You set up a calculated key field, which changes with your selection(s). The key field is used in the relationship to the portal.


            The important reminder. The calculated key can ONLY be on the right-side of the relationship if it is not indexed (i.e. the parent table related to children records shown in a portal).


            Another tip that can make this flexible is 'multi-line key', so that the same portal records can be filtered various ways (such as the name or city). The tip is that this key field is indexable and contains a list (return delimited) of values that could match a parent key field.


            Very cool tricks. Let us know if you need further help.


            -- sent from my iPhone4 --

            Beverly Voth