9 Replies Latest reply on Jun 27, 2011 4:40 AM by matthijsbeekman

    Filtering multiple related records

    matthijsbeekman

      Title

      Filtering multiple related records

      Post

      My problem is the following

      In my main table i have the following fields:

      Date, Item1Price2

      Now i have a second table which is a price list.

      It has the following fields: mutation Date, item, price 1, price  2, price 3 (Where the different prices depict different ammounts of the  item)

      My goal is to set the Item1Price2 field in het main table to the current price as set in the pricelist.

      At first I related the two tables using the Date and Mutation date (Date >= Mutation Date).  This way, the price changes in the future are not used and records from  the past are not changed with today's prices. I can't however figure out  how to make a subselection of the related tables. At default FMP seems  to take the first one. I would like to first cancel out all the other  items and then pick the most current record. Setting multiple  relationships won't do since i would like to retrieve prices for  multiple items.

      Attached is a picture showing the price list. For example, the date in the main tabel is 5/6/11 and i would like to get price 2 for item 1 from the price list. This should then be €1.70 (first ruling out all the dates after 5/6/11, then selection only the records for item 1 and then taking the most recent record and retreiving price 2).

      I hope somebody can help me with this.

      selection.jpg

        • 1. Re: Filtering multiple related records
          philmodjunk

          Main::Item = PriceList::Item AND
          Main::Date > PriceList::MutationDate

          Make sure to specify a sort order for the relationship that sorts the PriceList records in descending order by MutationDate so that the most recent date <= mutation date is the one that will be referenced.

          • 2. Re: Filtering multiple related records
            matthijsbeekman

            thanks for your quick response.

            some things are still not clear though. are you specifying the following in the relationships between the two tabels?

            Main::Item = PriceList::Item AND
            Main::Date > PriceList::MutationDate

            if so, this doesn't seem to fix the problem since in one record of the main table i would like to obtain prices of multiple items.

            • 3. Re: Filtering multiple related records
              philmodjunk

              That's a single relationship to match a specific Item by Item number and by matching to the most recent MutationDate that is not a future date.

              If you want to match to all items by date, eliminate the first clause to use:

              Main::Date > PriceList::MutationDate

              If that's not what you wanted, please explain in more detail what you mean by "I would like to abtain prices of multiple items."

              This may provide some clarity: What you have posted looks close to what you might see in an invoicing system where you'd have this structure:

              Invoices---<LineItems>----PriceList

              A portal to LineItems is then used to list the items purchased on the Invoice with current prices copied (looked up) from the PriceList table each time an item is selected and entered into the LineItems portal.

              The LineItems to PriceList link would use the relationship as I originally described it.

              • 4. Re: Filtering multiple related records
                matthijsbeekman

                Im still not sure whether i got the matching part. Where are you typing these relations?

                As to my explain what my requirements are:

                I have multiple fields in the main tabel corresponding to the various items and prices. I want these fields to retrieve the most current (not in the future) price in the pricelist.

                • 5. Re: Filtering multiple related records
                  philmodjunk

                  Im still not sure whether i got the matching part. Where are you typing these relations?

                  There's only one place you can define relationships. Open manage database relationships. Drag from a field in one box to a field in the other. Doubleclick the relationship line to open the relationship editor where you can more easily add additional pairs of match fields like you need for this relationship and where you can specify different operators in place of the default = operator.

                  "I have multiple fields in the main tabel corresponding to the various items and prices. I want these fields to retrieve the most current (not in the future) price in the pricelist"

                  That sounds like a troublesome design approach. Using a related table where you can make one record for each item simplifies things tremendously. Having separate fields for different items in the same record makes it difficult to refer to the correct record in your price list for each field. This might even require a separate relationship for each field and this could be very complex and "messy" as a design for your database.

                  Take a look at this simplified Invoice demo and note how a portal to a related table is used to list a series of items for which prices are looked up. If you started with this file and modified the product table to include a mutation date field and added a creation date field to line items, you could modify the relationship between line items and the products table to have the relationship I have suggested here.

                  http://fmforums.com/forum/showpost.php?post/309136/

                  • 6. Re: Filtering multiple related records
                    matthijsbeekman

                    Thank's for all the help.

                    I'll take a look at your suggested file tomorrow.

                    let me explain my requirements a little further though:

                    the records in my main table are drinks held in a bar. i want this record to calculate the costs of this drink. for this calculation, each record has to use the appropriate prices (most recent prices but not in the future, for the given item and a given amount). of course i don't want old records to change, when a price change is inserted. At first a record in the pricelist tabel contained all the prices off all the items. Changing a price would then mean copying the old price list, setting it's entry date en only changing the required price. This however creats lots of data and seems unnecessary. That's why i would like to work with a pricelist table which has the items as records and the mutation date.

                    • 7. Re: Filtering multiple related records
                      philmodjunk

                      That sounds like a perfect fit for the invoicing demo file I suggested. You'll just need to update the relationship to make it date sensitive.

                      • 9. Re: Filtering multiple related records
                        matthijsbeekman

                        Finally had some time to look at this again. Your solution indeed perfectly fits my needs. Thanks for the help!