absurd question ?

[Raybaudi]

Hello to all the friends

They are days that I try to solve this question:

it is possible that a calculation gives back all the values of a number field of the entire DB?

Without scripts and portals...

From the first moment I thought was simple... instead it appears to be an absurd question (from which the object!)

The summary fields make something of similar (in the sense that their calculation considers the finded records or all the records);

in order to make this THEY MUST know the content of all the fields of the DB and, as an example, make of the sum.

With the Last(field) function I succeed to obtain the last value of a relationated field, for which if A it is relationated with B, I succeed to having two values in the calculated field:

B::field & " " & Last(B::field).

But from here I have't any way ...

Some idea?

[CobaltSky]

Yes, it's possible, Daniele.

One way to go about it would be to create a value list which takes values from the field in question, and which includes a second field which holds a unique value (eg a serial number) and set the value list to sort by nthe second field.

Then set up your calculation with the formula:

ValueListItems(Get(FileName); "YourValueListNameHere")

Make the calc result 'unstored' to ensure that it will update when the screen refreshes and it will return a complete list of the contents of the field (the first field which the value list is taking values from - the 'also show values from' field will not be included). ;)

[Raybaudi]

Hi, CobaltSky

Many thanks for your kindly replay.

Unfortunately the problem persists...

The question was in fact an INCOMPLETE one.

In the sense that I need that nothing aids the calculation !!

That is the result must be exclusively the result of a calculation that only makes reference to a field (text or number), eventually helped from one (auto)relation.

The calc will/would need for the construction of one personalized function (therefore taking advantage of its ricorsive abilities).

I make an example:

the field "Fruits" of the table A (that contains 3 records) is so valued:

record 1 = pineapple

record 2 = orange

record 3 = banana

I want to obtain from the calculation:

pineapple

orange

banana

he would seem simple, not?

with the formula = B::field &"¶"& Last(B::field)

I obtain:

pineapple

banana

and here I remained (like saying) nailed!

BUT PROBABLY IT IS AN ABSURD QUESTION ; -)

[-Queue-]

How does Ray's solution not work for you? If you are using a related field, then base your value list on the related field. It works the same way.

[Raybaudi]

Hi Queue

ok...it seems that I am unable to explain me !

So now the question is:

If it will be a CF of this type:

GetField(Field;RecordNumber;FileName)

I could write this code:

GetField(Fruits;1;Get(FileName))&"¶"&GetField(Fruits;2;Get(FileName)) &"¶"&GetField(Fruits;3;Get(FileName))

or I could write a new recursive CF based on the ipotetic one above...

But it seems that FM has not the ability to grab contents of a field by a Record Number ;)

[-Queue-]

I do not think your CF will work, unless each field to be referenced has its own value list.

Note that if your setup is according the one Ray described above, then the nth record's field value is

Substitute( MiddleValues( ValueListItems( Get(FileName); "YourValueList" ); n; 1 ); ¶; "" )

where n's value is determined by the sort order of the value list's second field.

[comment]

In any calculation, you can only reference fields from the same record or a related record.

The related records that you can reference are first (default), last, min, max and all.

[Raybaudi]

The related records that you can reference are first (default), last, min, max and all.

Hi comment

so I can get:

first = B::fruits

last = last(B::fruits)

but I cannot get the fruits between ?

(neither with some plugin ?)

[comment]

That is correct, AFAIK. I don't know about plugins, but I'd doubt that would be possible. This goes to the core of Filemaker's internal structure.

[Raybaudi]

But it seems that FM has not the ability to grab contents of a field by a Record Number

This is the problem...

FM cannot grab field's contents by Record Number ! (without a script)

[Raybaudi]

Hi comment

perhaps it is possible to use the function: LookupNext ( sourceField ; lower/higherFlag )...

Something like:

Let([

first = Lookup(sourceField);

next = LookupNext ( sourceField ; higher)

];

Case(

... ... ;first &"¶"& CF(next);

""

)

)

What You Think ?

Edited August 6, 2005 by Guest

[comment]

It depends on what you're trying to do. If you want a calculation that "gives back all the values of a number field of the entire DB", then I think you should follow Ray's advice.

In any case: when there is a match, LookupNext() will return the same result us Lookup(). Moreover, the function returns the CONTENTS of the source field. So, if I read this correctly, the second iteration of your CF will have "bananas" as the function parameter.

[Raybaudi]

This is the problem...

FM cannot grab field's contents by Record Number ! (without a script)

that was the problem ... now isn't! ( FM8 comes to solve !)