topic-40528

Posts: 7318

Joined: Thu Oct 18, 2012 07:17 PM

check existing first and last on customer - RESOLVED !!!
Posted: Mon Jun 14, 2021 10:02 AM

when I add a record I encounter difficulties because the customer archive is not indexed on first and last
I must make a check if the first and last exist
and on the network I cannot make an index on the fly
so if I check the First or the Last it tells me that it is already existing

the problem is that I can have customers who have the same First and also the same Last so how can I check

how can i do this check and see that the name entered is already in the archive?

I state I'm using tdatabase and I use customer dbf

oDbf:SetOrder( 1 ) //FIRST
oDbf:GoTop()

if oDbf:Seek( UPPER( cFirst) )

this give me the user is allready on archive

if I make oDbf:Seek( UPPER( cFirst+cLast) ) I not have the index

I made a test making an index on first+last
then i made

local cNomeIntero :=alltrim(cFirst) +alltrim(cLast)

oDbf:SetOrder( 16 ) //first+last
oDbf:GoTop()
oDbf:Seek( UPPER( cNomeIntero ) )
xbrowser oDbf

not good it go to eof of dbf and give me not return if found the record

any solution ?

Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)

I use : FiveWin for Harbour March-April 2024 - Harbour 3.2.0dev (harbour_bcc770_32_20240309) - Bcc7.70 - xMate ver. 1.15.3 - PellesC - mail: silvio[dot]falconi[at]gmail[dot]com

James Bott

Posts: 4840

Joined: Fri Nov 18, 2005 04:52 PM

Re: check existing first and last on customer
Posted: Mon Jun 14, 2021 03:43 PM

Silvio,

>how can i do this check and see that the name entered is already in the archive?

This is a classic problem when you are not using unique primary-keys. Primary-keys are one of the basic principles of relational-database programming. Another principle is to store each piece of data, only once.

In the real-world, you are going find duplicate names--people, cities, items, etc. And you MUST be able to have more than one customer, city, or item with the same name(s).

A unique numeric primary-key is the way to avoid these kinds of problems. It also prevents you from having duplicate data in different files. For instance, all you need in a reservation database is the person's primary-key. This way the address (for instance) is only stored in the customer file, yet you can print the address on a reservation invoice.

This also prevents the problem that arises when the customer moves or changes their phone number. If you have this data in multiple files, then you have to update it in multiple files. Also you are using more disk space.

OK, back to your existing problem. The problem is that your first and last name files are different lengths in the customer and reservation files (15 in one file and 40 in the other). You are going to either need to change the field lengths to be the same, or make them the same in the search.

Or, better, switch to using a primary keys. I know this will be a pain now, but it will prevent a lot of future issues. And it will speed up your program since it uses less disk space, so there is less disk access. Note that adding a primary key to the database files is unlikely to break any existing code and it allows you to solve existing problems.

See also: Relational Database Concepts

James

FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10

Silvio.Falconi

Posts: 7318

Joined: Thu Oct 18, 2012 07:17 PM

Re: check existing first and last on customer
Posted: Mon Jun 14, 2021 04:23 PM

I have the primary keys, the customer database is indexed on name, surname, city, province and region.

the primary key that was there before, ie "0001" four characters, I have hurt to remove it due to the countless problems both with tdatabase and with tdata.

Yes, I know you and nages gave me many examples last year but then on the net all this becomes very difficult.

Before I had found an expedient that is to save the surname and name (full name field) in a single field and do the index and check on this field but I wanted to know if there was an alternative.

In the program I often have families that have the same surname and often also the name, for example my name is like my grandfather.

Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)

I use : FiveWin for Harbour March-April 2024 - Harbour 3.2.0dev (harbour_bcc770_32_20240309) - Bcc7.70 - xMate ver. 1.15.3 - PellesC - mail: silvio[dot]falconi[at]gmail[dot]com

James Bott

Posts: 4840

Joined: Fri Nov 18, 2005 04:52 PM

Re: check existing first and last on customer
Posted: Mon Jun 14, 2021 05:01 PM

Silvio,

I have the primary keys, the customer database is indexed on name, surname, city, province and region.

That is not guaranteed to be unique, and it takes up way to much space on the disk. You need to use an ID number.

the primary key that was there before, ie "0001" four characters, I have hurt to remove it due to the countless problems both with tdatabase and with tdata.

What countless problems? I have never had a single problem using a sequential primary key.

Yes, I know you and nages gave me many examples last year but then on the net all this becomes very difficult.

Why? I think this may just be because you have never used a primary key. The one problem I do remember is that a customer wants the ID displayed on a new record input screen. This is technically not possible, because you will end up with a blank record every time a user cancels a new record entry.

Is that the issue you are referring to? And by the way, how are they going to respond to not even having a reservation number (the way you are doing it now)?

It just came to me. The way to use the primary key and have it displayed on the data entry screen for a new reservation. The data entry screen has three buttons, OK, Apply, and Cancel. You just leave the primary key blank until the user is done, then they press the Apply button, and the record gets an ID and gets saved, and the blank field gets filled in on the screen. The screen remains open so they can copy the ID (or whatever). Then they press the OK button to exit. If they press the Cancel button, no blank record (with just an ID) gets added. Simple.

Before I had found an expedient that is to save the surname and name (full name field) in a single field and do the index and check on this field but I wanted to know if there was an alternative.

Yes, the alternative is a numeric, sequential primary key. This can be stored as a number or a character.

In the program I often have families that have the same surname and often also the name, for example my name is like my grandfather.

Yes, that is the classic reason for using numeric primary keys instead.

FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10

James Bott

Posts: 4840

Joined: Fri Nov 18, 2005 04:52 PM

Re: check existing first and last on customer
Posted: Mon Jun 14, 2021 06:20 PM

the customer database is indexed on name, surname, city, province and region

Note that this must take 30-40 characters in every file that needs to be linked to the customer file. That is a lot of wasted disk space, and slows down the program. Plus the index would be huge.

A 4-8 character primary-key uses much less disk space and speeds up disk reads and writes and thus the program is faster for the user.

James

FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: check existing first and last on customer
Posted: Mon Jun 14, 2021 07:57 PM

Mr. Silvio

Without any special index:

Code (fw): Select all Collapse

   local oDbf
   local cName    := "David"
   local cLast    := "Jochum"

   oDbf  := TDataBase():Open( nil, "CUSTOMER", "DBFCDX", .t. )
   if oDbf:LookUp( UPPER( PADR( cName, 20 ) + PADR( cLast, 20 ) ), ;
                   "UPPER(FIRST+LAST)", { || FOUND() } )

      ? "Found"
   else
      ? "Not Found"
   endif

   oDbf:Close()

Note: 20 and 20 in PADR() function are the field sizes of FIRST and LAST.

Regards

G. N. Rao.

Hyderabad, India

Silvio.Falconi

Posts: 7318

Joined: Thu Oct 18, 2012 07:17 PM

Re: check existing first and last on customer
Posted: Mon Jun 14, 2021 09:34 PM

nageswaragunupudi wrote:Mr. Silvio

Without any special index:

Code (fw): Select all Collapse

   local oDbf
   local cName    := "David"
   local cLast    := "Jochum"

   oDbf  := TDataBase():Open( nil, "CUSTOMER", "DBFCDX", .t. )
   if oDbf:LookUp( UPPER( PADR( cName, 20 ) + PADR( cLast, 20 ) ), ;
                   "UPPER(FIRST+LAST)", { || FOUND() } )

      ? "Found"
   else
      ? "Not Found"
   endif

   oDbf:Close()

Note: 20 and 20 in PADR() function are the field sizes of FIRST and LAST.

Sorry Nages I have a bit of everything. your idea can only work that I have to manage the various insertion modes
nMode = 1 new
nMode = 2 change
nMode = 3 duplicate records
nMode = 4 I need this in the selection dialog

I made

Code (fw): Select all Collapse

@ 12, 10 SAY "Cognome:" OF oDlg SIZE 31, 8 PIXEL    FONT oFont
   @ 10, 67 GET aGet[1] VAR oCliente:Clicognome OF oDlg SIZE 100, 12 PIXEL  FONT oFont

   @ 26, 10 SAY "Nome :" OF oDlg SIZE 22, 8 PIXEL FONT oFont
   @ 24, 67 GET aGet[2] VAR oCliente:Clinome    OF oDlg SIZE 100, 12 PIXEL FONT oFont ;
                     VALID   SearchCli( oCliente:Clicognome, aGet[2], 1,oDbf,oCliente:Clinome )

Code (fw): Select all Collapse

 
function SearchCli ( cCognome, oGet, nMode,oDbf,cNome )
   local lreturn  := .f.
   local nRecno   := oDbf:RecNo()
   local nOrder   := oDbf:OrdNumber()
   local nArea    := Select()


 if Empty( cNome )
      if nMode == 4
         return .t.
      else
         MsgStop("E' obbligatorio questo campo.")
         return .f.
      endif
   endif

oDbf:SetOrder( 1 )  //CLICOGNOME
   oDbf:GoTop()

 IF  oDbf:LookUp( UPPER( PADR( cCognome, 20 ) + PADR( cNome, 20 ) ), ;
                   "UPPER(CLICOGNOME+CLINOME)", { || FOUND() } )

 DO CASE
         Case nMode == 1 .OR. nMode == 3   // new or duplicate
            lreturn := .f.
            MsgStop("Nominativo esistente.")
         Case nMode == 2   // modify
            if oDbf:Recno()  == nRecno
               lreturn := .t.
            else
               lreturn := .f.
               MsgStop("Nominativo esistente.")
            endif
         Case nMode == 4     //selection dialog 
            lreturn := .t.
         END CASE

 ELSE

      if nMode < 4
         lreturn := .t.
      else
         if MsgYesNo("Nominativo inesistente. ¿ Desideri inserirlo ora? ")   // on selection dialog ask to add the new record
            lreturn := ClEdita( , 1, , , @cCognome )
         else
            lreturn := .f.
         endif
      endif
   endif

 if lreturn == .f.
      oGet:cText( space(20) )
   endif

  oDbf:SetOrder( nOrder )
  oDbf:GoTo( nRecno )

   Select (nArea)

   return lreturn

First I insert "Vitali" "Pietro" and the procedure returns me that it found it in the archive,
deletes the contents of the get (aGet [2]) and points the cursor on the get of the name field. all ok

Then I insert "silvio" instead of "Pietro" and the procedure makes an error in line 777 which appears to be

this is the error

Code (fw): Select all Collapse

Application
===========
   Path and name: C:\Work\Prg\Prenotazioni\WinBeach.Exe (32 bits)
   Size: 7,603,200 bytes
   Compiler version: Harbour 3.2.0dev (r1904111533)
   FiveWin  version: FWH 21.04
   C compiler version: Borland/Embarcadero C++ 7.0 (32-bit)
   Windows version: 6.1, Build 7601 Service Pack 1

   Time from start: 0 hours 0 mins 29 secs 
   Error occurred at: 14-06-2021, 23:29:13
   Error description: Error BASE/1066  Parametro errato: condizionale
   Args:
     [   1] = U   

Stack Calls
===========
   Called from: source\Clienti\PClienti.prg => CLCLAVE( 777 )
   Called from: source\Clienti\PClienti.prg => (b)CLEDITA( 597 )
   Called from: .\source\classes\TGET.PRG => TGET:LVALID( 2282 )
   Called from: .\source\classes\CONTROL.PRG => TGET:FWLOSTFOCUS( 1205 )
   Called from: .\source\classes\CONTROL.PRG => TCONTROL:HANDLEEVENT( 1802 )
   Called from: .\source\classes\TGET.PRG => TGET:HANDLEEVENT( 1279 )
   Called from: .\source\classes\WINDOW.PRG => _FWH( 3560 )
   Called from:  => DIALOGBOXINDIRECT( 0 )

Also If I make

IF oDbf:LookUp( UPPER( PADR( cCognome, 20 ) + PADR( cNome, 20 ) ), ;
"UPPER(CLICOGNOME+CLINOME)", { || FOUND() } )

lreturn := .f.
else
lreturn := .t.
endif

give me the same error on the second time when I insert a Last new

Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)

I use : FiveWin for Harbour March-April 2024 - Harbour 3.2.0dev (harbour_bcc770_32_20240309) - Bcc7.70 - xMate ver. 1.15.3 - PellesC - mail: silvio[dot]falconi[at]gmail[dot]com

James Bott

Posts: 4840

Joined: Fri Nov 18, 2005 04:52 PM

Re: check existing first and last on customer
Posted: Mon Jun 14, 2021 10:09 PM

The field sizes for LAST and FIRST in the sample database you sent me are both 18 characters not 20. You had better check yours.

Your GETs should be using the current field size, so you shouldn't need to pad them unless you are looking them up in a different file, then you need to use the field size of that file (or make the fields in that file the same length). In fact field sizes should always be made the same in each file that is using the same name fields.

These are all problems that unique primary key IDs would solve.

James

FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10

Silvio.Falconi

Posts: 7318

Joined: Thu Oct 18, 2012 07:17 PM

Re: check existing first and last on customer
Posted: Mon Jun 14, 2021 10:18 PM

James Bott wrote:The field sizes for LAST and FIRST in the sample database you sent me are both 18 characters not 20. You had better check yours.

Your GETs should be using the current field size, so you shouldn't need to pad them unless you are looking them up in a different file, then you need to use the field size of that file (or make the fields in that file the same length). In fact field sizes should always be made the same in each file that is using the same name fields.

These are all problems that unique primary key IDs would solve.

James

nages solution works but only as far as if it finds a customer if it doesn't find it it makes mistake so i think the line

IF oDbf:LookUp( UPPER( PADR( cCognome, 20 ) + PADR( cNome, 20 ) ), ;
"UPPER(CLICOGNOME+CLINOME)", { || FOUND() } )

has a setting error I think can be solved
not knowing that function I cannot adjust the settings

Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)

I use : FiveWin for Harbour March-April 2024 - Harbour 3.2.0dev (harbour_bcc770_32_20240309) - Bcc7.70 - xMate ver. 1.15.3 - PellesC - mail: silvio[dot]falconi[at]gmail[dot]com

James Bott

Posts: 4840

Joined: Fri Nov 18, 2005 04:52 PM

Re: check existing first and last on customer
Posted: Mon Jun 14, 2021 11:14 PM

Silvio,

Hmm, I see that my version of TDatabase does not have a Lookup() method. And Nages didn't provide the Found() function. So you are dead in the water until you have those. Does your version of TDatabase have the Lookup() method?

Perhaps you can just use the Seek() method?

James

FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: check existing first and last on customer
Posted: Tue Jun 15, 2021 01:22 AM

1) The function FOUND() is a native Clipper/Harbour RDD function and has been there from the time Clipper was born.
2) Method LookUp(...) was first introduced in Sep 2017 version and exists in FWH1805.

Regards

G. N. Rao.

Hyderabad, India

Silvio.Falconi

Posts: 7318

Joined: Thu Oct 18, 2012 07:17 PM

Re: check existing first and last on customer
Posted: Tue Jun 15, 2021 09:44 AM

James Bott wrote:Silvio,

Hmm, I see that my version of TDatabase does not have a Lookup() method. And Nages didn't provide the Found() function. So you are dead in the water until you have those. Does your version of TDatabase have the Lookup() method?

Perhaps you can just use the Seek() method?

James

dead ?

I have the last Release of Fwh

Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)

I use : FiveWin for Harbour March-April 2024 - Harbour 3.2.0dev (harbour_bcc770_32_20240309) - Bcc7.70 - xMate ver. 1.15.3 - PellesC - mail: silvio[dot]falconi[at]gmail[dot]com

Silvio.Falconi

Posts: 7318

Joined: Thu Oct 18, 2012 07:17 PM

Re: check existing first and last on customer
Posted: Tue Jun 15, 2021 09:46 AM

nageswaragunupudi wrote:1) The function FOUND() is a native Clipper/Harbour RDD function and has been there from the time Clipper was born.
2) Method LookUp(...) was first introduced in Sep 2017 version and exists in FWH1805.

Nages do you saw my problem?

Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)

I use : FiveWin for Harbour March-April 2024 - Harbour 3.2.0dev (harbour_bcc770_32_20240309) - Bcc7.70 - xMate ver. 1.15.3 - PellesC - mail: silvio[dot]falconi[at]gmail[dot]com

James Bott

Posts: 4840

Joined: Fri Nov 18, 2005 04:52 PM

Re: check existing first and last on customer
Posted: Tue Jun 15, 2021 03:07 PM

Silvio,

Code (fw): Select all Collapse

local cNomeIntero :=alltrim(cFirst) +alltrim(cLast)

oDbf:SetOrder( 16 ) //first+last
oDbf:GoTop()
oDbf:Seek( UPPER( cNomeIntero ) )
xbrowser oDbf

not good it go to eof of dbf and give me not return if found the record

cNomeIntero is in all uppercase and if your index is not, then there won't be a match. You need to show exactly how the index is made. It would have to be:

index on UPPER(alltrim(cFirst) + alltrim(cLast))...

If it is not, then that is why the search is failing.

Also, why do you even need to find if a customer's name already exists? If you use a CUSTNO then this doesn't matter--you can have ten customers with the same first and last names--they will each have a different CUSTNO. You just link the reservation by adding the CUSTNO to the reservation. And with this you can find the right customer information in the customer file from the reservation CUSTNO. Then you add methods to the reservation class to lookup the customer info in the customer database when needed--for instance when printing the reservation. Simple.

Of course, the program user is still going to have to deal with finding the right customer out of the multiple customer's with the same name. In this case, the program user would need to ask the customer for more information like address or phone number to find the right customer record, then they add a reservation for that customer and the CUSTNO gets added automatically to the reservation database.

CUSTOMERS.DBF FIELDS:
CUSTNO (primary key, unique)
FIRSTNAME
LASTNAME

RESERVS.DBF FIELDS: (You could have multiple reservations for the same CUSTNO)
RESERVNO (primary key, unique)
CUSTNO

RESERVITEMS.DBF FIELDS: (Multiple items per RESERVNO)
RESERVNO (primary key, unique)
ITEMNNO

Did you read the document about relational databases that I provided a link to in another response to one of your questions? You really need to if you haven't. Using relational databases will solve many of your problems.

James

FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10

James Bott

Posts: 4840

Joined: Fri Nov 18, 2005 04:52 PM

Re: check existing first and last on customer
Posted: Tue Jun 15, 2021 03:21 PM

Nages,

1) The function FOUND() is a native Clipper/Harbour RDD function and has been there from the time Clipper was born.
2) Method LookUp(...) was first introduced in Sep 2017 version and exists in FWH1805

Thanks for clearing that up. I don't remember using FOUND() with Clipper even though I was using Clipper when it was first released (and DBase before that). But that was a very long time ago...

I didn't find the Lookup() method in TDatabase because I was looking a the sorted methods in my editor and the actual method name is TD_LOOKUP, so it was under the T's not the L's.

However, I am sure using primary-keys will fix all these problems Silvio is having. He won't even need to do this lookup.

James

FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10

FiveTech Support Forums