1 2 Previous Next 17 Replies Latest reply on Oct 2, 2009 3:03 PM by jeffd

    Value list for Customer Contacts



      Value list for Customer Contacts


      I am using FMpro 10v3.0 on windows XP operating system.

      I have 3 files, a Contact, Invoicing & quoting file. These files are related to one another via the contact id.

      the invoicing & quoting file pulls the contact info from the contact file by a drop down value, sorted by the customer name. My problem is this, I have customers who's names are exactly the same except for the town or city in which 

      they are located and the drop down value list only shows one of the names. How can I get all the names to show?

        • 1. Re: Value list for Customer Contacts

          The best approach is not to use names in your relationship. Not only can customers have non-unique names, but they change their names from time to time as well.


          Define a new number field in your Customer table and define it as an auto-entered serial number.

          Use this customer number to link your three tables.


          In your drop down you can set up a two column value list (do not hide the first column) where column 1 is the customer number and column 2 is the customer name.

          Place a copy of the customer name field from your customer table next to your customer number drop down, so that the customer's name is displayed next to the number you select.

          • 2. Re: Value list for Customer Contacts

            I have a contact id field that is auto entered upon creation by serial number. In my invoicing & quoting file, do I need a unique field # in these files or should (can) I link these two files to the contact file(contactid) via a customer name field?

            Right now I am linking to the contact file by the customer name field.

            • 3. Re: Value list for Customer Contacts

              Define matching number fields in the other two tables. Do not make them them auto-entered serial numbers. They should get their values from Customer ID. You'll need to update all your existing invoice and customer records with the correct serial number in these fields.


              If you have a lot of records to update, keep your relationships in their original structure, use Replace Field contents to update all your existing invoice and project records with the correct matching value (use the replace calculated value option). Now change your relationship to match records by customer number.

              • 4. Re: Value list for Customer Contacts

                OK Sir, I have this figured out except for one thing, in order for the value list to work properly (or to show all customers)

                the list has to sort by the first column (customer id field or # field). This causes the names to be out sort because the

                number field is sorting. Is there a way around this. I also need the value list to auto complete based on the customer name.

                • 5. Re: Value list for Customer Contacts

                  If you define your value list two display two columns of data (Customer number and Name), you can specify that the value list be sorted on the second column.


                  Another useful addition might be to set up a "customer search" field where you enter all or part of a customer's name and trigger a script to search the customer table for matching records. If one record is found, the script can insert it into your invoice record automatically. If there are multiple matches, the script can popup a list of matching entries for the user to select from. This field can be a customer name field formatted as a drop down list with auto-complete enabled to make it easier/faster to use.

                  • 6. Re: Value list for Customer Contacts

                    Thanks Philmodjunk, I finally got this relationship working like you suggested.

                    However, I'm not really sure how to replace the contents of a field via a calculated result.

                    Can you give me an example on how this might look?

                    I would also like to know how I can set up a customer search trigger for matching records.


                    Thanks for your help!


                    • 7. Re: Value list for Customer Contacts

                      What I suggested would take place before changing your relationships to use ID values instead of customer names. This trick would populate your existing records in your other tables with the needed matching ID values. Once that's done, you'd modify your relationships to match by Customer ID number.


                      A replace with calculated result.

                      Make a back up copy of your file. (This tool can modify a lot of records in your database all in one batch and you'll want a back up to go back to just in case it doesn't work like you expect it to.)

                      Locate or create a layout that lists the records from your invoices table and also displays the blank Customer ID field in from invoices.

                      Put your cursor in the field.

                      Select Replace field contents from the Records menu.

                      Click the "replace with calculated result" radio button.

                      Specify a calculation:


                      Click OK

                      Click Replace


                      Repeat for your quotes table.

                      Now build your relationships that match on customer ID number.


                      Try that and report back. Then I'll post the search technique.

                      • 8. Re: Value list for Customer Contacts
                           I also still have the original relationship in place where New Company Name(Invoicing Table) = Customer Name (Customer Table)
                        • 9. Re: Value list for Customer Contacts
                             As long as that's the relationship you refer to in your calcualted result, you should be OK. Note that you may have to spot check any records where you have customers with identical names.
                          • 10. Re: Value list for Customer Contacts
                               Allright, everything worked just fine.
                            • 11. Re: Value list for Customer Contacts

                              You asked for a search by name capability. We can combine an auto-complete field with a scripted button or script trigger to search your Customer Table for a customer by name.


                              I'm going to assume you have a customer name field that displays the name in the following format: LastName, FirstName. It could be a single text field or a calculation field that concatenates a first and last name from separate fields. Either way works for this technique.


                              Define a global text field and place it on your layout. Call it gSearchName

                              Format it as a drop down menu that specifys the above Name field. Enable the auto-complete option.


                              Create a layout based on your customers table and set up a list type layout where you see the customer name field and any other relevant fields you want to help distinguish between customers with identical names. (Your address and phone number fields are likely choices here.)


                              Write a script:

                              Go To Layout [ "ListLayout" (Customers)]

                              Enter Find Mode []

                              Set Field [Customers::Name ; gSearchName]

                              Set Error Capture [on]

                              Perform Find []

                              Set Error capture [off]

                              If [ get ( FoundCount ) = 0 ]

                                Go To Layout [original layout]

                                Show Custom Dialog ["No records found..."]

                              Else If [ get ( FoundCount ) = 1 ]

                                Set Variable [$CustID ; Customer::CustomerID]

                                Go to layout [ original layout ]

                                Set field [ Invoice::CustomerID ]


                                Sort [No Dialog ; Restore ] //Sort names in alphabetical order

                              End IF


                              Place a button next to the namefield in your list layout and give it this script:

                                Set Variable [$CustID ; Customer::CustomerID]

                                Go to layout [ "InvoiceLayout" (Invoices)]

                                Set field [ Invoice::CustomerID ]


                              To perform the first script, either set it as a script trigger that uses On Object Exit or assign it to a button next to the drop down.


                              That should do it. If you select a customer name that is unique, they'll automatically be assigned to the current layout. If the name matches duplicat entries, you'll see a list of matching entries and can easily select the correct name to assign it to your current invoice.




                              • 12. Re: Value list for Customer Contacts

                                I will try this & report back to you.

                                Thanks for everything.

                                • 13. Re: Value list for Customer Contacts

                                  This works very nicely! Thanks for your help!

                                  One more questions if you don't mind. Is these a way to prohibit data entry in a field if, say, there is data in another

                                  particular field, i.e. both fields can not have data in the same record.

                                  • 14. Re: Value list for Customer Contacts

                                    Option 1:


                                    Set a validation rule on both fields. (Open manage | database | fields and double click the field definition. Click the Validation tab.)


                                    On field1 set: Not isempty(field2)

                                    On field2 set: Not isempty(field1)


                                    Use the custom validation message to tell the user why they can't have data in both fields.


                                    Option 2:

                                    Use accounts and privileges to control access to each field, you can use the above expression here to do the same thing.


                                    Option 1, kicks in after the user has entered data in the second field with a revert button that reverts the field.

                                    Option 2, I think, can be used to keep the user from entering one field after inputting data into the other.


                                    Play with both options and see which works best for what you want.

                                    1 2 Previous Next