Count of repeating values in a field

[avideditor]

Hi folks,

I'm brand new to FM. In fact, I'm justing using the trial version now to see if it can do all that I need it to.

I'm making a element library, which has 3 fields.

Field 1 contains the project number, which is entered manually. The same number will be entered into this field on several records, depending on how many elements there are for each project.

Field 2 contains the element number, which I'm using the serial number function to generate a unique number for each individual element in the database.

The 3rd field is the one I'm not sure how to do. What I'm looking for is a field that will count the number of records relating back to that records project number. For example, I've got 5 records for project number 125. When I enter a new record with this same record number, I'd like this field to automatically be filled with the number 6.

I hope that makes sense of what I'm trying to do. The documentation is a little lacking on the trial version. Is there anyplace to download any better documentation?

Thanks in advance for any help that anyone can provide.

Jamie Beedy

[LiveOak]

You have not said much about your application, but I'll bet what you really need is two files. The first would be "Projects" in which each records is one project and a related file "Elements" in which each record is an element. There would be a classic one to many relationship between Projects and Elements based upon a number field in both files called, for instance, ProjectID. In the Projects file (only) this would be an auto-entered serial number (can't modify). Don't worry if this number does not correspond to a "Project Number" you use, you can add a separate field for "Project Number".

You would create a relationship between the Project file and the Elements file based upon the field match ProjectID <---> ProjectID. We'll call this relationship "Elements by ProjectID".

To find the total of elements for a give project, create a field:

TotalElements (calculation, number) = Count(Elements by ProjectID::ProjectID)

This counts the number of related reocords. It doesn't really matter what field in the Elements file you reference, so don't try and figure out why I used ProjectID in the Elements file. The reason is simply that this was the only field in that file I knew the name of!

A good reference for FileMaker is Coulombre and Price. For more advanced programming techniques, take a look at Scriptology by Matt Petrowsky and John Mark Osborne. To get the most out of FileMaker, you will need some background in the theory of relational databases (buying a graphic design program doesn't make you a graphics artist either ). A good book on that subject, if you can find it, is The Relational Database Advisor by Kimberly Maughan Saunders (ISBN 0-8306-2500-3). It might be out of print. I'd don't know of any really comprehensive materials you can download, you'll just have to cut loose with some cash.

-bd

[avideditor]

bd

Thanks for the quick reply. And as a matter of fact, I do have 2 databases, project and element. I didn't put that in my last post in an attempt to keep my post somewhat brief

I did what you have suggested, but it isn't doing what I had hoped. What seems to be happening is that the field "TotalElements" in elements is keeping a count of how many times the "ProjectID" number is showing up in the ProjectID database. Which is exactly opposite of what I want to do. I want to keep a count of how many times each unique ProjectID numbers are showing up in the elements DB.

I also tried doing a self-join relationship in the elements DB, and doing a count on that and adding a "+1" at the end of the calculation, and that seemed to work fine, except that if I go back to the 1st record of a given project number, the totalelements has changed to show how many records total match.

I want the number not to change once it's been calculated. If it's project number 125, the first record of that project number would have a value of 1 in the totalelements field, 2nd record would have a value of 2 and so on.

Also thanks for the book recommendations. I will definitely check those out.

Jamie Beedy

[avideditor]

Ok..... I figured out how to do this with the self-join. I just turned on prohibit modification of value. Now each record keeps increases by 1 for each new record with the same projectID number.

The problem I'm running into with this is, the calculated number is one off, because the record currently being created can not be counted, because it doesn't exists yet, I assume. So, here's my calculation now "Count(Elements by ProjectID::ProjectID)+1". This solves my problem, except for the first record of a given projectID is showing up blank.

Any work around for this, or is my solution to the original problem at fault.

Thanks in advance for any advice.

Jamie Beedy

[Kurt Knippel]

if (

isvalid (Elements by ProjectID::ProjectID),

Count(Elements by ProjectID::ProjectID)+1, 1 )

This check to see if the self-join is valid, if it is then your existing calc works, if not (i.e. no elements) then it is simply 1.