Calculation help

[keepemup]

I have a field "animal type" that has a list of animals and a field "rabies". What I need is a calculation that pulls the number of dogs that got a rabies shot.

 

I don't know if I should use a if statement. if(animal type="dog", then returns the number of rabies shots that were given.

 

total dogs #rabies shot

646 200

 

Thanks

[mfif]

do you need this in a script, in some kind of interaction layout or in a report?

 

Easiest thing to do is run a script that does a Find for "dog" and then provides a Count of "rabies", perhaps in a Summary field. If you want the figure to be permanently available and only for dogs, you could add an extra calc field, RabiesShotDog = If ( type = "dog" ; 1 ; "" ), and have a global calc field that is a Count of RabiesShotDog.

[keepemup]

I need this in a report with all the information from the database that includes all the animals. I understand I could do a find on just dog but I need it to split out each animal and give the total of each vaccination.

 

I did the if(animal type="dog";1;0) but that just gives me the count of dogs but how do I include the count of rabies shot from that?

 

Thanks,

[keepemup]

Should I use a set field for this so I don't have to do alot of finds for each total I want?

[AHunter3]

You need to explain how your data is currently set up.

 

You have a field, "animal type", which either does or does not contain "dog" as its value. That much I understand.

 

What is in the field named "rabies"? A "yes" or a "no"?

 

Case (animal type = "dog" and rabies = "yes", 1)

 

will give you the value of the rabies-vaccinated dogs, and you can sum them up.

[keepemup]

Thanks Ahunter3, That is what I needed but I always got it to work by doing:

 

if (animal type ="dog" & Rabies ="rabies"; count(rabies). This also gave me the same answer. Is there a problem with having it this way or using case better?

[keepemup]

Ahunter3,

 

I need to do this for quite a few fields, is there an easier way to do this without having to make so many fields and retype the calculation for all the fields?

 

I have animal type which has 5 different animals. I have 3 different vaccines for each animal and would like a total for each animal with each vaccine. What I did for dog is;

 

field "Dog" if(patterncount(animal type="dog";1;0)

field "Dograbies" = Cas(animaltype ="dog"and Rabies="rabies";1)

field "dogsumrabies" = count of dograbies

 

I get the information that I want but Do I need to set it up like this for each animal and vaccine or is there an easier way?

 

Thanks,

[Maarten Witberg]

i think Ahunter is right, this should be thought through structurally. I'm thinking you can have several tables,

1 - animal types (AnimalTypeID)

2 - animal individuals (stores AnimalTypeID as foreign key)

3 - Individual treatments / vaccinations (stores InflictionID and AnimalIndividualID as foreign keys)

4 - inflictions (stores InflictionID )

 

where 3 is a join table between 2 and 4.

From the top of my head, you should then be able to filter all rabies shots for dogs from the table Animal Types to the Vaccinations table ( table 1 to 3)

 

but it is hard to be specific without knowing more about what this database is for.

Also, if there are multiple possible treatments to an infliction (say pills or shots, or different medications) then I assume the database structure should reflect this also...

[keepemup]

I am having a hard time setting up join tables and getting them to work right. So I have set this up the long way around. I am sure it will click one day and I will say "now that was really stupid of me".

[Maarten Witberg]

what you have may well be good and not stupid...I just don't fathom it at the moment and I have a hunch that you should think through your structure... but if my understanding is right that you have one or more many-to-many relationships, join tables are a good way to handle stuff. but not the only one.

Also it depends on what stage of development you're in. If you're just starting to build, then you might wisely track back to a structural-design phase. If you're modifying something existing or nearly finished, then it's a different story.

I know the first larger database I built makes me burst out in tears when I look at it now. And I am sure that when in a year or so I take a look at what I am doing now which I think is pretty cutting edge, the same thing will happen :rolleyes:

[keepemup]

Well, it started off with a database already designed with just one table and fields. I volunteer at this office and they were duplicating a lot of work by inputting same information in 3 or 4 different places. I have worked in paradox along time ago and thought that I could cut down on their work. So took the database they had and just added some more fields that they were missing. And now they keep coming up with things they want which in turn makes everything work differently so I just keep digging myself in deeper. I don't know file maker and what I started out doing was pretty simple then I kept learning more things and seeing that information should be set up differently but what do I do now. A lot of things just are not cut and dry. They have over 10,000 records in this database. The records they have in right now do not have a client ID but I can set that up easily but one of the problems is that they have multiple entries in for some clients. Most of the clients are in once to say maybe 10 times but they have one client that is in 600 times. I told them it needs to be change.

 

I started to set up a new database with join tables but I keep running into trouble with setting up the join tables information and inputting in the portal. I can't seem to get a handle on that. I understand the primary key and foreign key.

 

So here I am stuck in the middle, oh what do I do?::)

[Maarten Witberg]

make a list of tasks...

1a) data cleanup is their job not jours.

1b) You may conceivably facilitate the process for them with a procedure in the database.

 

2) try to finish what you've got now as best you can

 

3) take a breather

 

4) then start working on a clean sheet... redesign the whole thing...

 

if you upgrade your membership we can start exchanging samples...

[Jack Rodgers]

SIMPLE IS ALWAYS BEST...

 

A simple way to handle this is to create a summary field for the field rabies, which I assume is a boolean one or zero/null formatted as a checkbox. If not, create such.

 

Now find all records and sort on animal and run this report:

 

Animal...summary field

 

Put the two fields in the sub summary part and hide the body part by dragging it up into invisibleness...

 

Now you can quickly and easily print the report simply find showing all records and sorting on animal.

 

Cats 123

Dogs 445

etc.

 

You can do all kinds of searches on your data such as dates and produce the same report, say for last month.

 

Best not to get too complex or to try to create too many files, links, etc. The search will do all of the work. Trust me, every once in a while I forget and try to create some massive work of genius with linked files, portals, etc. when all that is needed is a simple paper report from a flat file.

 

If this isn't clear I can produce a simple example file.