10 Replies Latest reply on May 26, 2015 6:25 PM by burghfan

    value list issue

    burghfan

      Screen Shot 2015-05-26 at 3.15.20 PM.png

       

      In my solution I have a "Proposal" table that is related to a "Customer" table and to a "CustomerCont" table. "Customer" and "CustomerCont" are also related.

       

      The error message occurs when a try to select a name of a contact from a drop down list. Drop Down List is from a related only value list.

       

      The correct names show up but I get the above error message.

       

      What also occurs is the the corresponding Name, Email and Phone fields get populated by the first record from the Value List.

       

      I think I'm close but stump as to what step I've missed.

       

      Screen Shot 2015-05-26 at 3.23.32 PM.pngScreen Shot 2015-05-26 at 3.23.57 PM.pngScreen Shot 2015-05-26 at 3.24.25 PM.png

        • 1. Re: value list issue
          coherentkris

          Check to see if the field attached to the drop down list is set to be the calculation data type or if the "prohibit modification during data entry" flag is set

          • 2. Re: value list issue
            burghfan

            I checked that and a made the change, however now when I select a name from the list, the id_customer_contact number gets changed to that of the first CustomerCont in the list.

            • 3. Re: value list issue
              erolst

              How is your conditional value list defined – via what relationship does it get its values?

               

              What is the point of relating Proposals to itself via customer_contact_id?

               

              Why do you have a customer_contact_id in the Customer table?

               

              (And was it really sensible to skimp on three characters when naming the “CustomerCont” table?)

              • 4. Re: value list issue
                burghfan

                How is your conditional value list defined – via what relationship does it get its values?

                 

                first table "CustomerContact" (first field "id_customer_contact" sorted by second field "full name" ) getting the related only records from the "Customer" table

                 

                What is the point of relating Proposals to itself via customer_contact_id? That was the name I gave the Relationship the two Tables are: CustomerContact and Proposal

                 

                Why do you have a customer_contact_id in the Customer table? Not sure but I removed, I was thinking I needed it for the related value list

                 

                (And was it really sensible to skimp on three characters when naming the “CustomerCont” table?) Changed (see above)

                 

                A question for you;

                 

                Should the "CustomerContact" table be related to the "Proposal" table or should it be related to the "Proposal_Item" table, or neither?

                • 5. Re: value list issue
                  erolst

                  Your original problem probably stems from trying to write into the wrong field (one that is on the layout, but not (yet) valid via its relationship) – which can happen if you have fields where they don't belong, with names of legitimate fields.

                   

                  So let's start from the beginning:

                   

                  Though you didn't state this explicitly, what you're trying to do is set a contact_id, using a value list to constrain the list of contacts to those that belong to the selected company.

                   

                  With a basic structure of

                   

                  CustomerContact >-- Company --< Proposal


                  and an implementation of

                   

                  CustomerContact::id_company >-- Company::id / Company::id --< Proposal::id_company / Proposal::id_customerContact >-- CustomerContact_forProposal::id

                   

                  you create your conditional VL using fields from CustomerContact, starting from Company; use that VL to format Proposal::id_contact.

                   

                  Once you have entered an exiting companyID value into Proposal::id_company, the conditional VL will display the contacts for that company. (Otherwise, <no values defined>)

                   

                  To display (or – if you really must – copy) data of the selected contact, use the TO CustomerContact_forProposal.

                   

                  You have done some, and maybe all of these things already; so consider this a checklist.

                   

                  Also, eliminating confusingly named and/or wrongly placed fields (or avoiding these mistakes from the get-go) and giving TOs meaningful/correct names goes a long way towards preventing issues like this one.

                   

                  burghfan wrote:

                  Should the "CustomerContact" table be related to the "Proposal" table or should it be related to the "Proposal_Item" table, or neither?

                  This is the first time you mention a ProposalItem table …

                   

                  Let me put it it this way; structurally, CustomerContact is related only to Company.

                   

                  Where you will put another TO of CustomerContact (to implement a “lookup” / display data from that table) depends on where you place a foreign id_customerContact – in Proposal, or in ProposalItems, or both – and that in turn depends on which of these places a contact belongs to, according to your business rules. So be clear about what the role of a contact within a proposal and/or its line items is.

                   

                  burghfan wrote:

                  What is the point of relating Proposals to itself via customer_contact_id? That was the name I gave the Relationship the two Tables are: CustomerContact and Proposal

                  Right; I see now that this is a relationship between Proposal and CustomerContact; but the name you gave the CustomerContact TO is not a good one …

                  • 6. Re: value list issue

                    You have entered what I refer to as the Random Naming of Things zone.

                     

                    This is when we name things in what seems to be an understandable way but later gets confusing.

                     

                    After passing through this zone and in search of a better method, I came up with this personal technique. Others have their own.

                     

                    When I link two tables, I create a new TO for the child table. I then rename it in the fashion Parent | Child.

                     

                    It is amazing how much easier it is to find the related tables when in search of a field using this type of naming. The all line up nicely under the parent table.

                     

                    The naming can be further expanded by something like Parent | Child | What I am doing with this TO etc.

                     

                    In your example you might have, with the name on the right being the child

                     

                    Proposal | Customer

                    Proposal | Items

                    Proposal | Followup

                     

                    Customer | Proposal

                    Customer | Invoice

                    Customer | Followup

                     

                    See how nicely things line up?

                     

                    Keep in mind that FIleMaker does not have a one to many relationship, that is your problem in the field definitions, etc.

                     

                    Currently FileMaker has 7 types of relationship and you could indicate that but be careful as some characters may cause problems:

                     

                    | equal |

                    | not |

                    | equal or greater than |

                    | equal or less than |

                    | greater than |

                    | less than |

                    | all |

                     

                    Unfortunately some of FileMaker's dialogs are really in need of modernization and even the new one's are inadequate for showing long names.

                     

                    I think you will find that if you adopt a more logical, scientific if that is a good word, naming convention you will understand what you are trying to do.

                    • 7. Re: value list issue
                      burghfan

                      Thank you for your response;

                       

                      If I understand you correctly, it would be beneficial to rename the "Customer" table to "Company"

                       

                      Another suggestion would be to always name the Primary Key field in every table "id" then place the table name behind "id_" as the foreign key in any related table.

                       

                      Example: to relate the "Company" table to the CustomerContact" table it would be:

                       

                      Company Primary Key = id

                      CustomerContact Foreign Key = id_company

                       

                      Company:id = CustomerContact:id_Company and allow new CustomerContacts to be created from the "Company" table

                       

                      Is this what you showed me here "CustomerContact::id_company >-- Company::id" ?

                       

                      I apologize for not mentioning the "PropItem" TO

                       

                      The current work flow for the solution is as follows;

                       

                      We currently generate a new "Proposal" from the "Customer TO" (which I could change to "Company") where the desire to have only related "CustomerContacts" available is that some of the "Customers/Companies" have multiple "Contacts" that we might want to send a proposal, and it would be nice to customize the proposal to include their "name", "email", "phone" without manually typing that information into fields. And other times the proposal is just going to the "Customer/Company" but not to a specific "contact". Plus contact come and go more so than companies.

                       

                      If I then follow your response correctly I would have the following relationships;

                       

                      CustomerContact >-- Company --< Proposal --< PropItem


                      and an implementation of

                       

                      CustomerContact::id_company >-- Company::id / Company::id --< Proposal::id_company / Proposal::id_customerContact >-- CustomerContact_forProposal::id / Proposal:id >-- PropItem:id_proposal

                       

                      Where each of the following are a TO

                       

                      CustomerContact::id_company >-- Company::id

                      Company::id --< Proposal::id_company

                      Proposal::id_customerContact >-- CustomerContact_forProposal::id


                      I added


                      Proposal:id >-- PropItem:id_proposal


                      Does " >-- " reflect how the two TO's relate to each other and in the example "CustomerContact::id_company >-- Company::id" would it be that the field "id_company" from the "CustomerContact Table" would = the "id" field from the "Company Table" and that new CustomerContacts could be created from the "Company" table.


                      Or am I off base here?


                      And what does " <-- " signify

                      • 8. Re: value list issue
                        erolst

                        burghfan wrote:

                        If I understand you correctly, it would be beneficial to rename the "Customer" table to "Company"

                        Not really; I just misremembered your name when writing this from memory. Use whatever name describes your entity best, and what you feel comfortable with.

                         

                        You could have a solution where all Companies are … well, companies, but only some of them act as customer … or is that client …? How to call that table? And would the foreign key be id_company – for the table – or id_client – more to the point in, say, an invoice table, but you'd have to remember that there is no Client table …

                         

                        burghfan wrote:

                        Another suggestion would be to always name the Primary Key field in every table "id" then place the table name behind "id_" as the foreign key in any related table.

                        Just a convention; in my mind, this makes unmistakably clear that this is the primary id of the current table, without any mental calisthenics. Any field that starts with id_ is by definition a foreign key. Avoids a bit of the ceremonial naming clutter (PK, FK etc.; though I can understand how that can help when these concepts are still fresh)

                         

                        burghfan wrote:

                        Example: to relate the "Company" table to the CustomerContact" table it would be:

                         

                        Company Primary Key = id

                        CustomerContact Foreign Key = id_company

                         

                        Company:id = CustomerContact:id_Company and allow new CustomerContacts to be created from the "Company" table

                         

                        Is this what you showed me here "CustomerContact::id_company >-- Company::id" ?

                        All correct, but note that the part in bold is an implementation detail; also, be aware that this setting only pertains to a portal based on the TO for which it is selected; you can always use a script to create related records from the context of the related table itself (or do it manually).

                         

                        burghfan wrote:

                        The current work flow for the solution is as follows;

                         

                        We currently generate a new "Proposal" from the "Customer TO" (which I could change to "Company") where the desire to have only related "CustomerContacts" available is that some of the "Customers/Companies" have multiple "Contacts" that we might want to send a proposal, and it would be nice to customize the proposal to include their "name", "email", "phone" without manually typing that information into fields. And other times the proposal is just going to the "Customer/Company" but not to a specific "contact". Plus contact come and go more so than companies.

                         

                        Then I'd write a script that captures the current customer's id plus a selected contactID, goes off to Proposal and creates a new proposal.

                         

                        Or use a portal to create and display contacts; via a button in the portal you could create a new Proposal for the current company and that contact.

                         

                        If you really need to copy over data from Contact, use auto-enter calculations in the counterpart fields in Proposal; but if you only need these (unchanged) data for display, or in an e-mail, don't copy them; reference them.

                         

                        burghfan wrote:

                        Does " >-- " reflect how the two TO's relate to each other

                        This is simply a shorthand for “many-to-one” (left-hand larger-than right-side); so this actually denotes the “cardinality” of the relationship. Adding the match fields is just a bit of added info, but not the actual point.

                         

                        burghfan wrote:

                        And what does " <-- " signify

                        A typo …

                         

                        btw, have you solved the initial issue?

                        • 9. Re: value list issue
                          burghfan

                          I was side tracked with a few phone calls (my other work can get in the way ) and will get back to work on the initial problem late this evening or tomorrow morning. I'lll post my findings.

                           

                          Thank you for all of the great information, it is very helpful.

                          • 10. Re: value list issue
                            burghfan

                            FYI, I got my initial issue all cleared up thank you very much the solution you provided worked great.

                             

                            Solution:

                             

                            CustomerContact::id_company >-- Company::id / Company::id --< Proposal::id_company / Proposal::id_customerContact >-- CustomerContact_forProposal::id

                             

                            you create your conditional VL using fields from CustomerContact, starting from Company; use that VL to format Proposal::id_contact.

                             

                            Once you have entered an exiting companyID value into Proposal::id_company, the conditional VL will display the contacts for that company. (Otherwise, <no values defined>)

                             

                            To display (or – if you really must – copy) data of the selected contact, use the TO CustomerContact_forProposal.