5 Replies Latest reply on Sep 24, 2012 2:30 PM by philmodjunk

    Best way to "look up" customer records in a production application

    danphillips

      Title

      Best way to "look up" customer records in a production application

      Post

            

           Hello,

           To start off with, I am new to FMP.  I am trying to determine the best way to select a customer from a list and then moving to and updating that record.  I have used Access in the past as was able to easily design a drop down list to select which customer I wanted to update.

           I know FMP has the search feature but this requires a lot of moving around and the possibility of returning multiple records.

           I have 3 basic questions: 

           1.       What kind of lookup options are developers using for their applications?  (usually a development package leans to “one best solution”)

            

           2.       Is it feasible to develop in FMP a drop down list for a user to select a customer from, and then move to this record for update? 

            

           3.       If this drop down list is feasible, would it be easier / better to put a “add new Customer” button next to the drop down list, or could the user just type the customer name in, and if it does not match an existing Customer, add a new customer record and move to the first field.

            

           I know all things are possible, but I’m not interested in reinventing the wheel or fighting gravity. I’ll go with the flow and fall in line if I must.

           Assume that:

           Customer Name is unique

           Limited number of Customers (300)

            

           Thanks,

           Dan

        • 1. Re: Best way to "look up" customer records in a production application
          philmodjunk

               1) While drop down lists and pop up menus in FileMaker cannot have all the sophistication of an SQL based combo box in access, there are a number of options you can use for finding a record. WHich is best often depends on the design of your database, the layout and what else you want to do with the layout.

               2) You can define a value list of either just names or an ID number field in field one and a name in field 2. Since you can't specify more than two columns of data like you can in a combo box in Access, often it is necessary to make field 2 a calculation field that combines data from several fields. You can set a script trigger on the field formatted with such a value list that performs the find to pull up the record. If you use a drop down list, OnObjectSave makes a good choice, for pop up menus, radio buttons and check boxes, OnObjectModify is a better option. (In a drop down list, you can type info into the list and each keystroke would trip the onObjectModify button, hence using OnObjectSave makes for a better trigger in that case.

               Here's a sample script:

               Enter Find Mode []
               Set field [YourTable::YourField ; Yourtable::gSearchField ] ---gSearchField is the field with the value list and it should have global storage specified
               Set Error capture [on]
               Perform Find[]
               If [Not Get ( FoundCount ) // no records matching this criteria were found]
                  Show Custom Dialog ["Do you want to create a new record with this info?]
                  If [ get ( LastMessageChoice ) = 1 //OK was clicked]
                    New Record/Request
                  End IF
               End If

               More sophisticated options are also possible.

          • 2. Re: Best way to "look up" customer records in a production application
            danphillips

                  

                 PhilModJunk,

                 I have worked on this for many hours and having trouble – one time I was able to enter the “set field” command, but for some reason it did not save.  I have tried many times to enter this into the script editor.

                 So, Here is what I have:

                 Customer

                   CustID

                   CustName

                   Address…….

                  CustNameGlobal  (Using global storage option under Storage)

                  

                 Created a look up

                 Name: CustLookup

                 From field: Customer::CustName

                  

                 Back to Layout:

                 Select CustNameGlobal

                 Inspector:

                 Display from: Customer::CustNameGlobal

                 Control Style: Drop-down List

                 Values: Custlookup

                 Nex I tested the dropdown  – the drop down works fine – change data in CustName changes in the drop down

                  

                 Ok, now to enter the sctipt:

                 Manage Scripts:

                 New

                 Name script as CustScript

                 Select:  Enter find mode[]  (remove pause)

                 Select:  Set Field[]

                 Select Set Error Capture [On]

                 Select Perform Find[]

                 Select: If[]

                              EndIf

                  

                 Then I go back and attempt to edit the “set field” command and all I can do is select  a field name in the database.

                 This is what I get:   Set Field[Customer::Custname]

                 and I can not enter the rest of the command!

                  

                 Yes, Its late but I must be doing something wrong here.  Please help.

                  

                 Dan

            • 3. Re: Best way to "look up" customer records in a production application
              philmodjunk

                   When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

              • 4. Re: Best way to "look up" customer records in a production application
                danphillips

                      

                     First I’d like to thank Techphan for his patients and wisdom.  You first post was correct and precise. It was my inexperience that caused the problem.

                     Second, I’d I like to apologize to the community for my double post.  I routinely visit a hobby site (free) that is flooded with thousands of posts daily - older posts are rutinly ignored.  This is my first “paid forum” and I can now see that the rules / philosophies are much different.  It’s encouraging to see how civilly and professionally things are run and just how few posts there are.

                     Thank you all, I am glad I joined and hope to get up to speed ASAP.

                • 5. Re: Best way to "look up" customer records in a production application
                  philmodjunk

                       This forum is free and open to all.

                       One way to bring a post back into the forefront is to post your own replay to it. The activity this produces moves it back to the top of "recent items".