Calculation Help

[brio]

I am in the process of creating a warrant file for a small police department I work for. I've created two layouts, the first has the individuals personal information. Clicking a button takes you to the second layout, a table containing seven colomns,ie, warrant#,jurisdiction, bail amount, date received, date served, bail posted- a popup list, and status- a popup list containing 4 entries in a value list. Each column has 25 sub fields, ie, warrant1, warrant2, etc. What I would like to do is create a field on the first layout that will display the word ACTIVE, one of the choices in the 4 choice status popup field, alerting any user that there is an active warrant for this individual.

I'm assuming, (i know never assume), I need a calculation field to look for the word ACTIVE in the status fields, there are 25 of them. I'm in over my head, so I'm turning to the pros for help. Any suggestions greatly appreciated.

[Tripod]

Not sure about the calculation (should be pretty straightforward--something along the lines of "IF ((warrant1 = "ACTIVE") or (warrant2 = "ACTIVE") or... ) myfield= "ACTIVE", myfield="CLEAR") BUT,

why not separate out the warrants and use the relational capabilities, etc... so that you don't have to constantly deal with subfields? ie. a perp file and a warrants file--each with a primary key.

Tripod

[Fitch]

Tripod is correct, you should create a warrants file with the 7 required fields. Each warrant will create a new record in this file. In the original file, keep the person's name, address etc. fields -- get rid of all those warrant fields -- holy cow, you created 7 x 25 fields??? Wow.

OK, so create an ID field in your main file, that auto-enters a serial number. In the warrants file create a mainID field that does NOT auto-enter. Create a relationship in the main file based on these fields, and set it to allow creation of related records.

Create a portal in the main file, and put your related fields on it.

OK, now let's get to your Active question. Create a calc number field in the warrants file like this:

status = "active"

This will evaluate to 1 when status is set to active. Let's call that field wActive.

Now in the main file create a calc text field:

Case( Sum(warrants::wActive), "ACTIVE" )

I.e., whenever the total of active warrants is greater than 0, you'll see the alert text.

[Tripod]

>>>Create a calc number field in the warrants file like this:

Do you mean a summary field--it would be across records, correct?

[brio]

Thanks to you both for reaponding, I appreciate it. That said, I'm an old DOS/Novell/Nutshell guy. We just recently upgraded our system from a DOS based Nutshell database to Windows 2000 and Filemaker Pro, took a giant leap into the 90's. I'm going to try and create whats been suggested, if I run into difficulties I'll be back to bother you guys with more questions. Thanks again !

[Tripod]

You're welcome. Normalizing the tables and such though, can really pay off later. Subfields are a pain when it comes time to report any data. A little extra work now can really pay off later!

Good luck,

--Brian

ps. Remember dbase? I used to use that back in my DOS/WinDOS days...

[Fitch]

No, I don't mean a summary field. I mean a calculated field using the Sum() function.