8 Replies Latest reply on May 23, 2009 1:48 PM by KIDO

    Value List not working...

    synergy46

      Title

      Value List not working...

      Post

      I have 2 tables:

       

      Table 1 Customers

         CustomerID.pk

         CustomerName

       

      Table 2 Transactions

          CustomerID.fk

          TransactionID.pk

          TransactionDate

          TransactionAmount... etc

       

      Customers is 1 to many with Transactions with "Allow record creation..." checked for Transactions.

       

      The layout is based on Customers

      The Portal is based on Transactions.

       

         On the Portal, I have put Transactions::CustomerID.fk as a popup with Display Values From Fields... Customers::CustomerID.pk and  Customers::CustomerName

       

         Above the portal, for my own information, I have placed Customer::CustomerID.pk

       

         THE PROBLEM:

              As I select Transactions::CustomerID.fk popup values, the Customer::CustomerID.pk record pointer DOES NOT CHANGE.  (I can see this in the field above the portal)  This results in weird behavior in the portal field Transactions::CustomerID.fk.   For example, when I click out of the portal, whatever portal rows that are NOT equivalent to Customers::CustomerID.pk  are DELETED????

       

      I am stumped.... Got any ideas?

       

      Thanks for reading.

        • 1. Re: Value List not working...
          KIDO
             <!--            @page { margin: 2cm }           P { margin-bottom: 0.21cm }        -->

          Hi Snergy46

           

          Your portal is displayed in the context of Customers table. When you change the value in Transactions::CustomerID.fk that record becomes related to a different Customer rather then the one selected in Customers and disappears (changes its relationship) when you click out of the portal.

          It seems to me that you should display Customers in the context of Transactions instead.

           

          Regards

          • 2. Re: Value List not working...
            fabix
               it seems you should refresh the windows with a script after customer selection. try to add a script on field exit.   
            • 3. Re: Value List not working...
              synergy46
                 It sounds like you understand the problem but your solution says to me that I should base my  Layout on Transactions and the portal should be based on Customers (The one side)???  I did this and it did not work...
              • 4. Re: Value List not working...
                KIDO
                   <!--            @page { margin: 2cm }           P { margin-bottom: 0.21cm }        -->

                Each transaction is related to a customer through CustomerID.pk in Customers table and CustomerID.fk in transactions. If you change the value in transactions, that record becomes related to the new value, e.g., supposing that transaction #8 belongs to CustomerID.pk #1, when you change CustomerID.fk to #3 transaction #8 no longer relates to CustomerID.pk #1, but to CustomerID.pk #3.

                This is true no matter which table is in the portal. As it is, your structure should work when adding new transactions or amending the transaction customer. Perhaps if you explain what you are trying to achieve, we may find a suitable solution.

                 

                Regards

                • 5. Re: Value List not working...
                  synergy46
                    

                  hmmm... there definitely is something here I don't 'get'.

                   

                  Transactions::customerid.fk is the first field in a portal row.   That portal is based on Transactions.  I thought that if Transactions::CustomersIDfk was hooked to  a Value List based on the CustomerID.pk and CustomerName in Customers that each record created in the (Transactions) portal would create a CustomerID.fk equal to the Customers::CustomerID.PK????   

                   

                  It clearly doesn't do this.  I have put the CustomerID.pk field above the portal and seen that when I make a popup selection in CustomerID.fk, and unless I make a selection in CustomerID.fk that is = CustomerID.pk, when I click out of the portal, all rows that have differrent values are deleted.  However, it seems the added portal rows are written to the Transactions table.  Go figure.

                   

                  I have 3 FM books including "FM Pro 10 In Depth" and each leads me to the conclusion that the layout is based on the "ONE" and the portal is based on the "MANY"  in a one-to-many relationship.  This leads me to think that using the Value List is the crux of what I don't 'get'.

                   

                  Thanks for your help.

                   

                  Ron

                  • 6. Re: Value List not working...
                    KIDO
                       <!--            @page { margin: 2cm }           P { margin-bottom: 0.21cm }        -->

                    Your thinking is correct. If your layout is based on Customers and the portal based on Transactions, then each new record will create a CustomerIDfk equal to the RELATED CustomerIDpk edited or hooked in Customers. You don't need the value list in Transactions, although it might be handy in Customers.

                     

                    • 7. Re: Value List not working...
                      synergy46
                        

                       

                       

                       

                      Thanks for the confirmation.  But it does beg the quesiton "why doesn't this work as we both expect it to work?"

                       

                      Also, can you elaborate on what you mean by " You don't need the value list in Transactions, although it might be handy in Customers."

                       

                      PS.  I just created another "very simple" database (diagram above) and it too does the same weird failure to update transactions::customerid.fk after the customer name is selected.  Is this a FM "feature" or is it a "bug"????

                       

                       

                       

                       

                      • 8. Re: Value List not working...
                        KIDO
                           <!--           @page { margin: 2cm }           P { margin-bottom: 0.21cm }       -->

                        Hi

                        Sorry for the delay, but I've been out all day and only now read your post.

                         

                        It's not a bug. What happens is that FM portals only reorganize data when one leaves ( or click out) the portal, unless it is forced to do it by other instruction or script. If you're using FM 10, you can test this by writing a one line script – Sort Records [Restore; No dialog] – and assign it to the OnObjectModify script trigger of your field Transactions::CustomerIDfk in the portal. When you change this field the update will take place immediately. You may need to first define a sort field and direction in your portal setup or in the relationship.

                         

                        Elaborating my comment: From an end user perspective and if your layout is based on Customers, the value list would be handy on selection of customers for data entry, once Transitions::CustomerID.fk will automatically assume Customers::CustomerID.pk value in every new record in the portal.

                        And of course the value list might be useful to correct transactions as well, which should be minimised.

                         

                        Regards