check for existing records

[richard2007]

when a user is adding a new record, how can i based upon the value of the first 3 fields entered, check if there is an existing record.

if so - go to that record, if not allow the user to continue adding a new record.

been on this for a few hours now and dont have the skills to code it.

[Scott Emerick]

Re: check for existing records

Richard,

A easy answer would be to create a index(MyIndex) based on the 3 fields and then use Exist() to check the table. I would recommend alltrim() every field in the index. Then you would use something like:

vkey = alltrim([I]field1.value[/I])+alltrim([I]field2.value[/I])alltrim([I]field3.value[/I])
IF exist(vkey,"[I]MyTable[/I]","[COLOR="Red"]Myindex[/COLOR]") THEN
'Get Existing record
ELSE
'Create new record
END IF

There are other methods that don't rely on indexes, but this is the simple example.

Hope this helps.

Scott

[richard2007]

Re: check for existing records

thanks scott,

this is great, what id like to do is enhance it slightly, if there is a record, go to form 'a' filtered for the record (there will only be one record which can match all 3 fields) if there is no record go to form 'b' (in add new record mode.)

i had started on some code to search some unbound fields and my plan was to then do the above, but i was unsure how i was going to present this to the user. ( i started with just 2 filters)

dim t as p
dim nrecs as N 
dim i as p

if txtschsurname <> "" .AND. txtschfirstname <> "" then
    

t = table.current()

filter1 = "case_parent->clientsurname = " + quote(txtschsurname.text)
filter1 = alltrim(filter1)
filter2 = "case_parent->CLIENTFirstNAME = " + quote(txtschfirsrname.text)
filter2 = alltrim(filter2)
filterclient ="("+filter1 +") .and. ("+filter2 +")"

query.filter = filterclient

query.order = "" 
i = t.query_create()


 nrecs = i.records_get()
   crec=current_record()
   if nrecs = 0
     i.drop()
     ui_msg_box("No client found, proceed to add new case")
  'code to go to form to add new record
else
  ' code to go to form for selected record
   end
	end if

im not even sure i was on the right track and your method seems easier. and advice on how best to achieve openeing a form filtered on the unbound text boxes?

[Scott Emerick]

Re: check for existing records

Richard,

What you want to do is:

Do a lookup based on the same filter(key) to get the record number or unique identifier

Then open the form showing that record

Scott

[Keith Hubert]

Re: check for existing records

Hi Richard,

Here is the script I used in the CanSave form event. It was used to check if the same job had not been entered one than once for the same customer with the same dates. As you can see it is quite old but it still works. It will only show the dialog with data if there are duplicates.

'Date Created: 21-Feb-2003 07:18:16 PM
'Last Updated: 04-Mar-2003 01:52:56 PM
'Created By : Peter Wayne
'Updated By : Keith Hubert
''XBasic
dim tbl as p
tbl=table.current()
if tbl.mode_get() 0 then
show_recs = iif(recs < 11,recs,10) 'only show 10 duplicates
x = ""
crlf = chr(13)+chr(10)
for cnt = 1 to show_recs
x = x+alltrim(str(cnt))+". "+"Record "+alltrim(str(t.recno()))+": "+alltrim(t.idcode)+", "+dtoc(t.date1)+" - "+alltrim(t.Site_Name)+" "+dtoc(t.date2)+crlf
t.fetch_next()
next cnt
end if
t.index_primary_put(idx)
ix.drop()
t.close()
if recs > 0 then
ans= ui_msg_box("Warning! "+alltrim(str(recs))+" duplicates found",\
x + crlf + "Allow record ?",\
UI_ATTENTION_SYMBOL + UI_YES_NO + UI_SECOND_BUTTON_DEFAULT)
if ans = UI_NO_SELECTED then
cancel()
end if
end if
end

[richard2007]

Re: check for existing records

im working on scott's idea for now.

i have this code so far, the code runs but always open a form to accept a new record. so i have something wrong when trying to find an exisitng record. (i am testing a search on a record i know exists, so the first part of the code should run).

to be honest, i am bumbling my way through this.

vkey = alltrim(clientfirstname.value)+alltrim(clientsurname.value)
IF exist(vkey,"case_parent","case_parent_id") THEN
'Get Existing record
'Get pointer to existing window. In case layout_name is qualified with a dictionary name, extract up to first @. In case formname has spaces, normalize it
tempP=obj(":"+object_name_normalize(word(layout_name,1,"@")))
'Test if pointer is valid
if is_object(tempP) then 
	'Test if pointer refers to a form or browse
	if tempP.class() = "form" .or. tempP.class() = "browse" then 
				dim flagIsBaseFilter as l 
		flagIsBaseQuery = .t.

		
		if flagIsBaseFilter then 
			'Apply the query to the form or browse as the Base query. (this means that 
			'the user cannot turn the query off, only add to the query)
			tempP.BaseQueryRun(query.filter,query.order)
		else
			tempP.QueryRun(query.filter,query.order)		
			
		end if 
		
		'then activate the already open window
		tempP.activate()
	else
		'Window is not already open, so open it
		varP_parent_case = :Form.viewqueried(layout_name,query.filter, query.order )

	end if
else 
	varP_parent_case = :Form.viewqueried(layout_name,query.filter, query.order )

end if

ELSE
'Create new record

'Open a Form or Browse layout, displaying all, or selected records in the layout.

DIM Shared varP_fCaseCreate2 as P
DIM layout_name as c 
layout_name = "fCaseCreate2"
DIM tempP as p
'Get pointer to existing window. In case layout_name is qualified with a dictionary name, extract up to first @. In case formname has spaces, normalize it
tempP=obj(":"+object_Name_normalize(word(layout_name,1,"@")))
'Test if pointer is valid
if is_object(tempP) then 
	'Test if pointer refers to a form or browse
	if tempP.class() = "form" .or. tempP.class() = "browse" then 
		'If so, then activate the already open window
		tempP.activate()
		tempP.new_record()
	else
		'Window is not already open, so open it
		varP_fCaseCreate2 = :Form.view(layout_name)
		varP_fCaseCreate2.new_record()

	end if
else 
	varP_fCaseCreate2 = :Form.view(layout_name)
	varP_fCaseCreate2.new_record()

end if 

END IF

[Scott Emerick]

Re: check for existing records

Richard,

Your missing the step to find the existing record. You need something like:

'This finds the case_parent_id of the existing record
vfindrec = lookupc("F",vkey,"case_parent_id","case_parent","case_parent_id")

Now that you have the record you want stored in Vfindrec you then open the form filtered to this case_parent_id(vfindrec).

I hope that makes sense.

Scott

[Tim Kiebert]

Re: check for existing records

Richard,

A easy answer would be to create a index(MyIndex) based on the 3 fields and then use Exist() to check the table. I would recommend alltrim() every field in the index. ........

Scott

I would be careful about using alltrim in an index.

Consider these values in an existing record. The field size is 10 for each

Field1: "abc       "
Field2: "klm       "
Field3: "xyz       "

Combing these into the index using alltrim yields "abcklmxyz"

If you now enter a new record with the following

Field1: "abckl     "
Field2: "          "
Field3: "mxyz       "

and apply alltrim for the test you will get the same key and an incorrect result that calims this record already exists.

[Scott Emerick]

Re: check for existing records

Tim,

You make a good point, however, I have seen more failures not using alltrim() then using it .. but we all have our preferences.

Something to keep in mind though as it is good advice.

Scott

[Tim Kiebert]

Re: check for existing records

Scott,

Could you give an example of such a failure? Not that I don't believe you. Just interested in knowing.

I guess one situation might be if the field sizes were large then the key length would exceed the maximum
I think what it comes down to is that the index format and the search key format have to be the same. One could use alltrim and introduce a seperator that you know would not be in the data. That could even be a single space since after using alltrim() there should not be a space at the extremities of the data.

[richard2007]

Re: check for existing records

this is a bit above my skill level, but im slowly getting there.

i am trying to define my index which is 2 character fields and a date field. i am using this to format the date field to a characher but am getting an error message saying not a logical xpression.

trim(Clientfirstname)+trim(Clientsurname)+dtoc(Clientdob,"2/")

[richard2007]

Re: check for existing records

seem to have my index right with this, alhough the date doesnt seem to accept 'dtoc()' to format it to character.

Clientfirstname=alltrim(case_parent->clientfirstname).AND.Clientsurname=alltrim(case_parent->clientsurname).AND.Clientdob=(case_parent->clientdob)

[richard2007]

Re: check for existing records

based upon my index below, i have entered this code on the forms 'cansave' event.

vkey = alltrim(clientfirstname.value)+alltrim(clientsurname.value)+dtoc(clientdob.value)
IF exist(vkey,"case_parent","parent_id") THEN
ui_msg_box("Client Search","record found please add the new case to the clients master file")
ELSE
ui_msg_box("Client Search","no record found please add a new case")
END IF

then i have put a button on the form to save the record after the 3 fields have been filled in.

i am always getting the second message, even though i know there is a dulicate record.

i have tried all sorts today, keiths code below, other code samples from the msg board, dr waynes examples but i just cant get this to work.

[richard2007]

Re: check for existing records

thanks for all your help. i have solved my problem, took a different route the result is the same. (big thanks to stan).

for those who might need the same.

i made a form with 2 variable fields on it. added an embedded browse based upon my table 'case_parent' to display the results of the search. hid the browse initially.

then i added this code to a button, which searches for existing records which meet the 2 variable fields (will add a third to make the search for duplicate more robust).

if there is a record which meets the search, the browse and another button become visible.

if not the user is taken to a form to add the new record.

i take the values of the 2 variable fields and enter those in to the new record for the user.

i know this isnt the way it should be done and if anyone see's any major flaws, id be happy to hear, but its working.

'Date Created: 11-Jan-2009 08:55:27 PM
'Last Updated: 11-Jan-2009 08:55:27 PM
'Created By  : R Harris
'Updated By  : R Harris
dim tbl as P
dim qry as P
dim nrecs as N


tbl = table.current()

filter1 = "case_parent->clientsurname = " + quote(txtschsurname.text)
filter1 = alltrim(filter1)
filter2 = "case_parent->CLIENTFirstNAME = " + quote(txtschfirsname.text)
filter2 = alltrim(filter2)
filterclient ="("+filter1 +") .and. ("+filter2 +")"

query.description = "case_parent"
query.order = "clientsurname"
query.filter = filterclient
query.options = ""

qry = tbl.query_create()

nrecs = qry.records_get()

IF (nrecs = 0) then
    qry.drop()
    query.order = ""
    query.filter = ""

ui_msg_box("No Records","No records exist for this client You can now add a new client file." ,UI_INFORMATION_SYMBOL)
    
'Open a Form or Browse layout, displaying all, or selected records in the layout.

DIM Shared varP_fcreateparent as P
DIM layout_name as c 
layout_name = "fcreateparent"
DIM tempP as p
'Get pointer to existing window. In case layout_name is qualified with a dictionary name, extract up to first @. In case formname has spaces, normalize it
tempP=obj(":"+object_Name_normalize(word(layout_name,1,"@")))
'Test if pointer is valid
if is_object(tempP) then 
	'Test if pointer refers to a form or browse
	if tempP.class() = "form" .or. tempP.class() = "browse" then 
		'If so, then activate the already open window
		tempP.activate()
		tempP.new_record()
	else
		'Window is not already open, so open it
		varP_fcreateparent = :Form.view(layout_name)
		varP_fcreateparent.new_record()

	end if
else 
	varP_fcreateparent = :Form.view(layout_name)
	varP_fcreateparent.new_record()

end if 

else
	cancel()

    ui_msg_box("Client Exists","This client already exists in Mortgage Manager, please add a new case file to the client file." ,UI_INFORMATION_SYMBOL)
    	'show browse and cmd button if records exist
topparent:B_contact.show()
topparent:Cmd_viewparent.show()

    END

end if

hope this helps someone else as ive been at it for hours.

[Tom Cone Jr]

Re: check for existing records

No thread with this "topic" should ever end without someone mentioning the importance of the KEY_UNIQUE() function. In this case Richard it may offer a much simpler solution for you.