Grouping and Counting Values

[stockingup]

Hi Everyone,

I have the following situation I cant get my head around. I have a table "Customers" and a portal to a table "Machine Line Items". Each record in this second table has a "machine type" field.

My problem is that on the report for each engineer's route, I need to display how many of each type of machine there is, in one field!

For example:

Lets say there are 3x DC, 1x MV and 2x NP. Each one of these would be a seperate record in the "machines lineitems" table - a total of 6 records. I would need a field to display on this report to say that there would be:

3xDC, 1xMV, 2xNP...

Is there anyway to achieve this via a calculation field? Or would I have to be running a script to concactenate the field values, count them etc etc??

Best Regards,

Jason V.

[Søren Dyhr]

Is there anyway to achieve this via a calculation field?

That would be an inadequate way of solving the problem, this is supposed to be solved via reporting skills:

...when this is learned, dissect this template:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000717

...and observe the reporting actually is done in the most atomic of the tables exploiting the tunneling of values from relations away.

When you then master these techniques, could you turn to this URL, to see the initial thing I quoted you for suggesting ... which follows that approach ... if it really is nessersary at all in this case?

http://sixfriedrice.com/wp/youtube-roundup/

--sd

[stockingup]

Hi, I can't help but mention that the somewhat arrogant attitude is hardly appreciated.

I am familiar with reporting procedures. However, the client I am working for requires that the solution match that which is manually employed today.

As a result, I need all this information collated in one field. It also cant be related, because as time goes on, the machines, subgroups of machines, and their relevant quantities will change. However, for every individual visit, the numbers must be copied into a field - so that in retrospect these do not change as machines are added and removed.

None the less, I thank you for your help and the resources you linked me to.

Regards,

Jason V.

[Søren Dyhr]

It isn't my intention to offend you in any manner ... there is BTW another link/template you need to see then:

http://www.kevinfrank.com/download/kf-fast-summary.zip

But clients requiring or being so specific, should build their applications themselves. Since the use of calc's instead of genuine summary is against gist of the tool.

Another option you should consider to combine .pdf's to facilitate such matters!

The sharpness in reply, might be that dedicated tearing a tool out of it's realm ... is a always returning topic in forums, and often a source to a lot of grievance!

--sd

[bruceR]

See this custom function:

http://www.briandunning.com/cf/890

[stockingup]

Thanks for everyones help. I managed to get it working with that custom function you linked me to but eventually convinced the company to go about it with reporting instead. It is not reporting on all machine locations seperately.

Thanks again for all the help.

[Søren Dyhr]

eventually convinced the company to go about it with reporting instead

I would like to know how this pursuation happened, since spreadsheets are king of the hill as metaphors?

In societies where freedom of speech was not recognized as a right, the court jester - precisely because anything he said was by definition "a jest" and "the uttering of a fool" - could speak frankly on controversial issues[3] in a way in which anyone else would have been severely punished for, and monarchs understood the usefulness of having such a person at their side.[4] Still, even the jester was not entirely immune from punishment, and he needed to walk a thin line and exercise careful judgment in how far he might go - which required him to be far from a "fool" in the modern sense.

From: http://en.wikipedia.org/wiki/Jester#Political_significance

--sd