AnsweredAssumed Answered

Get Nth Record not working as expected

Question asked by fmtuner on Sep 19, 2017
Latest reply on Sep 19, 2017 by user19752

I have two tables, parent "CLTS" and child "clts_REFR".  I am trying to draw an excel report of data from the CLTS table, and include data of up to 5 records from clts_REFR.  Each record's data, per the client, needs to be in a single column, as [Name]: [Number].  So I built this formula for report fields 1-5 (Ref1, Ref2, etc), with the number of the field indicated as [num] below:

 

Substitute (

  List (

  GetNthRecord ( clts_REFR::RefName ; [num] ) ;

  GetNthRecord ( clts_REFR::RefNum ; [num] )

  ) ;

  "¶" ;

  ": "

    )

 

The odd thing is, this returns values from the first clts_REFR record ("Bob Vance:  25") no matter what record number is indicated in [num],  however if [num] is greater than the number of related records in clts_REFR, it will return "?: ?".

 

This works just fine:

 

GetNthRecord ( clts_REFR::RefName ; [num] ) & ": " & GetNthRecord ( clts_REFR::RefNum ; [num] )

 

So while it's working,  I cannot figure out why the first option doesn't work.  I feel like I'm missing something fundamental here.  Any wisdom?

Outcomes