GetNthRecord help

[KateJ]

Hi all,

First, I'm mostly a lurker, but I come here and very often find others' posts that I can glean helpful hints from. Thanks everyone who partipates here.

Here's my problem for today:

I am currently using a calc (with several repetitions defined) as follows:

GetNthRecord (Extend(table::field); Get ( CalculationRepetitionNumber ) )

and it's working beautifully to turn records (rows) into column headings. But, the clincher is that I need it to be conditional. In other words, I only want to pickup the "nth" record if (and only if) a certain status field is equal to "Y". If the status is not equal to "Y", skip that record (don't make a column out of that data) and move on to give me the next record as my next column head to the right.

I am thinking that something recursive might be necessary here, but I'm afraid that's something I'm not very good at.

Any help would be greatly appreciated.

Kate

[Raybaudi]

Hi

isn't GetNthRecord () a FMP8 function ?

[KateJ]

Sorry, just trying to update my profile now. I'm using 8 Advanced, so custom functions are a possibility. Thanks!

[John Mark Osborne]

I'm not sure I understand exactly what you want so maybe you can take a look at this example and tell if you need it to work differently. Basically, it uses a recursive custom function to look at all the records in the found set but only gather the field value from the records that have a "Y" on them. Let me know what should be different and I'll see if I can help.

GetNth.fp7.zip

[KateJ]

Thanks so much John Mark, for such a quick reply! Here's a bit more detail:

The solution I'm creating is more or less a project management system for a publishing company. So each project (or book/ISBN) has several tasks related to it. Each task has a due date. This data is held in the table called Tasks. This table has the following important fields: 1) task description, 2) short task description, 3) due date, and 4) track flag. I need to go to the parent book record and pull the data in the short task description field of all the related task records that have "Y" in the track flag field into COLUMNS along the top of a layout.

So, by way of example:

For book title = New Book

Task Desc|Short Task|Due Date|Track Flag

Deliver Manuscript|Deliver|5/23/2006|Y

Layout Pages|Pages|5/30/2006|Y

Create Invoice|Invoice|5/30/2006|N

We would thus end up with a report with one ROW (representing New Book; base table for the layout is the ISBN/book table) and TWO column headers in the header part. The two column headers would be Deliver and Pages (Invoice would be suppressed because its Track Flag = N). The body part would show 5/23/2006 under the Deliver column and 5/30/2006 under the Pages column. (Once I get the column head calc set up, I can then work with that to set up the calc the shows the actual dates in the body). I REALLY want to be able to do this with a repeating calc rather than having individual fields as we're talking about up to 36 columns on the report.

Again, see my original post for the calc I'm using that is working beautifully EXCEPT that it, of course, is picking up EVERY task record, and not just the ones that have Track Flag= Y. So, I know I'm close...I can almost feel it... :

Hope this helps!!

Best,

Kate

Edited May 24, 2006 by Guest

[KateJ]

Well, and just to be a real pain... :

I also would like to fetch the nth records in a certain order (not the creation order, which I believe is the default). The task table also has a "sequence" field which is used to sort the records. This is necessary because a project will often have new tasks added after the fact which need to be slotted in (time-wise) between two tasks created earlier. And thus the columns need to reflect this same sort/sequence order.

[comment]

GetNthRecord () does not accept a condition. However, you can filter your relationship so that only records with the right flag are related. Either add such criterion to your relationship, or define a new one for this purpose.

[comment]

Same thing: sort your relationship. GetNthRecord ()will follow.

[KateJ]

Good morning! Well, of course...just needed that little bit of clarity. It's working wonderfully now. Thanks so much.

[HALBURN]

Your sample is almost exactly what I am looking but I need the result to be a repeating field. Is this possible?