Search on customers.dbf

[Silvio.Falconi]

If a customer have the same surname and different name how I must create the search ?

I made the index
INDEX ON Upper(Cust->first)+upper(cust->last) tag Firstlast

cCognome:= cust-first
cNome:=cust->last

@ 26, 10 SAY "Cognome :" OF oDlg SIZE 34, 8 PIXEL FONT oFont
@ 24, 46 GET aGet[2] var cCognome OF oDlg SIZE 80, 12 PIXEL FONT oFont UPDATE

@ 26, 127 SAY "Nome :" OF oDlg SIZE 34, 8 PIXEL FONT oFont
@ 24, 146 GET cNome OF oDlg SIZE 100, 12 PIXEL PICTURE "@!" FONT oFont UPDATE


if I seach dbseek(cCognome+cNome) not run ok

any solution pls

[Otto]

Silvio,
remove space between the names.
Regards,
Otto

[Silvio.Falconi]

I try also with alltrim not run

[James Bott]

If I seach dbseek(cCognome+cNome) not run ok.

You have to convert the names to upper case.

dbseek( upper(cCognome+cNome) )

[Silvio.Falconi]

yes of course but -i remember I had on clipper+fw ( perhaps listbox) a possibility when I presses a Letter ok keyboard it showed me all customers initiating with that Letter.

my problem is another: I have two types of customers ..
1 reservation holder: the one who rented the umbrella
2. guests who are placed in each umbrella because they can be up to 4 (3 + 1reservation holder) for the normal umbrella and 10 (9 +reservation 1 holder) for hawayano type umbrellas.

in the archive I have inserted in addition to the surname and name of the owner of the booking also other 9 character fields (cr 30) where the end user can insert the names of the guests relative to that customer

the search must be done first on the booking archive, once a correspondence has been found with the name and surname of the customer who made the booking, the name must be searched for in the customer archive and selecting that record ie the name and surname found must be done a search in the 9 character fields to search for the name to be searched

or if I have to look for a guest not knowing in which umbrella is located I have to do a search on the customer archive to compare the name to be searched also in the 9 fields and then if I can trace the owner of the reservation I can see in which umbrella the guest was added

or do you have the best solutions?

[James Bott]

Silvio,

I don't know how familiar you are with relational database design, but this is the number one rule:

First Normal Form (1NF): A table is 1NF if every cell contains a single value, not a list of values. This property is known as atomic. 1NF also prohibits repeating group of columns such as item1, item2,.., itemN. Instead, you should create another table using one-to-many relationship.

So, in your rental file records you should not have multiple names as fields, but rather each name should be a record in a related table.

RentalID, Last, First

You can have any number of records in the name file with a minimum of one. This eliminates a lot of wasted space in the rental record file and it allows you to search for any name in the person file; just index on upper( trim(LAST) + trim(first) ). Then you can search for any name in any rental with one simple seek().

Alternately, in your case you could leave the renter's name in the rental file and just put all the "guests" in the related table. This would make it more difficult to get a list of renters plus guests though.

For more about relational database design see:

https://www.ntu.edu.sg/home/ehchua/prog ... esign.html

Unrelated to your current need, but note that you can create a method in the Rental Object (singular) class that returns an array of all the guests on that rental which you can then call from the object.

oRental:GuestList() // returns a list of people for that rental

This method would open the guests database and find all the guests on that rental and return the list as an array. This encapsulates the list as part of the rental object.

[Silvio.Falconi]

James,
on oldest app I had two dbf

1. Clienti.dbf (customers)
2. Ospiti.dbf (guest)

the second dbf had Numcli (4cr) to search it is the same of Numcli of clienti.dbf

sample : on each record of clienti.dbf ( customer) I had a button to insert the guests, for example, all family members of the customer

But Now I have problem on tdatabase( or tdata)

because on old app I calc the numcli in this mode :

Code: Select all  Expand view  RUN

 IF lNew
            (oDCli)->(DbSetOrder(1))
            (oDCli)->(DbGoBottom())
            cCli:=StrZero(Val((oDCli)->NumCli)+1,4)
            (oDCli)->(DbGoTo(nRec))
            aDat[ 2]:=Space(70)
            aDat[ 3]:=Space(50)
            aDat[ 4]:=Space(40)
            aDat[ 5]:=Space(50)
      else
            cCli:=(oDCli)->NumCli
            cObs:=(oDCli)->Observ
            cRfc:=(oDCli)->RegFed
            aDat[ 2]:=(oDCli)->RazSoc
            aDat[ 3]:=(oDCli)->CalFis
            aDat[ 4]:=(oDCli)->ColFis
            aDat[ 5]:=(oDCli)->CdeFis
endif

then on dialog
 REDEFINE GET oCve VAR cCli ID 101 OF oFld:aDialogs[1] PICTURE "@K9999";
                           WHEN lNew VALID Val_Cve(oCve,@cCli)

...
STAT FUNC Val_Cve(oCve,cCli)
   LOCAL lRet:=.T., nInd, nRec
   IF !Empty(cCli)
      cCli:=StrZero(Val(cCli),4)
      nInd:=(oDCli)->(IndexOrd())
      nRec:=(oDCli)->(Recno())
      (oDCli)->(OrdSetFocus(1))
      IF (oDCli)->(DbSeek(cCli))
         lRet:=.F.
         Tone(3000)
         MsgInfo("Già esiste il cliente "+cCli+"...","Verificare!")
      ENDIF
      (oDCli)->(OrdSetFocus(nInd))
      (oDCli)->(DbGoTo(nRec))
      oCve:Refresh()
   ELSE
      lRet:=.F.
      MsgBeep()
   ENDIF
RETURN (lRet)



 

Now I wish change the Numcli from strzero(Recno()+1) to 13 Cr

type : 0001201908001

to use it and create also a barcode





Now I understood I cannot make it with tdatabase( or tdata) and I have problem if i cannot use numcli

[James Bott]

Now I understood I cannot make it with tdatabase( or tdata) and I have problem if i cannot use numcli

What exactly is numcli? Is it the customer ID? Why can't you use it now?

Now I wish change the Numcli from strzero(Recno()+1) to 13 Cr

I don't quite understand what you are doing above.

I would not use Recno() for anything but a temporary place holder--never for any kind of ID if that is what you are doing. Recno()'s can change so you can't use them as an ID. ID's must never change. Again refer to the Relational Database Design article (link in my previous message) for more info about primary keys.

Ok, it just came to me that you are saying when using database objects you can't get an ID before you create the record? What does this have to do with searching for quests? I am confused.

Oh, I never said you can't create an ID before creating a record, I just said you shouldn't and that the database objects don't handle that automatically because nobody does it. It can be done using datbase objects, it will just require more work and dealing with a lot of deleted records.

[TimStone]

I actually use 3 fields: First, last, and company names.

If the customer is not affiliated with a company, then I put their name, ( Last + First, NOT trimmed ... leave the spacing in place ) into the company field. I use the index on the Company field for searches and ordering in the xbrowse. It NEVER fails.

[Silvio.Falconi]

Tim,
I cannot believe you not use a code number for a customer ...on invoice you use only First name ?

and then on Beach application I have a special feature

I must search a guest /customer

if is a guest and is on a umbrella or Palm or tent

each customer can save until 10 people or family members

If I search " Tim Stone" the procedure must be able to show me which umbrella is the guest or the customer..

imagine having 2500 umbrellas for beach and many establishments have at least 3 beaches (zones)

[Silvio.Falconi]

James,Tim

On my customer procedure I use numcli as strzero(oDbf:Recno,4) and

the end user is used to seeing NumCli in the dialog both in the modification and in the insertion ( a get with READONLY)

As you can see in this picture



now I created another dbf where I can save the guest od each customer ( with 4 telephone number )

orange arrow -> customer dialog
green arrow-> Guest Dialog

[TimStone]

Silvio,

Each customer does have an account number. I store a lot more information on them also. My xBrowse actually allows me to look them up by account number, company ( or Last-First name ), phone number, or even email. By clicking on the header, I select the field I want to search in, it resets the list in that order and I start typing in the info.

Thus, to do a search I popup the browse, click on the company field ( remember, last name + firstname or the actual name of a company ), and start typing the first few letters. The browse moves to that position instantly.

For efficiency, if I was linking people to umbrellas, I would use their account number, but I don't have to even show that link because people don't know the numbers. They know their name.

For what you want to do, each umbrella would have an assigned ID. A record would be created for a date, that umbrella, and have the renter's account number in one field, and the occupants account numbers in other fields set aside for them. I'd link the files, and the display could show the names.

Of course here we only track who rents something, not who might sit under it.

[Silvio.Falconi]

>Each customer does have an account number.

Well, how to create this account number?
I've always done a 4-digit number by taking it from the record number.
The end user wants to see the account number in the insert / edit dialog.
According to James and others with tdatabase (or tdata) this is not possible because on the net it could change if someone else enters the same account number because it is taken from the record number