Showing Count of Value List Items

[stefanshotton]

I have a field that contains the lender a customer is using, this is selected via a value list based on a field in a related table.

I need to set up a report that shows how many times each lender is used(preferably with zero used lenders excluded)

ie

Bos 9

Woolwich 4

Halifax 6

Gmac 5

Not sure where to start.

[IdealData]

You need a calculation field in the lenders table using the COUNT function.

The count will return the number of related records if you use the related key value as your field to count.

Count (customers::lender_id)

[stefanshotton]

Thanks but need to be able to constrain between 2 dates

[Søren Dyhr]

Then put the date range in two extra criterias for the relation...

--sd

[stefanshotton]

Tried that somethings going wrong though.

Heres the two tables and fields

Product Sales

Customer ID

Lender

Date

Lenders

Lender Name

gStart(global)

gEnd(global)

Have set up the relationship as

Lender=Lender Name

and Date>=gSart

and Date<=gEnd

How do I then get a count of each lender?

[Genx]

Evaluate from the left side TO, to the right TO (the option where to evaluate from is located at the top of the calculation options).

You should also be counting the right TO from the left TO hence counting whilst restricted over the relationship.

I.e. evaluate from left TO

Count(RightTableOccurance::Lender Name)

~Genx

[Genx]

Where product sales is the left table occurance and Lenders is your right one: so evaluate in the context of Product Sales

Count(Lenders::Lender Name)

~Genx

[John Mark Osborne]

What you really need is a subsummary report. This is the way FileMaker was designed to produce totals like you are requesting. Create a Subsummary part sorted by Lender field. You won't need any other parts unless you want to put a Header with a title or a Grand Summary with a total number of Lenders. Place the Lender field in the Subsummary part and also a Summary field that counts the Lender field. You can place the same Summary field in the Grand Summary part since Summary fields are context sensitive.

There are several advantages of the subsummary approach over the relationship approach. First, the subsummary report can be modified by finding different records. If you want a report on the month of May then locate all records in the month of may. If you want a report on the entire year, then search for all records that match a year. Secondly, relationship reporting can be very slow especially with a lot of records. Subsummary reports are the way FileMaker was designed to total records and is very quick.

Once you are done with your subsummary report, you need to sort the database by the Lender field and then either enter Preview mode or Print. You can't see the report in Browse mode.

[Genx]

Oh right, report, good point!

[Søren Dyhr]

Once you are done with your subsummary report, you need to sort the database by the Lender field and then either enter Preview mode or Print. You can't see the report in Browse mode.

Other options exists though:

http://www.kevinfrank.com/download/repeating-lookups.zip

...the lookup-repeat-v3.fp7 template, although I suggest you approach all templates in the download ...which gradualy gets more and more complex, the v3 is the toughest!

--sd

[John Mark Osborne]

Repeating fields have limitations based on how many repetitions you want to add. Plus, they don't page break as well as separating the records in subsummary report. The repeating field solution you reference is really best for cross-tab reports which aren't fully supported in FileMaker with built-in features. Really, the best standard reporting approach is the way FileMaker was intended to be used. Use a subsummary report.

[Søren Dyhr]

I was not as much the utilization of repeating fields as the use of both aggregation functions as well as genuine summaries, I meant by pulling this example file.

One of the finer points was, that neither sorting nor flipping into preview mode, is required ...in the third template is a "Trailing Grand Summary" part doing the stuff even in browse mode ...give following a try Omit one of the parent records and see the freshing of the sums.

The issue with the lookup is that fields needs to be stored to make a summary function work ...while it in the child table due to summing over a relationship is bound to remain unstored.

Then a question, you said this:

Secondly, relationship reporting can be very slow especially with a lot of records.

Which I too belive have it's bearings, however did I at devcon see Andy LeCates demonstrate that multicriteria relationships have been rewritten into something very tight and agile from version 7 to 8, is it the same as Steven Blackwell tells about discarding RPN as algorithm. Personally didn't I get particular far into assembler in my tuition, so I can only drop my jaw when hearing such things....

Will multicriteria relations be faster than monocriteria dittos?? Two shorter indexes vs one long??

--sd

[Genx]

Could you please explain what RPN means please?

Any if you could go a bit more in depth relating to speed of calcs over a multi / single criteria relationship?

Thanks,

~Genx

[Søren Dyhr]

Could you please explain what RPN means please?

Not remarkably better than this ....

http://en.wikipedia.org/wiki/Reverse_Polish_Notation

...but I paid attention when reading this:

http://www.nabble.com/Case-vs.-If-t1350857.html#a3622429

Any if you could go a bit more in depth relating to speed of calcs over a multi / single criteria relationship?

No, thats maybe a flaw in the Devcon concept, speakers throw in some statements or examples under timerestraint ...they can't easily be challenged by the audience, and especially LeCates comes in like a hurrycane of peptalk. Well I think it the idea you should feel comfy, in your choise of tool... by being on a bandwaggon progressing at full throttle?

Later at last years Devcon, was there perhaps not a direct conradiction made by Chris Moyer, who in his speak of optimization mentioned that searches could benefit form being performed on as single calcfield containing the cartesian product (not the relation type) of all field you need in the request, instead of turning indexes on in every field in the record likely to recieve a request value.

Well I do care for knowing such matters, but the hearsays and best practises of this ... well why repeat myself - read this instead!

--sd

[John Mark Osborne]

Creating reports with relationships also limits the found set you can report on because relationships disregard found sets. This is one of the great benefits of a traditional report. You can change it to a different report just by performing a find. Of course, you can attach additional match fields to a relationship but it's not nearly as flexible as being able to search on any field in your solution. I'm not saying reporting on relationships is bad just use it in the right situation. If you abuse it, you will lose flexibility and possibly slow your solution down.

[Søren Dyhr]

I'm not saying reporting on relationships is bad just use it in the right situation.

Niether am I saying that reporting or anything for that matter should be done outside the realm of the tool ...it's very tough to distinguish when a neat method, contribute to a persons road to virtuosity and when it just add's further to the confusion.

When it comes to it isn't it just "the right situation" but just as much, if the person copies unconciously ....

Ugo have a excellent disclaimer to his posts:

Please never directly implement tips, calcs or scripts directly into your files, specially when it comes from my .... brain.

We would like to believe, that everyone reading our posts takes what we say/write with a:

http://en.wikipedia.org/wiki/Grain_of_salt

But it's unfortunately not true!!!

--sd

[John Mark Osborne]

Then a question, you said this:

Which I too belive have it's bearings, however did I at devcon see Andy LeCates demonstrate that multicriteria relationships have been rewritten into something very tight and agile from version 7 to 8, is it the same as Steven Blackwell tells about discarding RPN as algorithm. Personally didn't I get particular far into assembler in my tuition, so I can only drop my jaw when hearing such things....

Will multicriteria relations be faster than monocriteria dittos?? Two shorter indexes vs one long??

--sd

I just talked to Andy Lecates and yes, it took me this long to get in touch with him. For those of you who don't know him, he is the Senior Systems Engineer for FileMaker, Inc. and an incredible wealth of knowledge, which explains why it is so hard to get hold of him. Anyhow, I asked about this statement and he says he always steers people towards traditional reporting methods (subsummary reports) for speed reasons and flexibility. Unless there is some really great reason to use relationships to simulate a report, don't do it. FileMaker was not designed to aggregate massive amounts of information through relationships. Anyhow, I just wanted to make sure readers were clear on the best approach to reporting.

[Søren Dyhr]

FileMaker was not designed to aggregate massive amounts of information through relationships.

Thanks for elevating a hunch to into knowledge!

--sd