Repeating Calculation Field

[Musoguy]

Hi folks. I am creating a solution in Filemaker 11 where one of the stipulations from the client was that one particular layout was in the form of a spreadsheet. I have figured out how to get around the lack of horizontal portals, and make it look how they want it.

However the title for each column (there are 14 in all) is a date that is taken from a related table with 14 records, each with a date in it. I figured that rather than create 14 one line portals each starting from a different record number to create the column labels, it would be far less fiddly to create a calculation field with 14 repetitions where each repetition uses the GetNthRecord function to get a different date which I can then use as a label to run across the top of the "Spreadsheet".

 

Problem I am having is as I have always steered clear of repeating fields wherever possible, I have never tried to create a repeating calculation field before. I am not sure how to create 14 different GetNthRecord calculations within one field with each result going to a different repetition. My guess is it has something to do with the Extend function, but I have not been able to get an answer by searching online. Hoping someone can help

 

James

[LaRetta]

Hi James,

 

You can try this ... create a calculation in your parent table, result is DATE with your 14 repetitions and:

Let ( [
I = Get ( CalculationRepetitionNumber ) ;
values = List ( child::date )
] ;
Case ( i  ≤ ValueCount ( values ) ; GetValue ( values ; i ) )
)

How sloppy of me ... also you should stop the calculation after 14 values instead of calculating for the entire batch.  Best to set the values to LeftValues ( List ( child::date ) ; 14 )

[LaRetta]

Actually, this is better, I think

Let ( [
I = Get ( CalculationRepetitionNumber ) ;
values = List ( child:date )
] ;
Case ( i  ≤ ValueCount ( values )  and  i ≤ 14 ; GetValue ( values ; i ) )
)

That's what happens when I try to do something in a rush.  My apology.  :-)

[comment]

Why not use GetNthRecord(), with Get ( CalculationRepetitionNumber ) supplying the N part?

[David Jondreau]

It's been a couple years since I worked on this exact issue, but IIRC, GetNthRecord() and repeating fields don't play well with each other. I think it has to do with how FM evaluates Extend() vs GetNth(). List() may not work either actually.

 

I recall having to use a separate field just for the List() calculation and then Extend() that in the calc repetition.

[LaRetta]

I've tended to shy from GetNthRecord() because of usual speed issues (in calculations) as well as the behaviour you mention, David, which is that one must explicitly type cast if using with List() looking to related records and I haven't tested it recently either.  

 

Good thinking, Michael; no surprise there. So then this works: 

Let ( [
i = Get ( CalculationRepetitionNumber ) ;
Case ( i < 15 ; GetNthRecord ( Extend ( child::date )  ; i ) 
)

[comment]

It's been a couple years since I worked on this exact issue, but IIRC, GetNthRecord() and repeating fields don't play well with each other. I think it has to do with how FM evaluates Extend() vs GetNth(). List() may not work either actually.

 

The following works fine for me =

Let ( [
i = Get ( CalculationRepetitionNumber ) ;
n = Count ( Child::ParentID )
] ;
Case (
i ≤ n ;
GetNthRecord ( Extend ( Child::Value ) ; i )
)
)

--

P.S. It 's not like you to post such vague speculations. The FM community is already rife with all kinds of "traditions" passed from mouth to ear, all based on some unfortunate experiences of someone who couldn't make something work and blamed it on the tool rather than on lack of skill. Don't start another one.

 

BTW, LaRetta's calculation works perfectly well, too - so there's no problem with List() either.

 

 

 

 

 

 

 

one must explicitly type cast

 

??

[LaRetta]

Michael, the reason I included the < 15 was because it was a 14-column display.  By not short circuiting the calculation, and if there are many related records, won't we be evaluating records 15 through maybe thousands needlessly?

 

As for the type cast, I'll explain tomorrow ... I'm on my way out the door.  Good thread!

[comment]

By not short circuiting the calculation, and if there are many related records, won't we be evaluating records 15 through maybe thousands needlessly?

 

We'll be evaluating n (the count of related records) once. Then we'll be evaluating each repetition (not related record) to see if i ≤ n. Therefore the number of required evaluations in step 2 is equal to the number of repetitions, regardless of n being calculated in step 1 or hard-coded.

 

 

Moreover, if you hard-code 15 as the number of repetitions, and the count of related records is less than 15, you will have question marks appearing in all the remaining repetitions.

[Musoguy]

Thank you all for your help. Calculation field now works. However I am not quite sure how! I think I am not grasping Get ( CalculationRepetitionNumber ). I'm not quite sure how the repetitions are being set. If any of you could take a moment to explain it, I would be extremely grateful. I'm thrilled that the issue is solved in my solution, but I would love to understand it better in case I come across a similar issue in the future. Thank you all again for taking the time to reply

[comment]

I think I am not grasping Get ( CalculationRepetitionNumber ). I'm not quite sure how the repetitions are being set.

 

Have you looked at:

http://www.filemaker.com/13help/en/html/func_ref2.32.18.html#1028830

[Musoguy]

Thanks for the reply. I have. I guess I don't understand where in the formula it is moving to a new repetition to make Get ( CalculationRepetitionNumber ) find anything other than the first repetition. Honestly I think I am just confused!

[comment]

It's not "moving". Each repetition calculates its own result, using the common formula. If the formula includes Get ( CalculationRepetitionNumber ), then the first repetition substitutes it for 1, the second for 2, etc.. If the formula is nothing but =

Get ( CalculationRepetitionNumber )

then the result is consecutive numbering of repetitions.

 

--

If you prefer, you can look at it as an iterative calculation, with Get ( CalculationRepetitionNumber ) being the iterator.

[Musoguy]

Ah. Now it makes sense. Thank you

[LaRetta]

I was vague in my reference and I do apologise and it may not be the behaviour Devid mentions at all but it is one of the reasons I am careful about GetNthRecord's use.

 

 as well as the behaviour you mention, David, which is that one must explicitly type cast if using with List() looking to related records and I haven't tested it recently either.  

 

I have attached an fp7 file showing the behaviour I reference.  It is when using List() with GetNthRecord().  

 

Regular GetNth shows Apr 9 as the third child for record 1 but if used within List it shows the FIRST related of Apr 1.  Only when we wrap GetNth() with type cast (third example) does it work.  It doesn't matter the data type - all data types fail in List() and all data types work if type casted first.  IOW, even if text, we must wrap with GetAsText().

 

BTW, why are the calculations in this brand new file not switching to unstored automatically ... they reference child records.  

GetNth.fp7.zip

[LaRetta]

Or wait.  Is it because, as you said recently, List() is like an aggregate and wears many hats.  So when using in List() it can only ever see the FIRST related?  Similar to another mistake of mine where I wanted to sum value of current record or its children and I put

 

Sum ( thisRecord ; child::thatRecord ) ... which was wrong because it would only see the first related child to compare and instead I needed:

 

Sum ( thisRecord ; Sum ( child::thatRecord ) )

 

Anyway it was a behaviour I never had a chance to get back to explore so, since GetNth() can be slow, I just haven't reached for it recently.  :-)

 

But that would not explain why type casting the puppy will allow it to work however.

[comment]

It is when using List() with GetNthRecord().

 

Ah, yes, that can be a problem:

http://fmforums.com/forum/topic/89952-picking-the-3-best-grades-from-a-course-linkup-table/#entry412810

 

But this is not about type-casting as such; that is merely a trick to get Nth to evaluate before the List() function gets to it and gets confused. For example, adding 0 to the date does not type-cast it (it remains a date), but it still gets around the bug.

[LaRetta]

That makes more sense than the type cast itself ... inner parentheses will evaluate before outer List().  Well in 13 it is still there.  Somehow I missed that thread.  Dang; I hate it when that happens.  

 

Converting the file to 13, it updated the field definition to unstored but if I create the same calc in 13, it does not automatically make it unstored as it should. 

 

I also wanted to mention this:

 

http://forums.filemaker.com/posts/5e860ce8fd

[comment]

AFAIK, Filemaker does not force GetNthRecord() to be unstored. There is a vague note in the help suggesting that.

 

 

 

I also wanted to mention this:

 

http://forums.filemaker.com/posts/5e860ce8fd

 

[groan]