Counting specific fields in a table

[Aussie John]

Hi Hope this is not too confusing.

I have a table where I can select a field which is a title for the table. Basically I want to have a sequential set of numbers based on the Title. I know I can calculate the number of records with a particular specific title using a script but is there a way using a self relationship so it is automatic.

I can see I will still need to include it in a script to make the number permanent but if possible it seems a better way than using finds and record counts. I would also get to see the number before running the script.

Thanks

[Razumovsky]

Hi John,

I think you need to provide more info on what you are trying to do, it is not really clear.

Are you trying to have a sequential serial number that includes the title name in it? Like

Apples 1

Apples 2

Apples 3

Perhaps attaching a file might help.

-Raz

[Aussie John]

Hi Raz - yes that's the idea

Apples 1, apples 2 apples 3

oranges 1, oranges 2

pears 1, pears 2, pears 3, pears 4

the above example would be 9 records

thanks

[Razumovsky]

Try creating a self join relationship of FruitName = FruitName

then use the calculation

fruitname & " " & count(fruitname::uniqueID).

you might have to add +1 to make it work correctly depending on how you create/committ records.

You can put this calc in a calc field if you want it dynamic, or as an autoenter in a text field if you want it static. Keep in mind that if it is static and you delete an earlier record then create a new one of the same type, you will end up with duplicates...

HTH

-Raz

Edit- on second thought, this wont work as a dynamic calc, as all records of same FruitType would display the same result. Autoenter would be fine.

Edited March 7, 2006 by Guest
original post too late at night...

[Aussie John]

thanks Raz

but Im afraid I havent gotten the hang of your idea.

I've made the self relation ship to the "fruit" title but i just get an "index missing" in the "fruit xx" count number field. Also when you say count unique ID, which field is that supposed to be?

[Razumovsky]

Sorry,

UniqueID is just a naming convention that I use for a field that holds a unique and hidden unmodifiable ID for each record in each file, used as the key field for one to many or one to one relationships between records.

You could actually use any field that you know will ALWAYS have a value (any value) in each record. The count function just counts the number of records that have a value in the field referenced.

Your 'index missing' could be a result of referencing an unindexable field in the count function.

Also, you havent said if you want it to be set once and retain the same value, or recalculate every time a prior record was deleted.

HTH

-Raz

[Aussie John]

Thanks Raz that works in my test dbase but do you think i can get it to work in my main dbase-no!!!!!

depending on which table I select the count field from either get no result or . I have checked it over and over and am sure I have done things exactly as my test.

[Razumovsky]

Sounds like your relationships might be relying on unindexable fields. Relationshps can only point away from unindexed (unstored calcs and globals) fields, not towards.

You must be sure there is a valid relationship between the two tables for the record you are on as well.

-Raz

[Aussie John]

Fantastic - that worked great changing field to indexed

thanks