Counting Customer Notes

[HiredGun79]

Hello,

I have two tables, a customer table and a customer notes table. The customer notes table has a foreign key that is a customer id to relate the tables.

I have a field in the customer notes table that determines the type of note. I have 4 different types.

I have a portal set up on the customer layout that shows the notes. I have a menu variable in the customer field that determines what notes we're looking at in the portal.

For example, I have general notes, billing notes, etc. If the user selects general from the menu, the general notes are in the portal. They select billing, they're looking at billing notes. What I want is 4 fields that show how many notes are of each type. So when a user is looking at the notes area, he can see how many notes of what type exist. Like 4 general, 2 billing, 0 directions. . etc.

How can I get a count of portal results?

I hope this makes sense.

[David Kachel]

Create 4 calculation fields in your notes table, one for each note type.

The calc is: If (typeOfNote ="billing",1,0)

You get the idea...

Then create four unstored calc fields in your customer table, one for each total.

The calc is: Sum(Notes::nameOfrelatedCalcField)

[-Queue-]

The If statement is redundant in this case, since the result is boolean. So,

typeOfNote = "billing"

is more efficient.

[HiredGun79]

Hey guys! Thanks for the fast reponse! That makes sense to me. I already implemented it and it works great. I had add another instance of customer_notes to the relationship graph to have the calculated totals show all the results. I had a different relationship with the customer table to filter the notes by catagory to make viewing easier.

Again, thanks for the help!