Marking records

[alanjay]

I hope I describe this correctly. I have tables called File, Client and Accident. Each has a a unique ID Field. One file has one client and one accident only (though one client can have many Files or one Accident many Files). In the File Table is a field for the client ID and another for the accident. This was originally part of a database set up in 2003 using version 5.

I am now updating to version 10.5. In the last seven years about 4000 new records have been set up in the File table, and close to that in the client and accident. There were about 5000 records already in the tables from a previous database program (the much loved Q&A). Now the problem. The person(s) in my office who sets up new cases have had a tendency in the last few years to begin the process, create the Client record, go to lunch or something like that, and then come back and end up recreating the Client Record, and finish the process. There could be five client records, each with their own ID, and one File with one of those IDs. In the search client dialog box, you would see one client multiple times, and have no idea but by trial and error to figure out which one was associated to a File.

Now that I have spent my time (and while, as clear as this is, I am not a programmer, I love this stuff) redesigning the application I want to go back and delete the extraneous client records -leaving only those who have a File record associated with it. How do I design this operation? It must mark Client records that are not associated with a File records. The key is the Client ID.

This is not going to make or break the fact that after this weekend we will be using version 10.5 in our office. It is perplexing me -any thoughts or suggestions would be appreciated.

[MikeC]

Re: Marking records

The Delete operation sounds so straight-forward that I must be missing something!

If the field for file records has no data you want to delete them it sounds like....then the condition you tell the genie to use could be Your_File_Record_Field<>"" or maybe even isnotblank("Your_File_Record_Field") if this is a character field....for numeric, date, whatever there are similar filters...but you didn't tell us what it is so I guessed character! :)

I also assumed you did want to use a delete operation and not just a mark operation...the same filter would be used for either so is actually a moot point.

[alanjay]

Re: Marking records

I am not being clear, and I apologize. The "Client Table" has a unique ID ("CL0001" that is automatically incremented for new record. The "File Table" has two fields - a unique ID ("F00001") and a Client ID from the Client Table. It is a one to many relationship -one Client can have many files, one File can have only one Client.

Theoretically every Client record should be connected to at least one File record.But in practice there are many Client records without an associated File Record. My question is how to find those Client Records. It is sort of asking the program to discover a negative -i.e. look for Client records not associated with any particular File record. There is no empty fields per se.

[Mike Wilson]

Re: Marking records

To get a list of the client records without files:

dim vLfiles as C					' list of file records unique for each client
vLfiles=table.external_record_content_get("filetable","client_id","client_id","unique_key_values()")

dim vLclients as C				'list of all client records
vLclients=table.external_record_content_get("clienttable","client_id","client_id","")

dim vLempty_clients as C
vLempty_clients=word_subtract(vLclients,vLfiles,crlf())	 'subtract the file list from client list leaving clients without files

To mark client records without files:

dim vcid as C
dim t as p
t=table.open("clients")
t.fetch_first()
while .not. t.fetch_eof()
	vcid=t.client_id
	if a5_get_records_in_query("filetable","client_id="+quote(vcid))=0
		t.change_begin()
		t.mark()
		t.change_end(.t.)
	end if
t.fetch_next()
end while
t.close()

[MikeC]

Re: Marking records

An alternative to what Mike said would be to use Substract operations....same thing basically, just doing it on a table basis instead of a list.....I have used both depending probably just on my mood at the time as I do not think much difference in either way. :)

[alanjay]

Re: Marking records

Your message came just as I finished doing it that way. Because I am not very good with code at first i was still lost. But the code is very logical -I could understand what Mike was advising me to do -I just could not execute it. What I like about the program is that there is likely some way to do many, many things with the build in genies and action scripting etc. I stumbled across the subtract operation and accomplished what I needed to do.

I have not often used this message board but very much appreciate that when I have the responses have always been helpful. The great thing is that in learning the answer to one specific problem, I learn a more about the software itself.