GetNthRecord and Repeating Fields in Custom Functions

[AJV]

In a related topic (Multiplying Fields Together) a solution to the problem of multiplying together a set of fields (in separate records) was given:

Product (fieldname; n_records) = If (n_records>1; GetNthRecord(fieldname; n_records)*Product(fieldname; n_records-1); GetNthRecord(fieldname; n_records) )

Now suppose that fieldname is a repeating field. My testing seems to indicate that this custom function only works for the first repetition. I found that surprising (I am learning to not be surprised by FileMaker), but I can't seem to figure out why it is not working. Based on the documentation for GetNthRecord it seems the syntax for returning a repeating field is, for example,

GetNthRecord(MyRepeatingField[2]; 2]

This seems to imply that I need a function that returns the nth repetition field (not its value since GetNthRecord needs a field). But such a function does not seem to exist. I tried

GetNthRecord(fieldname[Get(CalculationRepetitionNumber)]; n_record)

but that syntax is not allowed.

I have a feeling I'm missing something obvious. Any pointers?

Thanks...Andrew

Edited September 20, 2005 by Guest

[Raybaudi]

Hi AJV

OBVIOUSLY I haven't 8 Advanced today too...

but this calc for two records goes:

Let([

n_records = Get ( FoundCount )

];

Case (

n_records ; GetNthRecord ( GetRepetition ( number ; Get ( CalculationRepetitionNumber ) ); n_records ) * GetNthRecord (GetRepetition ( number ; Get ( CalculationRepetitionNumber ) ); n_records - 1);

GetNthRecord ( GetRepetition ( number ; Get ( CalculationRepetitionNumber ) ) ; n_records )

)

)

[AJV]

Ray,

I tried

SimpleProduct (fieldname; n_records) =

If ( n_records > 1; GetNthRecord ( GetRepetition( fieldname; Get ( CalculationRepetitionNumber ) ); n_records) * SimpleProduct(fieldname; n_records - 1);

GetNthRecord ( GetRepetition( fieldname; Get ( CalculationRepetitionNumber ) ); n_records )

)

Seems the syntax is acceptable but there are only "?" in all repeating fields except for the first.

Did I misunderstand your suggestion?

Cheers...Andrew

[Raybaudi]

AH very well...I was thinking that the syntax wasn't accepted by FM (in a custom f.)!

I now have just to suppose...

have you maked the calc field as a repeating with the same repeatition number as the field "fieldname" ?

[Raybaudi]

AJV

now I am going to sleep...in Italy it's half past one.

I'll see your replay tomorrow.

Good Luck !

[AJV]

Ray,

I'm going to have to work on this some more. It seems the problem is with argument I'm passing to get n_records (as defined in the table). I'm calling my custom function with

SimpleProduct(Data::Value; Count(Extend(Data::Id))

If I use a simple scalar for n_records, for example:

SimpleProduct(Data::Value; 3)

the code in my last post works fine. So, I need to spend some thinking about how to use Extend (either in the field definition or in the custom function). It may be a couple of days before I can back to this.

Stay tuned...ajv

[AJV]

Ok, I think I finally have this figured out. The problem was trying to pass a non-repeating field (n_records) and getting it "extended" within the custom function. The complete solution is really quite straightforward...

First, the custom function to do the multiplication can be written as:

Product(fieldname; n_records) =

If ( n_records > 1; GetNthRecord ( fieldname;n_records) * SimpleProduct2(fieldname; n_records - 1);

GetNthRecord ( fieldname; n_records )

)

The calculation repeating field in the master table for doing the calculation is simply:

SimpleProduct2( Data::Value; CountofRecords) // with multiple repetitions

where CountofRecords is also a calculation repeating field defined as

Extend(CountofIds)

and where CountofIds is the simple calculation field

Count(Data::Id)

The indirection for the counting of records seems like a kludge to me, but I was unable to get Count(Extend(Data::Id) to work.

That's it: no need to use GetRepetition or Get(CalculationRepetitionNumber)

Cheers...ajv

[Raybaudi]

Hi AJV

happy that you solved !

Can you send me an FMP Advanced 8 file with the two Custom attached ? (so I can look how they works with my FMP8 trial)

[AJV]

Ray,

I'd be happy to. Is there away on this forum to send you files or do I need your email address.

Cheers...ajv

[Lee Smith]

Click on the "Help" in the Menu Above, OR HERE and then [color:green] Frequently Asked Questions and then jump down the list to [color:red] Posting Messages and then click on [color:red] Can I attach files to my post?

HTH

Lee

[Raybaudi]

Hi

yes, Lee is right.

BTW this is my email: "cleared"

Many thanks !

Edited September 21, 2005 by Guest
Email cleared

[Raybaudi]

Hi, AJV

many thanks !

But...were is this ?

Product(fieldname; n_records) =

If ( n_records > 1; GetNthRecord ( fieldname;n_records) * SimpleProduct2(fieldname; n_records - 1);

GetNthRecord ( fieldname; n_records )

)

By open with FM8 trial I can't see it into the Define Calc list of functions.

By open with FM7 Dev it seems as you have named it:

GetRowData()

but it don't appears into the list of custom in 7, neither in 8 !

[AJV]

Ray,

In my version of FM8 it is under File->Define->Custom Functions...

I'm not sure where you are seeing "GetRowData()". It's not a function I wrote.

Hope this helps...Andrew

[Raybaudi]

This is a screenshot of the lonely custom function that I see