marshachristy

Hi there again,

Thanks for your help on my previous topic. I have another question.

I will have multiple records for the same person, called the visit
table, where I keep track of their visit information.

I would like to have on these forms Visit ## of Total Visits for this
person.

So I want to be able to calculate the total number of records that
person has in the database and assign the visit number as they come in
based on the previous total.

I am using filemaker pro 7 and there is no status or anything in there.
I am trying to do this via a calculation so that it is automatically in
there, but I can't seem to make it work in fm7.

I am using the id number for the person as the identifying search
criteria..but it just won't work.

Thanks for any help

chris

ursus.kirk

Assuming you have a unique ID field for each person. Which would be normal
procedure. Typoos in names are easily made. Choosing from a pre-existing ID
not.

Make a self-join relation. joining PersonID to PersonID (Yes, you do connect
a fiels to itself). Call the relation PersonCount.
Now create a calculationfield. Resulting in a number. The calculation being:
Count ( Personcount::PersonId ).
The result containing the number of times that the current PersonID is in
your database

<marshachristy@gmail.com> schreef in bericht
news:1125320278.296502.325330@f14g2000cwb.googlegr oups.com...

marshachristy

Hi I am trying this but I can't seem to find a way to give the
relationship a name...as you indicated. I looked in the help and I am
not sure if I am misinterpreting what you said.

Thanks!

marshachristy

Umm got it :S
Sorry it was just naming the new instance of the table differently..I
feel special

Thanks a lot!!!

marshachristy

Hi again

Sorry, I am just having the hardest time...I got FM7 to find the total
number of records based on the person id, thanks to ursus.kirk but I
cant seem figure out how to be able to do this

This is visit # of total visits...the first part...I found this one
calculation that says get(RecordNumber) which is supposed to give the
record number from a found set...but umm I don't know how to relate it
to the Count(PersonCount::PersonID) and I looked through the group and
found some references to Status calls but I don't seem to have those in
FM7?

I have tried everything I can think of...

please help me if u can

fp

I think what you're saying is that you have eg. 3 visit records for
person A and you want the first visit record to say "1 of 3", the
second to say "2 of 3", and the third to say "3 of 3".

You already have the total count of visists, now you want the record
number portion. I've seen people get this working using Get (
RecordNumber ) in portals but it's flaky.
The easiest way to make this work is to create a calc field in the
PERSON table "TheVisistNumber_c" which is a count of total visits for
that person plus 1.
Then create a number field in the Visists table "ThisVisitNumber" and
have it auto-enter the value from "TheVisistNumber_c" in the Person
table.

marshachristy

Hi that is exactly what I want to do, but my person table only has one
record for each person, with their personal information alone, Then I
have another table with the visit info in which the personal info is
autopopulated from the person table...and lastly I have a self-join
from the visit table to the visit table for the personid.

I tried what you said but it doesn't work...I thought if I could force
the number to be, at the time of creation, total visits +1, then that
would work...but FM7 won't allow me to store the value as its being
calculated from a relationship :S

sighs

fp

To make this work you will need;

1) a relationship from PERSON::ID Person to VISIT::ID Person

2) the calc field "TotalNumberOfVisits_c" in PERSON with formula:
Count(VISIT::ID Person)

3) the auto-enter number field "ThisVisitNumber" in VISIT with
auto-enter formula:
PERSON::TotalNumberOfVisits_c + 1

4) (optional) the calculated field "TotalVisits" in VISIT with formula:
PERSON::TotalNumberOfVisits_c

Now each record in your VISITS table will contain both the current
visit number and the total visit numbers.
The field "ThisVisitNumber" in VISITS will only be populated by the
auto-enter for new records, not existing ones. To populate the
existing records, you'll have to write a script along the following
lines:
sort the records by ID Person and visit date
set global number1 to blank
starting at the first record loop through all records
if ID Person <> global number1 then set global num2 to 1
set ThisVisitNumber to global num2
goto next rec
end loop

marshachristy

Hi there again

I did try this but the problem is and I tried something similar
before...is that it won't allow me to store that field as its being
created because it references a relationship. I tried forcing it to
store the value as the record is being created but it just won't do it.


I figured I would only have to increment the total visit count in the
visit number but wow this is really frustrating.

marshachristy

Sigh so I have encountered another problem which might be why I can't
make this work...If I allow it to be a number and then auto-enter the
number field it just shows up blank in the browse mode...there is never
a number there...