Counting Number of Related Records in Portal

[gaby78]

2 tables : Customer table related to Invoice table as follows:

CUSTOMER INVOICES

CustId = CustId and

gZero <> BalanceDue (gZero is a global field in Customer table = to 0)

I have a portal of Invoices on Customer layout.

How can I display on the layout the number of invoices that appear in the portal (ei: the number of related records)?

[LaRetta]

As long as BalanceDue isn't an unstored calculation, you will create a calculation (number) in Customers with:

Count ( Invoices::InvoiceNumber )

...(ei: the number of related records)?

It wouldn't give you the number of Invoices for that Customer but rather the number of Invoices for that Customer with a balance other than 0. Edited December 20, 2007 by Guest

[gaby78]

Exactly what I needed.

Would the approach be the same in case the relationship involves 3 fields like

Field1 > FieldA

Field2 = FieldC

Field3 < > FieldD ?

Thanks for your help

[Søren Dyhr]

Why would you expect it was different??

--sd

[LaRetta]

As Soren is suggesting, the answer is yes. All calculations applied to a relationship will work on the related set, so if you change the relationship (by filtering it in various ways) then the results of the calculation will change in respect to that filtering as well.

This manipulation of relationships (filtering and creating multiple same-table TOs to connect in various mix-and-match configurations with other TOs) provides great power and flexibility.

[gaby78]

Simply because I wonder which of the 3 fields to use: Count(FieldA) or Count(FiledC) or Count(FiledD). Or would they all return the same number?

Thanks for the feedback.

[LaRetta]

Count(FieldA) or Count(FiledC) or Count(FiledD)

If they all contained a value, they would all return the same result. What wouldn't be counted is number of records with blank values. This is why it is usually recommended to count an ID such as Invoice Number (which would never be blank) if you want to count the number of related RECORDS. But you may also just want to count the number of related records with a value in one particular field so, in that case, it would count the number of non-empty records for the field specified.

Good question and important distinction, by the way. :smile2:

Edited December 20, 2007 by Guest
Added sentence

[Søren Dyhr]

Yes your validation's should have prevented null values, if we were to look strictly at it, filemaker is slightly more forgiving than other tools, when it comes to aggregations is it behaving spreadsheet'ish - however LaRettas idea works!

But there seems to be some sort of tagging via the value of a global, you can't make the relations work with unstored values on the many side. Instead should it be done like comment shows in his template here:

http://www.fmforums.com/forum/showpost.php?post/275746/

--sd

[LaRetta]

I appreciate you taking this a level deeper, Soren. On a basic level, record marking (tagging) might not be used to achieve the Balance at all; rather, it could be simple auto-enter or even indexed calculation (if all fields are within Invoices).

Most times, however, a Balance on an Invoice implies aggregated related lineitems and the method described in the link can eliminate the need for writing static balance. Thank you! :wink2:

[LaRetta]

Why isn't this post displaying?

[Søren Dyhr]

I discovered the same thing yesterday, I thought it was when you passed midnight would the post and you be on differnet sides but no, but if you turn listing to "Week" is it not showing.

--sd

[LaRetta]

That brings it back!

[gaby78]

The fields always have values. Thank you both for your replies.