Tricky Test function HELP!

[liltbrockie]

Hi, i'm trying to make a text function that will take the contents of a text field and return a sort of summarised version..

TextA =

2 x Take Away Old Appliance @ £15 each

1 x Take Away Old Appliance @ £15 each

2 x Take Away Old Appliance @ £30 each

1 x Take Away Old Appliance @ £25 each

3 x Take Away Old Appliance @ £40 each

2 x Take Away Old Appliance @ £40 each

And im trying to make TextB =

3 x Take Away Old Appliance @ £15 each

2 x Take Away Old Appliance @ £30 each

1 x Take Away Old Appliance @ £25 each

5 x Take Away Old Appliance @ £40 each

Can this be done?

[Søren Dyhr]

While it certainly not could be said to abide to the one fact per field rule from 1NF could there be a solution via this.

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

But I do strongly suggests you normalize your data! Turn to subsummary reporting when building the invoice:

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

--sd

Edited August 3, 2009 by Guest

[comment]

CollapseList() does not summarize. I suppose it would be possible to use a custom function here, but the OP doesn't indicate Advanced version, and (more importantly) I don't see how it would improve things. It seems almost certain that the next step is to multiply the summarized quantities by price, and produce a total. The best approach, IMHO, would be to start at the beginning and split the data into records and fields.

[Søren Dyhr]

Indeed my point although perhaps a bit convoluted? : - I didn't say the CF as is, solved the problem, and raised the yellow flare right away ... nobody should likely land themselves in such a problem unless patching up for others mistakes and hardly any time at hand to do some proper development.

In short, this isn't a text processor - but a RDMBS....

--sd

[comment]

  Quote

I didn't say the CF as is, solved the problem

True, but what I meant is that the CollapseList() function wouldn't provide a good starting point for this.

[Søren Dyhr]

I guess I've chosen the wrong CF, I seems to recall someone have been threading this path - perhaps It's this one:

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

...where you then make a dynamic valuelist on a selfjoin on the recordID and utilize Valuelist(Items in the first parameter of the function. But trying so is indeed a daft endeavour!

--sd

[fseipel]

Attached is a solution to the problem, albeit in script form. It should be possible to convert this into a custom function if necessary, using recursion, two indices, and a working results parameter. Alternately it can at least be converted to a script that takes a parameter if it is used frequently.

This implementation assumes the descriptors are sorted as the data you provided in example (i.e. all items with same price are sequential in list). If not, a minor modification will allow it to work for non-sorted input.

I suggest using this as a template to get the data into fields. In that case it may be advantageous to also break out the price as well as quantity and descriptor which this script already breaks out.

Summarize.zip

[Søren Dyhr]

  Quote

It should be possible to convert this into a custom function if necessary, using recursion, two indices, and a working results parameter.

No one have ever doubted that, it's the feasibility to deliberate break first normal form - which really needs a serious argument!

Filemaker have now ever since 1995 been a sort of implementation of Boyce and Codd's theories, why deliberately ignore this? Have we all been barking up the wrong tree?

--sd

[liltbrockie]

Thanks everyone for your help.. in the end i did just break it down to related tables and summaries...

Just a bit too messy doing it any other way

[fseipel]

That's good the data has been put into atomistic fields, when it is aggregated it is extremely difficult to search or generate reports. Unfortunately this frequently becomes a problem only after large amounts of data have been stored aggregated, then the need for reports arises.