3 Replies Latest reply on Apr 28, 2009 12:29 AM by Orlando

    Value List changes Foreign Key ????



      Value List changes Foreign Key ????


      I have 2 tables:     


       popup changes foreign key


      This database is designed to allow for MORE THAN ONE PROPERTY to have associated transactions.  Everything works well except for....


      I have a layout based on transactions.  It is a sub-summary report that breaks on  Transaction::Item


      I put a popup in the header that is:  Transactions::PropertyID.FK with a value list of Property::PropertyID.pk & Property::Address.

      This works well.


      The problem is that when I select an address in the popup, it changes whatever record I happen to be on to the PropertyID of the property selected.  In the example beleow, East Elm is property 4.  But the Benjamin Moore PAINT record previously was Property 5 but was changed to 4 as soon as I selected the East Elm property. As you might imagine, this screws up the report.  And, it happens BEFORE any script is run.  ie, it happens immediately AFTER the selection is made.

       Top row should be property 5

      Got any ideas?



        • 1. Re: Value List changes Foreign Key ????

          Hi synergy46


          I think the problem is you are breaking the link between he active transaction record and the property record because you are modifying the value in the Transactions:: PropertyID.FK field in the header, which will be the active record you are viewing, even though you are in a sub-summary report you are still in 1 of the transactiuon record.


          Depending on what you are selecting an ID and Address you may want to use a global to select the id and address and then run a script of whatever to perform the action you are trying to achieve, or if its just for display the global will be all you need. 


          I hope this helps 

          • 2. Re: Value List changes Foreign Key ????

            As you suggested I put a global field in Properties and used it as a basis for a Value List with fields Property::PropertyID.PK and Property::Address1.


            It seems like this arrangement returns the propertyID.pk value and not the address.  I presume the address is just a way to 'remind' the user which property is being specified?  In this situation, the real work is done by the script which assigns a variable $$property to that PropertyID.pk.  Then I switched to the Transactions layout and did a Find on $$property in the PropertyID field.  Once the correct 'set' was determined I just switch back to my original layout and the correct records are there.


            Thanks for your help...


            Onward through the fog....

            • 3. Re: Value List changes Foreign Key ????

              Hi synergy46


              When you use two fields in a value list the field you use the value list on will only populate with the value from the first field you specified, in this case the ID, and your on the right lines, this just makes it easier to find a record from the list by name rather than numbers when you ultimately want the ID number for example.


              And this works the same way for both options of displaying the value of the first field or not.