Newbie: Summary or Count?

[peoplayer]

Hi y'all.

I'm working an inventory system, where I want to be able to see how many items with the same article number exist in my database. I assume this is a really easy one for you guys, but I haven't had the luck to find the solution yet. I presume is a GetSummary function or something like that... anyone care to explain how to do this?

Thank you in advance!

[Chuck]

The easiest way to do this will be to create a relationship that joins the file to itself using the article number field as the match field on both sides of the relationship. Assuming that the name of the relationship is "Self_ArticleNum" and the name of the key field is something like "ArticleID", the calc would be:

Count( Self_ArticleNum::ArticleID )

I'm using the count on ArticleID field because it should be the key field and therefore every record should have data in that field. Any field that will definately have data in every record could do just as well.

Chuck

[peoplayer]

This was exactly what I was looking for! Thanks! By the way, any1 know a good place to learn how to filter portals? I have looked for tutorials about this without finding anything useful for beginners.

Thanks!

[Vaughan]

Portals filter themselves! The trick is to work out what value gets entered into the keys... sometimes multiple keys (keys separated by carriage returns) can do magic things, like making one record appear in many places.

[girish]

Hmmm - This is pretty basic (I hope!) but I think I have a related question here. Say I have a database that contains 7 fields -

* name (containing the person's surname)

* id (containing an unique id number)

* time (radio buttons that give a selection of 4 times eg. 13.00, 14.00, 15.00, 16.00)

* nt1, nt2, nt3, nt4 (count of times selected across total records eg. nt1 = total number of people who have selected time 13.00)

Now, if I have 50 records in my database (where nt1=5, nt2=15, nt3=20, nt4=10), if I do a find for say id=123456, the corresponding record is displayed, however, the totals displayed in the nt* fields only relate to the record on display.

How can I set the counts so that no matter what the find criteria displayed, they still represent the totals across all records?

Hope this is clear...

[Fitch]

I think I understand, but what are your field definitions for the nt fields?

[girish]

Hi Fitch - I've tried using both a calculation field (count) and a summary field (count). Both seem to have the same problem, ie. they only operate on the records that are displayed in the found set, not all of the records in the database.

[Vaughan]

Try this: make a self-join relationship that relates all records (use a calc field = 1 for both keys) and use this relationship for the Count() function.

[girish]

Thanks Vaughan - have tried this but still no luck. I have created a calculation field "selfjoin" with a value of "= 1" (ie. displays 1 in every record). I then made a self relationship with this field. However, using the count function on this field always returns the total number of records in the database, ie. if there are 50 records and only 10 of them have time=13.00, the count still returns 50. Have tried using an if statement, but this still doesn't work. The statement " If(time = "13.00", Count(testrel::selfjoin), "NA") " returns "NA" if time 13.00 isn't selected in a record, but 50 as soon as 13.00 is selected. What am I missing here?

[Fitch]

What's missing is calculated fields that extract the data from your time field:

13Counter... time = 13

14Counter... time = 14

etc.

This calc will return "1" if it's true, then you can count. total or summarize it, e.g. Sum(SelfJoin::13Counter)

[girish]

Ha! - yep, think I've got it now. I've put together a test DB and it seems to be working. Vaughan, your suggestion about the self-link was the key, I think. And Fitch, that final suggestion of yours tipped me over into enlightenment :-] Thanks...