Information disapearing

I have a database where the main table where all the information gets added in.

There are alot of fields which will open a selection box where you could select a technician.  There is another tabel where we can add new technicians.  But when a technician is deleted (left the company) the information gets stripped out from the main database too.

Is there a way to stop this from happening?  I have about 15 other fields that have the same setup.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sounds like you may have the option checked to delete related records when the original record is deleted. Take a look at the definition of the relationship between the tables. There's a checkbox in  the relationship dialog that says "Delete related records in this table when a record is deleted in the other table." This is sometimes call cascading deletion.

I'd also point out that, in general, you don't want to actually destroy information like this. The preferred way to handle this would be to simply use a status flag to show them as no longer employed, rather than actually removing the record. By deleting the record, you're actually making the related records orphans, with no way to be certain of their validity. This is especially important in cases where accountability/liability is an issue, such as legal, medical and financial systems, but it's good practice for any database.

Bill, if that would be the case, every time he deletes a technician, it would delete several records in the main database, and he says that only the technician info is deleted from the main table if I got it right (information stripped out does not mean to delete a complete record to me)
If I am right, it means the technician field in the main table is not properly defined and is dynamically linked to the technician table somehow...
CruizectrlAuthor Commented:
Thats right.  The technicain information is removed from every main record he appears in.  So what you say is correct.  It just creates holes in information.

I do like Bills answer and I was thinking about that as well.  The details for the technicians record should be kept, regardless if hes active or not.  So I'm thinking on every situation like this, I create a active/inactive button and add in a script to the buttons to find all actives when the subforms are called.
This sound like a solution?
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

if the main table realy contain a field for "technician", what is its definition is not pure text? is it a looked up value? or is the table linked via a technician ID, and the layout using something like technicians::name, ie a field from the sub table instead of maintable::technician_name?
what is the technician list for the popup menu done from?
can you provide an example of the file (emptied from its data would be ok)? I am sure this can be fixed in 5s if I can see the file...
>So what you say is correct.  It just creates holes in information.
OK, in that case I agree with Lesouef that it's more likely the technician info in the main table is an unstored calculation or something like that. Redefining the field so the information is stored in the main table is a possibility, but it's not the BEST option, because it increases redundancy.

>So I'm thinking on every situation like this, I create a active/inactive button and add in a
>script to the buttons to find all actives when the subforms are called.
>This sound like a solution?

That's a quick and easy option, but one limitation of it is that it's still possible for folks to see the other records by doing Show All.
Here's another option which might be more foolproof: Filter what technicians show up in the selection by editing the relationship itself so that instead of showing all records, it only shows records where the employee status is a certain value, using a portal or a drop-down based on a value list. That way, all the records are still there, but only the current employees appear as choices. The details of doing this vary depending on your FM version.

CruizectrlAuthor Commented:
Alright.  So I have the technician records in one table.  In the main database I have created a value list that will allow someone to select a technician through a dropdown on the main table.  Now, if I do this whole active/deactive thing,  How do I get my value list to show only the active records
Suppose you have a field in the techician table called TechStatus, which has one of two values, such as "Active" or "Inactive."
Create a Global fieldfield of the same type as the TechStatus field, called globalTechStatus or something.
Next, create a self-join relationship between the technician table and itself, based on the ActiveTechStatus field and the TechStatus field.
Create a new value list, based on the values from the technician table, but check the option to use only related records.
Now create a drop-down or popup menu field based on this value list.
Enter the "active" value in the globalTechStatus field. Now, when you use the popup, it will show only those technicians who have the active value in the status field.
Some of the specifics differ depending on which version of Filemaker you're using, but the concept is the same.

Here's a simple demo file done in FM6 that illustrates the concept.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CruizectrlAuthor Commented:
Thanks Bill.. Just what I was looking for!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.