If and count calculations

[Bill Atkin]

I am trying to get a filed to display the last admission dates and I get nada. What I have for the field calc. is :

field= If ( (Count ( Admission date))<2; ""; GetNthRecord ( Admission date; (Count ( Admission date))-1) )

I thought this would go: If there is only one record return nothing if more than one go to the end of the repeating field Admission date and get the second from last. I am desperatly confused, please help.

[David Jondreau]

Are you really using a repeating field? Count only counts the number of related records, not the number of repetitions in one related record.

From your brief description of your database, I'd recommend not using repeating fields.

[Bill Atkin]

Thanks for the quick response. I want to store a number of dates for admission's. What other options do I have? I wanted them to just input a new date in a field and then have them be stored and be able to display the last 4 'Reasons for departure', Departure date', 'Admission date', etc..

[David Jondreau]

I'm unclear on how the database is already set up and what you want to accomplish. If post a description with tables, relationships, layouts, and relevant fields it'll be easier to help.

[Bill Atkin]

Here is the database, the fields I am trying to get to display that way begin with RA i.e. RADate1

Copy_of_MainIX.zip

[David Jondreau]

The proper way to set up a Filemaker database is to use a separate table for each 'class' of information. A class could be People or Contact Info or Admission, though how a 'class' is defined is up to a developer and the information you want to capture (for instance one may think it's worth having a separate table for Guests, Social Workers, PGSO, Children or maybe just one table for People in which you define each type of person).

Regardless, this system needs at least 4 tables rather than just one. It can be a little overwhelming, but let's just start with two tables:

Guests

Intakes

You would split the current fields into two tables, one to capture the person-specific info (Name, Age, Children, etc) the other to capture the Intake-specific info (Facility, Interviewer, Admit date).

You need a unique ID number for each record in each table (easily handled by created a number field with an auto-entry serial number) and a field in Intakes called Guest ID. Then you relate the two tables. Each Guest will be related to one or more Intake records. Each new Intake would entail the creation of a new record in the Intake table (and a new record in the Guest table too if the Guest is new).

Once you start 'normalizing' the database, changes will be much easier and the solution will be very powerful.

But, if you don't want to go through all that to solve this one issue, I understand. Instead use:

If (Count(Admission)>1; Max(Admission))

[Bill Atkin]

I realized what my problem is i need to setr up more tables with relationships to those fields, so i guess I have some more reading to do. Thank you, apparently I should have figured that out sooner.

[AudioFreak]

Most of us have made the same mistake, but to a much worse degree. At least you don't have 100 repeating fields w/ 20 reps each.

A few tables, relationships, portals, multiple layouts and yer on your way to a solid system.

From the looks of what you are doing, good luck to you and the business you are running. If you need help ask away, someone is sure to help guide you, including myself.

Michael

[Bill Atkin]

Actually I am trying to set up databases for 5a 5 facility homeless shelter and enable them to get reports coresponding to gov't and grant funding...been fun so far ...

[Søren Dyhr]

(for instance one may think it's worth having a separate table for Guests, Social Workers, PGSO, Children or maybe just one table for People in which you define each type of person)

What is missing here is the mission statement, why is this form turned into a database in the first place. What kind of searches are likely to be performed ...reporting from such a flat structure is quite a challenge. But first when the mission statement is present could proper guesses regarding the structure be made.

What I usually think of when I see such a vast number of field is in these two directions:

http://www.filemakermagazine.com/videos/data-tagging-classification-vs-organization.html

....and this:

http://www.fmforums.com/forum/showpost.php?post/204083/

Since you with this kind of structure have opened for structured seaches a table away, which also gives more reporting options, but my hunch tells me that a lot of these reports have AND'ish structure, a kind of datamining, where you have an urge to see if the incidents draws patterns.

Taken to the extreem, could searches make found set in the the many part of the relation, which then by a double GTRR(FS) back and forth over the one_many relation, make a constrained search and yet a GTRR(FS) back again, give you forms where all criterias are met!

--sd