7 Replies Latest reply on Oct 27, 2009 5:28 AM by mrvodka

    HELP NEEDED - dynamic value list with lookup

    Alex_Riva

      Title

      HELP NEEDED - dynamic value list with lookup

      Post

      Please help me with this issue. I'm stuck.

       

      I have 3 tables: INVOICES , TOUR OPERATORS ,  CONTACTS

       

      A Tour Operator is a company who sends guests to our hotel. When we have a reservation, a new Invoice is created. One Tour Operator can have many contacts (people who work for that tour operator), and all these people can make reservations in our hotel, thus relating to an invoice.

       

      What I need is: 1st I want to choose a tour operator. Then, I want to choose a contact based on the existing contacts from that tour operator (dynamic value list). Finally, when i choose a contact, I want to lookup many variables related to that contact and copy them to fields in my invoice.

       

      I'm having problems relating these tables to make it work. Either the lookup doesn't work or the dynamic value list doesn't work. Please, I really need help!

       

      I started using Fmaker a couple months ago and I am studying a lot and reading many posts in the forum. I am using the Fmaker Version 10.0v3 on a Mac with Leopard OSX.

       

      Thank you! 

       

       

        • 1. Re: HELP NEEDED - dynamic value list with lookup
          Alex_Riva
            

          I wanted to give some more information to explain better what I need:

           

          When I create a new invoice, I need to link it to one tour operator. Every invoice is always linked to only one tour operator, but one tour operator can be linked to many invoices. 

           

          After choosing a tour operator, there might be 4 or 10 contacts who work for that company. Thus I want to choose one of these contacts. When I do so, I want to lookup information from that contact into invoice fields, such as phone number, full name, email and so on.

           

          I hope someone will be able to help! I really need it.

           

          Thanks a lot!

          • 2. Re: HELP NEEDED - dynamic value list with lookup
            mrvodka
               You can use lookups to grab the company data from the company table. Then you can use a conditional value list to select your contact for that company.
            • 3. Re: HELP NEEDED - dynamic value list with lookup
              Alex_Riva
                

              That's not what I'm looking for.

               

              What I want is:

               

              1. Fill an Invoice for a sale.

               

              2. While filling the Invoice, I want to choose one Tour Operator based on the Tour Operator table. That's a simple pop-up menu where I get the values from the Tour Operator Table.

               

              3. Having done that, then I want to choose a contact (person) that works for that Tour Operator. There are many contact for each Tour Operator. Only the contacts that work for that tour operator should appear in another pop-up menu of the Invoice. The contacts also have a table for themselves. 

               

              4. After choosing that particular contact, I want to lookup information from that contact into other fields of the Invoice.

               

              I don't want to use heavy scripts to do this.

               

              I found a solution in which I use a portal for the contacts and a button in which I use to lookup the records. But, is it possible to use a pop-up menu instead? What I'm trying to do is such a hard thing? Why don't I get replies from the forum? Thanks a lot! 

              • 4. Re: HELP NEEDED - dynamic value list with lookup
                mrvodka
                  

                Alex_Riva wrote:

                That's not what I'm looking for.

                 

                What I want is:

                 

                1. Fill an Invoice for a sale.

                 

                2. While filling the Invoice, I want to choose one Tour Operator based on the Tour Operator table. That's a simple pop-up menu where I get the values from the Tour Operator Table.

                 

                3. Having done that, then I want to choose a contact (person) that works for that Tour Operator. There are many contact for each Tour Operator. Only the contacts that work for that tour operator should appear in another pop-up menu of the Invoice. The contacts also have a table for themselves. 

                 

                4. After choosing that particular contact, I want to lookup information from that contact into other fields of the Invoice.

                 

                I don't want to use heavy scripts to do this.

                 

                I found a solution in which I use a portal for the contacts and a button in which I use to lookup the records. But, is it possible to use a pop-up menu instead? What I'm trying to do is such a hard thing? Why don't I get replies from the forum? Thanks a lot! 


                That IS what you are looking for. As I said, you will want to use a conditional value list to pick your Contact for the tour company. Your other fields in the invoice you want to lookup data from the contact table should be LOOKUP fields based off the relationship to the contact table based on the fkContactID ( foreign key ).

                 

                This doesnt involve scripts. I would research lookups and conditional value list.

                 

                 

                As for why you havent received any replies... 1. Well I guess I dont count...    2. This post has been the most informative post that you have posted regarding your issue.


                • 5. Re: HELP NEEDED - dynamic value list with lookup
                  philmodjunk
                    

                  You have two key options here when it comes to "looking up" related contact data:

                   

                  1. Dynamic data. Any time you update the related record, the new data automatically appears on your invoice. This is good for things like cell phone numbers and such.
                  2. Static, copied data. Data is physically copied from the related table. Updates to a related record do not update the data shown in the invoice. This is typically used for pricing information so that changes to the related table don't change values in existing invoices.

                  Which option do you want to implement?

                   

                  For option 1, you can display data in a portal of related records

                  For option 2, you set the field's auto-enter options to use a lookup or calculation to copy the data from the related table which is what Mr. Vodka describes.

                  • 6. Re: HELP NEEDED - dynamic value list with lookup
                    Alex_Riva
                      

                    Hello, thank you very much for your help. I solved this problem. I was actually looking for the option 2, but I was having problems with the relationship among the tables (i.e. I was not being able to pull the right information).

                     

                    I found a solution in this link that helped me very much. Even though it shows a relationship between 3 valuelists, the concept was very helpful and perhaps might be useful to others as well.

                     

                    http://www.fmforums.com/forum/showpost.php?pid/301789/

                     

                    All the best!