Need a way to make a customer selection list(?)

[FoxPRO01]

Me again, In the program I'm writing I need a way for the user to search for a customer's last name/ID Number and have the first names, last names, and ID numbers populate a list or something where the user can select a name to continue. Which object should I use and how would I go about populating it?

 

[mm0000]

There are a couple of examples in the FAQ section

How to make an incremental search in Grid

http://www.tek-tips.com/faqs.cfm?pid=184&fid=1214

How to create an "auto-fill" combox (Like internet explorer) in a prg format

http://www.tek-tips.com/faqs.cfm?fid=1812

 

[Mike Lewis]

I would use a simple grid for this. In summary:

1. Put a grid on your search form. Start by making it invisible (thisform.Grid1.Visible = .F.).

2. Do the search. Capture the search results (first name, last name, etc) in a cursor.

3. Bind the cursor to the grid (thisform.Grid1.RecordSource = "MyCursor"). Make the grid visible.

4. If the search doesn't produce any results, skip step 3. Instead, display a message saying the person was not found.

5. In the InteractiveChange of the text box (where the user enters the search term), make the grid invisible, ready to repeat the procedure for the next search.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[JRB-Bldr]

Another way that I have done this type of thing was to use a 2nd Search form.

So as not to clutter up the already 'busy' primary form, I put a button on it and used that button's Click Method to open up a 2nd form (modal) on which the user could either use the reference table's Indices (Fname, Lname, ID_No, etc.) to organize the 2nd form's grid and find the desired individual.

Additionally the user could enter into a Text box the desired name (or part of a name) and the Text box's Valid Method would find the matching entries and bring them to the center of the grid.

NOTE - Don't forget that most methods of searching are case-sensitive and that the use most often doesn't know how the data was originally entered. Therefore make your search routines NOT Case-sensitive so that it can find the desired records regardless of how it might have been originally entered.

Then when the user clicked on the Select button on that 2nd form, the values would be brought back into the primary form and displayed.

Good Luck,
JRB-Bldr

 

[FoxPRO01]

How do you make a search not case-sensitive? That is also something I've been trying to figure out for a while but have had no luck.

 

[Olaf Doschke]

Non case sensitive search: Either you UPPER() or LOWER() the data you search, or you search with ATCC().

Bye, Olaf.

 

[NasibKalsi]

To expand on Mike' solution.

1. Leave the Grid Visible all the time.
2. Have a TextBox, where user can enter FirstName/LastName or ID
3. At the Point of Enter (if table size is not very big and your PC Speed is good) use A, else use B(preferable)

A. t_ToSearch = alltrim(upper(thisform.txtSearchString.value))
locate for at(t_ToSearch,upper(LASTNAEME+','+LASTNAME+','+ID) > 0 && From Olaf'
* If ID is numeric then use str(ID) in the above expression.
if found()
else
endif
You may need to take care of duplicates by issuing 'continue' command.

B. Create 3 indexes and set one according to user click on the header of the Grid. then issue a 'seek' command. Create indexes with upper() function i.e. index on upper(FIRSTNAME) to .....
If you want to save 'header click()', then iterate the 'set order to ' amomng your 3 indexes or until found().
If the grid is still visible, the user can select one from the duplicate(s).
You can also implement partial search by removing the last char/digit until found(), etc. For example, if 'Bryan' is not found, then try 'Brya', 'Bry', 'Br', 'B', etc.

If found(), Record pointer will be pointing to the found record, else issue 'Not Found Message'

Nasib

 

[FoxPRO01]

So I have decided to go with the grid method, but my next question is how do I only display certain fields of the database, like there are some things that I don't want the user to be able to change, and secondly how do I actually make the user able to select the name from the grid?

 

[NasibKalsi]

I thought your question is very specific, but from your above post it looks like that is very general. I guess, others may have the same impression.

Please review if you know the following
1. How to create a table
2. How to Add, Browse, Change, Delete records ?
3. How to do 2 programatically ?
4. How to add a Grid on the form ?
5. How to browse the table using Grid ?

Let us know which point you are at, and the recommendations will follow.


Nasib

 

[FoxPRO01]

I've done everything, the grid, the search and all the main stuff is finished. I just need to know how to let the user select a customer from the grid.

 

[Olaf Doschke]

The grid is not the normal control to choose from. Nevertheless if you click on the grid, that row get's active. So the record pointer of the table or cursor shown in the grid is the picked customer.

Assume the table is called customer, then customer.id is the picked customer ID after the user picked one. Initially the first record is the active one.

If you want a control, which only has a picked record, if the user actively picked one, use the combobox instead. You can use it in combo mode, so the textbox part of the control is where users could write their search term.

Bye, Olaf.

 

[Mike Lewis]

The grid is populated by a cursor that contains your search results. That cursor should include the customer ID. When the user clicks on a customer in the grid, VFP will move the record pointer to the corresponding record in the cursor.

In the grid's AfterRowColChange event, look at the ID in the current record in the cursor to find which customer the user is interested in. Then, in your main customer table, seek that record, and refresh the main customer form. That way, as the user moves the grid highlight through the search results, the selected customer's details will be displayed.

You also need to make a decision as to whether to show the full customer details and the search results on the same form, or to use two different forms. Personally, I would go with the latter - along the lines that JRB suggested (above). The main form would have a search button, which opens a (modal) search form. Once the user has selected a customer from the search results, you close the search form and refresh the main form to show the selected customer. But that's just one way to handle it. You should try the different methods and decide for yourself.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[NasibKalsi]

I am repeating to what is already responded in the above posts (Olaf/Mike)

Example, to show 'FirstName', 'LastName' and 'ID' in the grid.
Assuming grid name is 'Grid1'
in the init() of the form add the following code. All this can be done through IDE as well.

with thisform.Grid1
    .columncount = 3
    .recordsource = 'customer' && Alias of your search table
    .deletemark = .f.
    .readonly = .f.
    .recordmark = .f.
    .gridlines = 1
    .gridlinecolor = rgb(235,245,235)
    .gridlinewidth = 2
    .scrollbars = 2 && Vertical

    .column1.width	 = 60
    .column1.controlsource = "customer.FIRSTNAME"
    .column1.Header1.caption = "FirstName"
    .column1.Header1.fontbold = .f.
    .column1.Header1.fontunderline = .f.
    .column1.readonly = .t.
    .column1.fontsize	 = 10

    .column2.width	 = 60
    .column2.controlsource = "customer.LASTNAME"
    .column2.Header1.caption = "LastName"
    .column2.Header1.fontunderline = .f.
    .column2.readonly = .t.
    .column2.text1.fontsize	 = 9

    .column3.width	 = 60
    .column3.controlsource = "customer.ID"
    .column3.Header1.caption = "Cust ID"
    .column3.Header1.fontunderline = .f.
    .column3.readonly = .t.
    .column3.text1.fontsize	 = 9
endwith

Add some 'TextBox' controls such as 'FirstName', 'LastName', 'Custmer ID', and more.
Then in the 'AfterRowColChange()' event assign values to these controls such as

thisform.txtFirstName.value = FIRSTNAME
thisform.txtLastName.value = LASTNAME
thisform.txtCustID.value = ID
thisform.txtOtherField.value = OtherField && Any other field

When using 'Up' 'Down' keys on Grid1, the contents of the CURRENTLY SELECTED record will be reflected in the 'TextBox' controls (thisform.txtFirstName, thisform.LastName, etc)

Note: FIRSTNAME, LASTNAME and ID are the field names of the search table.

I hope this will help.


Nasib

 

[FoxPRO01]

thisform.grid1.RecordSource="CustCur"
SELECT * FROM PAWN69 WHERE (UPPER(alltrim(thisform.text1.Value))=UPPER(ALLTRIM(PAWN69.last)) OR ALLTRIM(thisform.text3.Value)=ALLTRIM(pawn69.id)) INTO CURSOR CustCur

Using this method (PAWN69 is the customers table) the grid returns the values of all the customers in the database, not limiting to the search criteria, it just takes all the customer from the table and puts them into the cursor -> The Grid

 

[Olaf Doschke]

You create a cursor first, then set grid recordsource. Even if doing it in the right order, when requerying a cursor your grid will go blank. That doesn't explain the wrong filtering.

Don't use ALLTRIM(field), neither in the field list, nor in conditions, it will render indexes useless and your query will not be optimizable. Embrace the way foxpro compares strings and use it to do partial comparisons without LIKE clauses or trimming. Also in case of ORing conditions, if either text1 or text3 is not giving, you allow any record. You need to check, whether a filter is set and only filter, if there is a value entered.

lcLast = UPPER(ALLTRIM(thisform.text1.Value))
lcID = PADR(thisform.text3.Value,Len(Pawn69.ID))

then... WHERE (NOT (m.lcLast=="") AND UPPER(PAWN69.last) = m.lcLast) OR (NOT m.lcID=="" AND PAWN69.ID == m.lcID)

Also I padded lcID to the length of the ID field. This way it always has the full width and only a certain ID is accepted, not the begin of an ID. If you don't want that you can also use ALLTRIM() again. If you want that, you can also delete the NOT .mlcID="" part, as that always will be .T., because of the paddfing. Is ID really a char field? If not this won't work anyway.

In short: Think more about what empty values and boolean algebra cause.

Bye, Olaf.

 

[Olaf Doschke]

Also read about SET EXACT (which by the way has no influence over query string comparisons but explains VFPs string comparison) and SET ANSI (which affects queries).

Bye, Olaf.

 

[Mike Lewis]

Using this method (PAWN69 is the customers table) the grid returns the values of all the customers in the database, not limiting to the search criteria

If the grid is showing all the customers, but your cursor (CustCur) is showing only the search results, then you need to remove the grid's RecordSource before doing the query, and re-set it afterwards:

thisform.grid1.RecordSource=""
SELECT .... INTO CURSOR CustCur
thisform.grid1.RecordSource="CustCur"

Be sure to do the above in the order shown.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads