List to ordered stream of data

[jorfasan]

Hi,

I get, through ValueListItems function, this result in list format:

FP-01209-1629

FP-01209-1630

FP-01209-1631

FP-01209-1632

FP-01209-1633

FP-01209-1634

CE-01209-1632

CE-01209-1636

And I would like to change it to:

FP-01209-1629-1630-1631-1632-1633-1634, CE-01209-1632-1636

or:

FP-01209-1629,1630,1631,1632,1633,1634 / CE-01209-1632,1636

The structure of each code is as follows

12345678901234

FP-000-000-00

FP-0000-000-00

FP-00000-0000

FP-00000-00000

The FP can be CE, and the 0 are any number. The length is fixed in a record but changes from record to record.

For Filemaker 6, please (I'm sure that 6 to 7 conversion is affordable)

Any ideas to code it in a Calc field? Thanks

[Lee Smith]

How is this value list established?

In other words, is it result of user entry, is it hard coded, or a calculation that is concatenating the text from other fields?

Lee

[The Shadow]

Here's one way that gets you pretty close, this would be a lot easier in FM7, but I've used multiple calc fields to make up for the lack of variables. I call your input values list:

The first two calculations find the prefixes to consume:

fpFront =

If( Position(list, "FP", 1, 1),

Middle( list, Position(list, "FP", 1, 1),

                    Position(list, "-", Position(list, "FP", 1, 1), 2) - Position(list, "FP", 1, 1) + 1 ), "" )

ceFront =

If( Position(list, "CE", 1, 1 ),

Middle( list, Position(list, "CE", 1, 1),

                    Position(list, "-", Position(list, "CE", 1, 1), 2) - Position(list, "CE", 1, 1) + 1 ), "" )

fpOutput is an intermediate to keep the final calc a reasonable size.



fpOutput =

If( not IsEmpty(fpFront) and (PatternCount(list, fpFront) > 1),

     Left( list, Position(list, fpFront, 1, 2) - 1 )

     & Substitute( Right( list, Length(list) - Position(list, fpFront, 1, 2) + 1), fpFront, "" ), list )

output =

Substitute(

If( not IsEmpty(ceFront) and (PatternCount(fpOutput, ceFront) > 1),

Left( fpOutput, Position(fpOutput, ceFront, 1, 2) - 1 )

& Substitute( Right( fpOutput, Length(fpOutput) - Position(fpOutput, ceFront, 1, 2) + 1), ceFront, "" ),

fpOutput ), "

[jorfasan]

Lee,

It is the result of user entry. It comes from a related table. A layout with a portal and the user sets the data directly. Later a process should extract this keys and I wondered "could be posible to order the data as is presented now in a manual system". So I posted the question here.

Thanks,

[The Shadow]

Here's one using FM7, that is done with a single custom function. It works for an arbitrary number of prefixes, and adds the "/" between sections.

This demonstrates clearly, FM6 sucks.

[jorfasan]

Hi Shadow,

First of all I have combined all your calcs into one that evaluate just the list field, as given in your brilliant file attached. Just to point it out, the five pages calc triggers the field list 289 times. That remarks me two important facts: the excellence of your analysis and implementation and the Version 7 jump

[jorfasan]

Sorry, the file attached.

[The Shadow]

The thought of 289 field references is a bit unsettling, and the thought of the single calculation that could do several versions of each prefix makes me rather nauseous.

I realize its not quite what you were looking for, but here's a version that can do the more general case with a script and a couple of globals. The script handles multiple FP or CE prefixes, and could easily be extended to handle other prefixes as well.

[jorfasan]

The thought of 289 field references is a bit unsettling, and the thought of the single calculation that could do several versions of each prefix makes me rather nauseous.

You know the path: you solve the issue in five fields and I try to simplify in one or two. The result is, as seen, daunting. But the try was worthly.

I realize its not quite what you were looking for, but here's a version that can do the more general case with a script and a couple of globals. The script handles multiple FP or CE prefixes, and could easily be extended to handle other prefixes as well.

my expectations, mr shadow, are always surpassed by the technical excellence that comes from the top posters of FMForums

A way of say you thank you very much.

All the best