2 Replies Latest reply on Oct 17, 2013 4:33 PM by philmodjunk

    Misbehaving Value List

    StuartPalm

      Title

      Misbehaving Value List

      Post

           I wonder if someone can shed any light on my problem - I'll describe it as best I can:

           I have created an Engineers booking system for sending and monitoring the work of our company's gas engineers who service house boilers.

           Everything is fine on our central booking form (using various linked tables) apart from a problem occuring with looking up house addresses. We have a table for Property Addresses which I use as a Value List. The idea is that when a call comes in the caller is asked their Postcode (Zipcode). In the UK a single postcode such as this - LS103AQ - describes a section of a street on which there could be maybe a dozen different numbered properties - for example 1,2,3,4,5,6,7,8,9,10 Bedford Street can all end in the same postcode.

           So I have set the Value List up to filter only the addresses corresponding to the entered postcode and the resultant small filtered section of addreses appear in a drop down list which the operator can then pick a single correct address from.

           My problem is this - whatever the last picked address is for any postcode - let's use LS103AQ above as the example - then the last picked property value alters all the previous property values for that postcode used in the Job booking table. So for instance if I do a job booking and use that postcode above and I pick 1 Bedford Street as the address that's fine, it is entered on the form. Then lets say a day later another call comes in from 8 Bedford Street and we enter the same postcode and pick Number 8 from the Bedford Street list, then the record entered the day before for 1, Bedford Street immediately changes to 8 Bedford Street as would any other record with the same postcode.

           This is driving me nuts!! Can anyone shed any light on what I may be doing wrong?

           I have defined relationships between the Job Booking Form and then Property List with a field I call Postcode to link the tables together but I could expand on this information if any wants to look at my database file.

            

      FM_Table_Relationship.JPG

        • 1. Re: Misbehaving Value List
          StuartPalm

               Sorry - forgot to say "thanks" if anybody can help. - palmy100

          • 2. Re: Misbehaving Value List
            philmodjunk

                 You can use the relationship to BITMO Property 2 to control your conditional value list, but you cannot use it to display address data. Instead, you need to refer to fields from Property Addresses to display the clients address.

                 For existing clients, selecting their address should be all that you need to do unless a client can have multiple addresses--in which case more relationship changes are needed to facilitate that set up.

                 For a new client, you'd create a new record in Clients and link it to a new record in Property Addresses.

                 Now that I've talked that through inside my head, I'm not seeing a purpose for a conditional value list based on PostCode.