Shaun Flisakowski’s GetNthRecord

[Zippy Aus]

Hi all

 

I think I have this custom function in every database I use.

 

Case ( Start < Number ;
               GetNthRecord ( field ; Start )
                    & ¶ & AppendNth ( field; Start + 1 ; Number );
          Start = Number ;
               GetNthRecord ( field ; Start )
         )

 

 

It is just standard practice for me to use it. 

 

I mostly use it to concatenate data from Portals into a single field for display.

 

As it only concatenates one field, I usually create a calc of all the fields I want to display and then use

 

AppendNth ( relate::field ; 1 ; Count ( relate::recid ) )

 

Is there a better way?  It just occurred to me this afternoon, that I just automatically use the FM7 custom function.

 

Is there an inbuilt function that I have overlooked?

 

I am sure I could achieve this with ExecuteSQL too.

 

Thanks in advance.

 

ZP

 

 

 

 

 

 

[comment]

I am guessing this custom function predates the List() function introduced in version 8.5.

[Wim Decorte]

or the ExecuteSQL() function; using this you can avoid any and all intermediate calculations.

[Zippy Aus]

The one good use for this function, which we used today was to list the foundset .. I don't think I can do that with the List function.

 

I have started to use the ExecuteSQL function for the related records now.  A little off topic but can you ORDER BY a value list in ExecuteSQL.

 

Thanks for responding.

 

ZP
 

[comment]

 to list the foundset .. I don't think I can do that with the List function.

 

If you are using version 13 (please update your profile), you now have a summary field for that.

 

Incidentally, I am puzzled why would you prefer a text field for displaying your records over a portal or a list view - where records and fields can be individually selected, clicked, formatted and whatnot.

[Zippy Aus]

It is mostly for merging to Word or displaying in a webviewer with other data from the systems.

[comment]

for merging to Word

 

How so?

[Wim Decorte]

 A little off topic but can you ORDER BY a value list in ExecuteSQL.

 

 

Sure.  If you want to get up to speed quickly, read this:

http://filemakerhacks.com/2012/10/19/the-missing-fm-12-executesql-reference/

[bruceR]

Where does that describe ordering by value list? Seems to describe only standard order by operations.

[David Jondreau]

You could create a new function that's similar to the old, passing a second field as an additional parameter.

 

Case ( Start < Number ;
               GetNthRecord ( field1 ; Start ) & " " &    GetNthRecord ( field2 ; Start )
                    & ¶ & AppendNth ( field; Start + 1 ; Number );
          Start = Number ;
               GetNthRecord ( field ; Start )
         )

 

Personally, I try really hard to avoid adding fields.

Where does that describe ordering by value list? Seems to describe only standard order by operations.

 

I think the OP meant one can "ORDER BY a *list of values* in ExecuteSQL".

[Zippy Aus]

Thanks David.  That looks good.

How so?

With regards to merging.  If for example, I have a related table of fees for a product, I will append them as one field and export that for the merge.  If I export the related fields, I can insert the single merge field.

[comment]

With regards to merging. If for example, I have a related table of fees for a product, I will append them as one field and export that for the merge. If I export the related fields, I can insert the single merge field.

I am not sure I understand the advantage here. If you have multiple related records, and you export them as a list, you will end up with vertical tab character as the delimiter - does Word know how to handle this?

My policy is to never add anything to the solution for the sole purpose of export; I farm it all out to XML/XSLT.