We help IT Professionals succeed at work.

Information disapearing

Cruizectrl asked
Medium Priority
Last Modified: 2010-04-27
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.
Watch Question

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...


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?

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.


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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


Thanks Bill.. Just what I was looking for!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.