1 2 Previous Next 15 Replies Latest reply on Jun 24, 2014 8:43 AM by philmodjunk

    Relook Up field based on Conditional relationship

    shona1

      Title

      Relook Up field based on Conditional relationship

      Post

           In my Database I have TO based on the relationship between 2 tables conditions being met as noted below

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

      The result gives me the correct price in the RentalLines Portal from the Price Table for each item rented. RentalLines is a portal within the RentalCard Table, the relationship is working fine unless the NoDaysPaid are changed on the rental card.  

      This is because the  "RentalLines::Days" is a actually calculation field, unstored, from "Rental Card::NoDaysPaid".

      How do I force it to relook up the Price when the No.of Days is Changed.

        • 1. Re: Relook Up field based on Conditional relationship
          philmodjunk
               

          How do I force it to relook up the Price when the No.of Days is Changed.

          In which table? RentalLines or Prices?

               Is this for a rental invoice?

               You shouldn't need to relookup any data if you are changing data in Prices. That would be the same as changing a price of an item after the customer purchased it. Changes to RentalLines::days should automatically look up new data from Prices.

          • 2. Re: Relook Up field based on Conditional relationship
            shona1

                 Yes it is a rental Invoice this is this table called "Rentalcard" The problem occurs after the "RentalLines" records have been created, so initially it might have the price for 2 days, but the customer decides to change to 3 days renting. So a change is made to the Rentalcard field "RentalCard::NoDaysPaid"  which does actually change the "RentalLines::Days" field as it is a calculation field to equal the Days from the Rentalcard, but this doesn't seem to force the change in the price. So the rental is showing 3 days but with the price for 2 days. 

                 The Tables "Rentalcard" and "RentalLines" are linked with primary and foreign keys.

            • 3. Re: Relook Up field based on Conditional relationship
              philmodjunk

                   While I think I remember helping you set this up originally, too much has gone by for me to remember all the details.

                   Wasn't this a database where we set up a table with one record for each day that the client would be renting from you? In order to handle rates that could be different for different days of the year?

                   If so, I think that table is not getting the needed additional record to go from two records to three.

                   But I am guessing from an imperfect memory here....

              • 4. Re: Relook Up field based on Conditional relationship
                shona1

                     I have managed to mimic what I have in a smaller test database, can I send this to you? Thanks

                • 5. Re: Relook Up field based on Conditional relationship
                  philmodjunk

                       Why not just answer the questions that I have asked? If my memory on how this is set up is correct, it shouldn't be too complex to fix.

                  • 6. Re: Relook Up field based on Conditional relationship
                    shona1

                         It is a database for handling rental records. There is one customer to each rental card (or Invoice).  There can be many rental transactions for the one customer so we have a portal on the Rental card which is the table called "RentalLines" The rental rates (or Prices) differ depending on four different criteria in the conditional relationship all drilling down to the one matching price from the "Prices" table. Each of the four criteria for the conditional relationship have a field in the RentalLines table. This was the problem that you helped me with back in December. The four different criteria are; Number of days renting, Age (Adult or Youth), Department (type of item skis, jacket etc), BootCode (A-No Boots,B-with Boots,C-Boots only).
                         When you assisted me back in Dec one of the problems was I didn't have the "BootCode" included in my TO. This is working fine now, where there can only be the one correct price brought through from the "Prices" table.

                         When a new record is entered in the "RentalLines" portal it is working fine; as each of the four conditions are met it drills down to the correct price for that rental transaction. The problem I am having is occasionally we may need to change the number of days renting after the rental lines transactions have been created.
                         The "Number of Days" is actually changed in the rental card table. The field for Number of Days on the Rentallines table is a Type: calculation, unstored from RentalCard::Days. This Rentallines field is changing correctly when it should but the prices are not. Hence my post to this forum.
                         If I go into one of the rental lines transactions and type in one of the conditional fields (typing exactly the same details), it will actually update the price correctly.
                         I have tried lots of different ways that the price is shown on the rental card having it come as an auto enter or type:calculation or just have the field prices::prices and every way I try it, it won't automatically update the price when on an existing record, when we have changed the days.
                         I know it "shouldn't be too complex to fix" but cannot see what the setting is that I have wrong.

                         I created the database originally and we ran it in two rental locations last year but we used it to track for tracking the customers and the gear hired with No pricing. The pricing Table has now become an add on. I apologise again for having difficulty in explaining myself clearly but Im a self taught, part-time FM user.

                          

                    • 7. Re: Relook Up field based on Conditional relationship
                      philmodjunk

                           I don't see how that answers this question:

                           Wasn't this a database where we set up a table with one record for each day that the client would be renting from you? In order to handle rates that could be different for different days of the year?

                      • 8. Re: Relook Up field based on Conditional relationship
                        shona1

                             Not exactly, its one record for each type of item rented (Dept). Rates are different for different number of days rented (e.g.:1,2,3...Days) and also different to met the other 3 conditions - Dept, AgeGroup, & BootCode.

                        • 9. Re: Relook Up field based on Conditional relationship
                          philmodjunk

                               Ok, then I am recalling a different project. I needed to be sure on that as I can easily see in that other project why things would not update and what would need to be changed in order to fix the issue.

                               But this project is set up differently so scratch those thoughts...

                               Can you upload a screen shot of the relevant portion of your relationships found in Manage | Database | Relationships?

                               Can you do a screen shot or copy and paste of the options used to look up the rates?

                          • 10. Re: Relook Up field based on Conditional relationship
                            shona1

                                 Hopefully you can see these screenshots

                            • 11. Re: Relook Up field based on Conditional relationship
                              shona1
                              /files/01225c599c/Options.png 1084x673
                              • 12. Re: Relook Up field based on Conditional relationship
                                shona1

                                     Hi, I never did get a answer to this post??

                                • 13. Re: Relook Up field based on Conditional relationship
                                  philmodjunk

                                       Do you use a looked up value or auto-entered calculation to copy a value from Prices into RentalLinesEntry?

                                       If so, which?

                                       If I am reading the details correctly, all four match fields in RentalLinesEntry are either unstored calculations or global fields? (I see "T" line connectors.)

                                  • 14. Re: Relook Up field based on Conditional relationship
                                    shona1

                                         The PriceCharged field in RentalLineEntry is a Type: Calculation (RentalLineEntry::StandardPrice = Prices::Price)

                                         The four Match fields are also Type: Calculation

                                    1 2 Previous Next