record count

hi

I've not been filemaking for a while. I'm fiddling w my finance db set
up a few years ago fr scratch w FM7. It works ok, but tweaking it is
always a bit hair-raising.

How do i do the following simple-sounding thing:

....a field showing the number of records in the table which contain the
same job number

It sounds like a self-join to me

Say I have 3 records out of a larger total w the same job number

Each one has a different record id but the same job number

How do I do this
- first record w the lowest id number to show the number of records w
the same job number is 1
- the second record w a greater id number to show number of records w
the same job number = 2
- the last and third record w a greater id no. that the other two
records to show 3

I sense this is simple, but as I say I haven't been Filemaking for a
good while

thdy

Re: record count

i seemed to have done it anyway but soooo clumsy

made new calc field: Count(job_number)

then a new summary field summarising above field

then a new calculation field summing above summary field and adding 1

then

a made a self join

join on job number = job number in copied table, and record id >
record id in copied table

seems to work

looks like work of barbarian: someone got a more civilised answer

Re: record count

In article
<9d7e710c-d2bc-49e7-9447-f6bdc202fab5@s13g2000prd.googlegroups.com>,
thdyoung@googlemail.com wrote:

> hi
>
> I've not been filemaking for a while. I'm fiddling w my finance db set
> up a few years ago fr scratch w FM7. It works ok, but tweaking it is
> always a bit hair-raising.
>
> How do i do the following simple-sounding thing:
>
> ...a field showing the number of records in the table which contain the
> same job number
>
> It sounds like a self-join to me

Exactly.

Use a self-join Relationship based on the Job Number and then have a
Calculation field to count the matching records.
eg.
NumberOfTHISJob Calculation, Number Result, Unstored
= Count (Relationship::JobNumber)


> Say I have 3 records out of a larger total w the same job number
>
> Each one has a different record id but the same job number
>
> How do I do this
> - first record w the lowest id number to show the number of records w
> the same job number is 1
> - the second record w a greater id number to show number of records w
> the same job number = 2
> - the last and third record w a greater id no. that the other two
> records to show 3

If you want to actually number the records this way, then you need to
use a Number fields with an auto-enter calculation almost the same as
above:
If (IsEmpty(JobNumberCount), "",
Count (Relationship::JobNumber) + 1
)

Each time a record is created it will be given the count of the
existing records with the same JobNumber plus counting itself.

Using the surrounding If statement forces FileMaker to not evaluate the
auto-enter calculation until the JobNumber field actually has data in
it.


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Re: record count

Dear Helpful Harry

You have been darned helpful and cleaned up the mess of semi-
functional but rubbishy fm work I had done

Your solution works a treat

Thank you Helpful Harry

thdy