10 Replies Latest reply on Feb 22, 2010 1:30 PM by philmodjunk

    Partial Value List?

    spectre6001

      Title

      Partial Value List?

      Post

      Background:

      Payment Detail Table >- Payment Table >- Payor Table -< Product Group Table -< Client Table

      also: Product Group Table -< Product Table

      The troublesome layout is based on the Payment Table with a creation-permitted portal to the Payment Detail Table. 

      Within the portal is a arrowed drop-down field for Product Number with a value list based on the associated field in the Product Table.

      I've tried setting it to "show all values" and "show related values starting with..." (I've tried multiple tables). At best it will display the values from one Product Group (always the same Product Group) for a total of five Product Numbers (no scrollbar present or needed) and nothing more. 

      How do I get the value list to display the Product Numbers associated with the Payment's respective Payor's Product Groups? Thanks in advance.





        • 1. Re: Partial Value List?
          philmodjunk
            

          It sounds like you have the following match fields in your relationships:

           

          Payment Table:: PaymentID = Payment Detail:: PaymentID

          Payor:: PayorID = Payment Table:: PayorID

          Payor::???? = Product Group::????

          Client Table::???? = Product Group::????

          Product Group::GroupID = Product Table::GroupID

           

          Can you fill in the fields where I have ???? and confirm if I have the rest correct?

           

          I think you'll need to add a table occurrence that links Payment Details directly to the Product Table, but don't yet see all the details to know that for sure.

          • 2. Re: Partial Value List?
            spectre6001
              

            I believe this will fill in the "????"s and put us on the same page. My relationship notation (keeps things straight in my head anyway) for what it's worth: One -< Many; Many >- One.

             

            Payment Detail Table:: PaymentID >- Payment Table:: PaymentID

            Payment Table:: PayorID >- Payor Table:: PayorID

            Payor Table:: PayorID -< Product Group Table:: Payor ID

            Product Group Table:: Product GroupID -< Client Table:: Product GroupID

            Product Group Table:: ProductID -< Product Table:: ProductID


            • 3. Re: Partial Value List?
              philmodjunk
                

              Shouldn't that last line be:

              Product Group Table:: Product GroupID -< Product Table:: Product GroupID

               

              Doesn't make sense to me the way you've posted it. Perhaps a typo?

              • 4. Re: Partial Value List?
                spectre6001
                   You are correct. Typo. Sorry.
                • 5. Re: Partial Value List?
                  philmodjunk
                    

                  Ok, I had to play with a sample database here. I was concerned about the number of Table Occurrences the conditional value list had to filter through to get from payment details to Product IDs.

                   

                  The following conditional value list works for me and if you send me a private message with an email address, I can email it to you.

                   

                  Here's the set up:

                   

                  Define your value list to bring ProductID's from the Products table, Starting from Payments.

                  • 6. Re: Partial Value List?
                    spectre6001
                       I think I did what you've said (PM on its way) and it didn't quite work. The problem is that the Product Name (the desired conditional value) and the ProductID are not the same. I'm surprised Filemaker would be in some way limited in the number of table occurrences it is able to go through to pull a related field. 
                    • 7. Re: Partial Value List?
                      philmodjunk
                        

                      You need an extra table occurrence:

                       

                      Payment Detail Table:: ProductID = Product Table 2:: PRoductID (Product Table 2 has the same data source table as Product Table).

                       

                      Put a name field from  Product Table 2 or define a looked up value that draws from it and it'll work. You'll find that also in the demo file.

                       

                      This isn't a limitation of filemaker. If you trace through your relationships, you'll find that your record in payment detail matches to a record in Products via PayorID instead of product number. Thus, you see the name of the first Product from the first Group with that Payor Number instead of matching by product number.

                      • 8. Re: Partial Value List?
                        LaRetta_1
                          

                        PhilModJunk wrote:

                        The following conditional value list works for me and if you send me a private message with an email address, I can email it to you.


                        There are free file sharing services where you can place your file for others to access, such as 4shared

                         

                        Since this forum doesn't allow attachments, it is a good option and it keeps the discussions and information on the forum where they should be.

                        • 9. Re: Partial Value List?
                          spectre6001
                            

                          "Define your value list to bring ProductID's from the Products table, Starting from Payments."

                           

                          I must have done something weird when I tried this earlier.... I tried it again today and it did the trick. I cleared out all of the faux entries and started over with new ones just to make sure all of my scripts were working between then and now, so that might have had something to do with it.

                           

                          What's the logic behind starting from Payments? What does "starting from" mean in this context? Thanks.

                          • 10. Re: Partial Value List?
                            philmodjunk
                              

                            "starting from" identifies the "point of view" from which your relationship will be evaluated. You could have several table occurrences that all refer to the same data source table. The starting from drop down is used to identify which table occurrence to start from. The link from this table occurrence to the table supplying the values in the value list is the "filter" that controls which values are included in the list.

                             

                            Here's a more in dept hlook at tables, table occurrences, relationships and how they affect filemaker, if you're interested:

                            Table vs. Table Occurrence (Tutorial)