7 Replies Latest reply on Aug 27, 2013 11:52 AM by philmodjunk

    Using Find to Attach Customer to Request

    AmandaO'Connor

      Title

      Using Find to Attach Customer to Request

      Post

           Hello,

           This is my scenario.

           I have  'Customers' table, with a PK as 'Customer ID'. This is a serialized auto-generated value. There are several demographic fields attached to the Customer.

           I have 'Requests' table, with a PK as 'Request ID'. I want to attach the customer to the request. This can be a many to one relationship (many requests to one customer)

           I don't want to use the "Value List" as I could potentially have hundreds of customers. I am looking for a button, to enter the find mode of the 'Customer' table, locate the customer, and select the customer, and the Customer ID value will auto-populate into "Customer" field in the 'Request' record I am creating... and based on the customer ID in the record, field such as phone and address will auto-fill.

           Someone HELP! Please.

            I'm using File Maker Pro 12

        • 1. Re: Using Find to Attach Customer to Request
          philmodjunk

               My answer assumes that while a customer can have many requests, a given request is linked to only one customer.

               Perhaps a value list will still work if it could auto-complete on the customer's name in order to narrow the list quickly down to just a few potential matches:

               FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
               Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7

               You can also set up a search portal that starts with a list of all customers but that narrows down keystroke by keystroke as you type text into a search field and you can then select a specific customer by clicking that customer's row in the search portal: https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7

               As you are using FileMaker 12, you can open this file from the File menu to get a copy converted to .fmp12 format.

               And yes, you can set up a script that performs a find and either selects the one matching customer or that presents a list of multiple matching customers such that clicking a button on the layout of a list view listing those found records copies the customer ID into a variable and then switches back to your original layout in order to set an fk field in Requests to the customer ID from the variable.

               I'd use a global field where the user enters all or part of a customer's name--possibly with search fields for additional criteria that a script performed from that layout sets up the needed find requests, performs the find and then takes whatever action is appropriate.

               As a start in that direction, check out the scripted find examples in: Scripted Find Examples

                

          • 2. Re: Using Find to Attach Customer to Request
            SteveMartino

                 Do you have a Customer ID foreign key set up in Request Table? Do you have a relationship connecting the 2, allowing create records via this relationship? After that you'll need to set up a button and attach a script to capture the Customer ID as a variable, and set the fk Customer ID to that variable

            • 3. Re: Using Find to Attach Customer to Request
              philmodjunk

                   That's an excellent suggestion Steve.You could also use a portal to the request table on your customer layout and enter the request data directly into the portal.

              • 4. Re: Using Find to Attach Customer to Request
                AmandaO'Connor

                     Phil / Steve,

                     Thank you for the quick responses.

                     Portals - I'm using a portal for a "Customer Note" field, works perfect for that scenario. Not looking to use that for attaching an existing Customer Record to a new Request Record.

                     Auto-Complete Value-List - Looking more and more as if I may not have a choice but to use this.

                     I read several of the articles that Phil provided, definitely jump started my in the right direction. I created my "Requests" layout added the Customer ID, Customer Name, Customer Phone and Email fields. I added a push Button with the attached Script:

                     Go to Layout ["Customers" (Customers)]
                     Enter Find Mode []
                     Set Field [Customers::First_Name; Global::gCustomer]
                     Set Error Capture [On]
                     Preform Find []
                     If [not Get (Found Count ) // no records were found]
                         Show Custom Dialog ["Error"; "No Records Found"
                     Else If [ Get (FoundCount ) = 1
                     Copy [Select; Customers::Customer ID]
                     Go to Layout ["Requests (Requests)]
                     Paste [Select; Requests::Customer ID]
                     End If

                     I think I'm missing something. I typed in the Customer Name in the Name field and hit the button. At the time only that customer existed, so it appeared to work. It copied the Customer ID, and with other look-up logic attached to the Name, Phone and Email fields it auto-filled based ont he Customer ID.

                     I had three more Customers to the Customer Table, and tried the Push button again looking for a different customer, the behavior copied over the first record in the table. sad

                     I want to be able to search by Name, Phone or Email to locate the customer and copy over the Customer ID. If there are more than one result show the results and allow me to select the customer that I intended to find.

                     If no records are found, I want to be able to have an additional button that is a "Quick Add" Basically copies the vaules in Name, Phone and Email creates a record in the Customer table and copies the new Customer ID into the Customer ID field int he Requests record.

                     Any additional assistance would be GREATLY appreacited!

                     ** I do have the Relationship configured for the Customer ID Field for the Customers and Requests Tables

                     Thank you!

                     -Amanda

                • 5. Re: Using Find to Attach Customer to Request
                  philmodjunk
                       

                            I had three more Customers to the Customer Table, and tried the Push button again looking for a different customer, the behavior copied over the first record in the table.

                       And then what happened? The emoticon doesn't tell me how it failed for you.

                       I can see several issues with your script:

                       1) The script does not create any new requests record and thus overwrites the current request record witha  different customerID each time.

                       2) The script uses copy/paste instead of set variable/set field. That works, but can cause issues for your users when data they have copied to the clipboard mysteriously disappears, replaced by the number copied by your script to the clip board.

                       3) It's fully possible that your script might find more than one customer of the specified name. The script, as posted, does not have any code for handling the case where you get more than one customer. Perhaps you haven't gotten to that part yet or perhaps your Customers layout is a list view layout with a button for selecting a customer from the list of found records...

                       To resolve issues 1 and 2 modify your script like this:

                       Go to Layout ["Customers" (Customers)]
                       Enter Find Mode []
                       Set Field [Customers::First_Name; Global::gCustomer]
                       Set Error Capture [On]
                       Preform Find []
                       If [not Get (Found Count ) // no records were found]
                           Show Custom Dialog ["Error"; "No Records Found"
                       Else If [ Get (FoundCount ) = 1
                            Set Variable [$ID ; value: Customers::Customer ID]
                            Go to Layout ["Requests (Requests)]
                            New Record/Request
                            Set FIeld [ Requests::Customer ID ; $ID ]

                       End If

                  • 6. Re: Using Find to Attach Customer to Request
                    AmandaO'Connor

                         It failed by "[copying] over the first record in the table" instead of the desired record. I apologize that I didn't make that clear. The modifications below resolved that issue, as well as cleaning up the code to be more effecient per your suggestions. Thank you!!

                    Set Variable [$FirstName; Value:Requests::Customer First Name] // added this line
                         Go to Layout ["Customers" (Customers)]
                         Enter Find Mode []
                         Set Field [Customers::First_Name; $FirstName]
                         Set Error Capture [On]
                         Preform Find []
                         If [not Get (Found Count ) // no records were found]
                             Show Custom Dialog ["Error"; "No Records Found"
                         Else If [ Get (FoundCount ) = 1
                              Set Variable [$ID ; value: Customers::Customer ID]
                              Go to Layout ["Requests (Requests)]
                             -- New Record/Request // Took this out, I want it to replace on current record
                              Set FIeld [ Requests::Customer ID ; $ID ]

                         End If

                         I have not attempted the multiple records piece yet or the quick add... Any advice? I'll see what I can come up with, if any issues arise, I'll post.

                          

                         Thank you again!!

                    • 7. Re: Using Find to Attach Customer to Request
                      philmodjunk

                           I don't understand why you do not want the new record/request step.

                           You have specified that there will be many requests for one customer and without the new record/request step, this will not happen. And there is no guarantee with your script that the correct record in Requests will be overwritten with the specified customer ID. It is entirely possible that the wrong request record will be changed by your script as it does not include code for selecting a particular record in the requests table.