Jump to content

Shaun Flisakowski’s GetNthRecord


This topic is 2597 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

 

 

 

 

 

 

Link to comment
Share on other sites

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
 

Link to comment
Share on other sites

 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.

  • Like 1
Link to comment
Share on other sites

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".

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 2597 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Similar Content

    • By HOnza
      I needed to decode HTML encoded text in FileMaker. I thought this must have been done many times before, so I tried to search for a suitable custom function at BrianDunning.com and FMFunctions.com. After checking few functions I found one that seemed pretty good...
      Read the whole article
    • By HOnza
      Two weeks ago I wrote an article about a FileMaker custom function I needed to preprocess some data imported from the web. I used this custom function in an auto-enter calculation to immediately preprocess the data while being imported. I created a server-side script that does the import every morning. But when I discovered that the import was taking over an hour every day, I saw it deserves some optimization. I used FM Bench Detective and optimized the custom function to evaluate up to several hundreds times faster…
      Read the whole article...
    • By HOnza
      Last September I wrote an article about a custom function that I optimized to evaluate hundreds times faster. At the end of the article, I challenged my readers and myself by claiming that the already optimized custom function can be optimized even further. Do you remember?
      Later on I actually really optimized it again, and talked about this optimization during my session at Pause On Error [x] London 2011. Now you can watch the video of this part of my session below:
      Read more and download my updated sample file at honza.24uSoftware.com.
    • By HOnza
      As a Claris partner, we also resell FileMaker licenses. Buying the license from us has some extra benefits, one of them being a nicely formatted PDF document with all information about the license, including download links. In order to be able to generate this document from our FileMaker based CRM, I needed to find a way to include functional web links in it. So I wrote a custom function that does it, without needing any plug-ins or other external technologies.

      The custom function simply takes a PDF saved from FileMaker, and adds a link to it. As long as you know what page and where on that page you want the link to be, you can modify your PDF that easily.

      No plug-ins are needed, no web viewer, so it works in the whole platform, including WebDirect and server-side scripts.
      Click here to get the example and read the story behind it.
    • By Richard Carlton
      For this Coaches’ Corner tip, Kristian Olsen demos a simple technique for creating a popup menu in which you can make multiple selections. Very useful!
      Grab the sample file here:
      http://bit.ly/2qTSttq
      FileMaker Coaches' Corner - Tip 4 - Editable Popup Menu - Custom Function
      https://youtu.be/HGw_nkhU58A
      www.fmcoachescorner.com
      Most Recent Upload https://goo.gl/Dbn9fm
      Get up to speed with the FileMaker Pro 16 Video Training Course!
      Top Rated Course by FileMaker Expert, Richard Carlton.
      http://learningfilemaker.com/fmpro16.php
      Experience Richard's dynamic and exciting teaching format, while learning both basic, intermediate, and advanced FileMaker development skills. With 26 years of FileMaker experience and a long time speaker at FileMaker's Developer Conference, Richard will teach you all the ins and outs of building FileMaker Solutions.
      The course is 50 hours of video content!
      Richard has been involved with the FileMaker platform since 1990 and has grown RCC into one of the largest top tier FileMaker consultancies worldwide.
      Richard works closely with RCC's staff: a team of 28 FileMaker developers and supporting web designers.
      He has offices in California, Nevada, and Texas.
      Richard has been a frequent speaker at the FileMaker Developers Conference on a variety of topics involving FileMaker for Startups and Entrepreneurs, and client-server integration.
      Richard is the Product Manager for FM Starting Point, the popular and most downloaded free FileMaker CRM Starter Solution.
      Looking for FM Starting Point free software download: http://www.fmstartingpoint.com
      Richard won 2015 Excellence Award from FileMaker Inc (Apple Inc) for outstanding video and product creation, leading to business development.
      RCC, Filemaker Videos, and LearningFileMaker.com are headquartered in Santa Clara, CA.
      http://www.rcconsulting.com/
      Please feel free to contact us at support@rcconsulting.com
      FileMaker Pro is simply a powerful software used to create custom apps that work seamlessly across iPad, iPhone, Windows, Mac, and the web
      Transform your business with the FileMaker Platform
      Free FileMaker Training Videos Channel https://www.youtube.com/user/FileMakerVideos
      50 Hour FileMaker Pro 16 Video Training Course-FileMaker 16 News-Online FileMaker 16 Training Videos
      https://www.youtube.com/watch?v=KpQqLLDcZ8I
      Playlist https://www.youtube.com/watch?v=KpQqLLDcZ8I&list=PLjTvUZtwtgBTMCfjM6LLwBAwGf_yXfvd_&index=13
      Top 10 New Features in FileMaker 16-FileMaker 16 News-FileMaker 16 Instructional Videos-FileMaker 16
      https://www.youtube.com/watch?v=urh8iHOCxkg
      Playlist https://www.youtube.com/watch?v=urh8iHOCxkg&t=130s&index=1&list=PLjTvUZtwtgBTMCfjM6LLwBAwGf_yXfvd_
      Sharing your Database with Other Devices and Users-FileMaker 16 News-FileMaker 16 Database Sharing
      https://www.youtube.com/watch?v=GF82vkYtCtA
      Playlist https://www.youtube.com/watch?v=GF82vkYtCtA&index=8&list=PLjTvUZtwtgBTMCfjM6LLwBAwGf_yXfvd_
      Introduction to FileMaker WebDirect 16-FileMaker 16 News-Online FileMaker 16 Training Videos
      https://www.youtube.com/watch?v=uaZKIpBjMAM
      Playlist https://www.youtube.com/watch?v=uaZKIpBjMAM&list=PLjTvUZtwtgBSVV1-4pFG4SHAhCIP3Yy-I&index=17&t=10s
      A database management system (DBMS) is a computer software application that interacts with the user, other applications, and the database itself to capture and analyze data
      Official site provides the SDK, Developer's Guide, Reference, and Android Market for the open source project
      http://learningfilemaker.com/FIAS.html
      https://sites.google.com/site/filemakerprotrainingd2n/home/filemaker-training
      https://drive.google.com/file/d/0ByHcthu5EL9NSmowQm9OUDdZZ1k/view?usp=sharing
      Free FileMaker videos check out ...http://www.filemakervideos.com
      Download the FileMaker Pro 16 & FileMaker GO 16 for mobile devices training videos at http://www.learningfilemaker.com
      Download FileMaker Go 16 video training at http://learningfilemaker.com/FMGO-16/fmgo16.php
      Download FileMaker 16 Full Video Training Bundle at http://learningfilemaker.com/subscription.php
      FileMaker Video Training Review-FileMaker 16 Video Course Review-FileMaker Pro 16 101 Course Review
      https://www.youtube.com/watch?v=mF6Uor0KmKo
      Playlist https://www.youtube.com/watch?v=mF6Uor0KmKo&list=PLjTvUZtwtgBT8tNHuzF6cOKC_37zCTQl6&index=18
      Use FileMaker to create an app with the FileMaker Training Series
      FileMaker Pro is a cross-platform relational database application from FileMaker Inc.
      Comment, Like & Share Our Videos.
      Feel Free to Embed any of Our Videos on Your Blog or Website.
      Follow Us on Your Favorite Social Media
      Facebook
      Twitter
      Google+
      #FileMakerCoachesCorner
      #FileMakerPro16Training
      #FileMakerPro16Videos
      https://www.youtube.com/watch?v=4ozLZaRUgZ0&feature=youtu.be
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.