Text Field relating to date in other field

[Thad]

I am trying to create a field in Filemaker that will give me a Yes no answer depending on a number from another filed. I have a date field that records the Death Date of an animal. I would like to link that to a field that reports Alive or Dead if the death date is entered. Can anyone help with this? Thanks

[Kriski]

I may be doing this an old-school way, but can't you have a calc:

If (Death Date = " ", "Alive", "Dead")

which, if there is no date in the Death Date field, results Alive otherwise results Dead???

[Thad]

Thank you! That does work, but I would like it to list Alive if there is no number in the Death Field. I am also using a field to calculte Age. I would like to stop the count when a Death Date is entered. Can anyone help?

[comment]

1. I would use a calculation with a number result =

IsEmpty ( DeathDate )

This returns 1 if the animal is alive, 0 otherwise.

In Layout mode, format the number as Boolean, showing non-zeros as "Alive" and zeros as "Dead".

2. The Age calculation can be prefaced with the same condition:

Case (

IsEmpty ( DeathDate ) ,

... current formula ... ,

... the same formula using DeathDate instead of Status(CurrentDate) ...

)

[Thad]

The first

one worked, but I am having a hard time with the second. My Age Fiels has, Today-Date Of Birth, for the calculation. Do I need to redefine the age field, or Death filed? Please help....

[Thad]

I got it working. One more question. Can I make a button in the list mode that will only list my Live Mice? Also one to list the Dead mice. Thanks you so much for helping!!

[comment]

Are you computing the age in days? If so. you can use:

Case (

IsEmpty ( DeathDate ) ,

Today - Date Of Birth ,

DeathDate - Date Of Birth

)

However, the Today function has some problems (do a search here to read about them). Most people prefer to use Status(CurrentDate) instead.

Note that a calculation using Status(CurrentDate) needs to be defined as unstored.

[comment]

You have 2 options here:

1. A script that goes to Find mode, enters either 1 or 0 in the Alive field and Performs Find.

2. Create 2 calculation fields:

constant1 (result is number) =

1

constant0 (result is number) =

0

Define two relationships to the same file:

Alive:

constant1 = thisFile::Alive

Dead:

constant0 = thisFile::Alive

Define the buttons to Go To Related Record (Show related only). Each button uses one of the relationships.

[Thad]

I like the script idea, but how do you have it enter 1 or O and then perform find? I can get it to enter Find mode, but after that I need help. Thanks

[comment]

You can do the find manually first. Then the script will remember it, if you select "Restore find requests". This is very basic stuff and well covered in FMP help.

[Thad]

I could not find restore find request as a script option. I also could not find it in help. Thanks...

[comment]

See the attached file.

Mice.fp3.zip

[Thad]

Thanks a bunch!! That helps. I am trying to organize a database of Transgenic Mice in my lab and I was using Excel but I thought I would switch to Filemaker. However, I am a novice at this program and I am finding it more difficult to deal with. Currently I have fields for ID#, Date of Birth, Strain, Genotype, Age, Date of Death, Experimental Notes, Sire, Dame, Genotyped, Sex, and Status. I am trying to figure out a way to link all this info. Any help is appriciated. Thanks so much!!!

[Thad]

Again thanks for the Help. I have another question:

I would like to keep my records separate for my live and Dead animals. For example, I would like to only have my live animals active so that when I search a cage only live mice show up. Also when I search my other fields live mice show. Only if I do a search for Dead mice do I want them to show. Any tips on how to manage this?

[comment]

No, you wouldn't (want to keep them separate). Mice are mice, whether alive, dead or in-between. By keeping them together, recording a death of a mice is a simple matter of filling in the date of death. Otherwise you'd need to copy the entire record to another file and erase it in this file - an entirely unnecessary complication.

To find mice that (a) belong to Cage x AND (: are alive, go to Find mode, input "x" into the Cage field and "1" into the cAlive field, and hit Enter.

As with any find, you can set up a script that will remember the search criteria and restore them. You can also define a script that will set the search criteria dynamically, depending on context. Once again I urge you to read the help on the subjects of Find mode and Find script steps.

[Thad]

Good advise. I have been looking over the help file, but remember, I am learning as I go (slowly). Tips from you and others help in understanding much quicker. I was thinking of separating only because once the mouse is dead, it is out of a cage, and I only refer to it once in a great while. I just wanted a method to look at live mice and sort them without the dead mice involved. Your attachment was helpful, and your advise appreciated.

[Thad]

I have another little problem. I am trying to link my data to a new layout that I can se to print Cage Cards. I would like to include many of the fields I have, but I would like it to be on a 3x5 index card. I would also like to have a small chart on the card for marking. Is there a way to do this? Change the margins that samll and add a chart. I could not find any way to add a chart. Thanks!

[chaversteve]

Perhaps I should be posting this as a separate query but it is related.

I want to add a calculation field to a database that will automatically complete itself in date format to be a date exactly 10 years after the date entered in another existing field.

How can I structure the calculation in the new field to get the result I am after?

Steve

[LaRetta]

Hi Steve,

If the 10-year date field is in the same table as your original date field, you can use a standard date field with Auto-Enter calculation (unclick 'Do Not Replace') or a date calculation of:

Date(Month(originalDate); Day(originalDate); Year(originalDate) + 10)

If the original date field is in another file/table, you'll need to use a date calculation (unstored) for it to update if the original date is changed.

LaRetta