6 Replies Latest reply on Dec 7, 2013 1:25 PM by shona1

    Conditional fields



      Conditional fields


           On my database I m wanting to populate the foreign key field based on the criteria of 4 other conditional related fields, this will then give me the related price$. I have previously done conditional value lists which I thought it would be similar, but in this case Im not selecting from a list, so Im at a blank as to how make it work. I have attached an example test database which is simplified to muck around with, Thanks.


        • 1. Re: Conditional fields

               Just open field options for LinesPrice and set it to auto-enter the value of Price from the prices table. This can be an auto-enter calculation or you can use the looked up value option. Prices 2 is not needed to make this work.

          • 2. Re: Conditional fields

                 Thanks but I still don't have it working on either of those options, any suggestion of what I may have wrong. I might not have asked the question properly - It needs to drill down to get the correct Price when (RentalLines::LinesDept = Prices::Dept) and (RentalLines::LinesDays = Prices::Days) and (RentalLines::AgeGroup = Prices::AgeGroup) and (RentalLines::BootsCode = Prices::BootsCode). All 4 conditions met would leave on one record in the Prices table.

            • 3. Re: Conditional fields

                   In your sample data, the _fkPricesID field is empty and thus no record in RentalInes currently matches to any record in Prices.

                   But that's not the only issue. _pkPricesID is a unique value in your table. If you match records using the _fkPricesID and _pkPricesID fields, then you cannot also match by the other three fields since this pair of fields can only match to one record to begin with. The other match fields serve no purpose in matching to a specific record in PriceLines.

                   There are two approaches you might take to get this to work:

                   Use this relationship:

                   RentalLines::_fkPriceID = Prices::_pkPriceID

                   And then use days, age group and department in a conditional value list to limit the options that pop up when you select from a value list that enters an ID value into _fkPriceID.

                   Or set up a relationship that does not use the _fk to _pk pair of match fields.


                   Just from the data provided, I can see two records in Prices where Dept is Skis, AgeGroup is Adult and Days is 2. The only visible difference is the BootCode. If that's significant difference--the reason why one option is $55 and the other $35, then you could use this relationship:

                   RentalLines::AgeGroup = Prices::AgeGroup AND
                   RentalLines::Days = Prices::Days AND
                   RentalLines::Dept = Prices::Dept AND
                   RentalLInes::BootCode = Prices::BootCode


              • 4. Re: Conditional fields

                     Thank You I feel like such an idiot for not seeing it- it totally makes sense now "I just removed the _fk to _pk match fields and it worked" I just had it stuck in my mind that that had to be there, even though I have some conditional value lists with their own TOs.

                     This forum is such an awesome resource, especially when you work on a database by yourself without anyone else to bounce off.

                     ThankYou ThankYou


                • 5. Re: Conditional fields

                       But just removing the PK and FK fields WON'T work!

                       Note that you have at least two records in your sample data where the department, days and age group are identical, but the price is not. You need some way to further refine the matching to match to the correct record. I can't tell whether or not the "BootsCode" is the missing factor or not.

                  • 6. Re: Conditional fields

                         Yes the "BootsCode" is definitely one of the match fields, it is a result of a calculation to determine whether the rental is "WithBoots" "WithoutBoots" or "BootsOnly" giving an "A" "B" or "C" result in that field. I have the Prices working from your help in my actual database now which has nearly 500 different prices and is matching back to last years data correctly. Thanks for the help.