getnthrecord bugs out on me!

[Jonas Löfvenmark]

I am trying to define a calculation to check if the name field has changed from the next record, and add a counter if so like:

name order DESC

A 3

A 3

B 2

C 1

Case(Get(Postnumber)=Get(FoundPosts);1;Let([

this=name;

next=GetNthRecord(name;Get(Postnumber)+1);

value=GetNthRecord(order DESC;Get(Postnumber)+1)];



Case(Exact(this;next);value;value+1)))

(I've translated the get functions from swedish to english as I remember them - if wrong it's my bad)

The calculation above functions fine up till 173 records. Adding record 174 the calculation starts displaying "?". Sample file attached.

I have tried declaring variables as above, not declaring variables, defining as a function without any luck. Any tips on how to move forward is greatly welcome. Is there another way to accomplish the same result? Have never used statistics fields, but the name field will eventually be a calculation itself - have have just dumbed it down to understand what is going on.

Thanks!

Jonas.

test.zip

[Jonas Löfvenmark]

Funny how sometimes you just need to sit down and formulate a problem to understand the solution

Found what I believe to be the best solution by avoiding getnthrecord and instead using a value list.

By defining a value list for the Name column and using the valuelistfunction you get a set of unique set of names. Then find the position of the record's name in the list of names, like:

Let([uqNames= ¶&ValueListItems("myfile";"myvaluelist")&¶ ;

myname = name ;

mylist = Right(uqNames;Length(uqNames)-Position(uqNames;¶&myname&¶;0;1) )] ;

 PatternCount (  mylist ; ¶ )

)

[comment]

It works for me for more than 173 records - but that could be only a matter of available memory. What exactly is this supposed to accomplish?

By defining a value list for the Name column and using the valuelistfunction you get a set of unique set of names.

Note that a value list lists all values in the field - your previous attempt dealt with found set only. Still not sure what you are calculating here, though.

Edited February 3, 2012 by comment

[Jonas Löfvenmark]

Interesting - I get the same result on my client using mac, PC, and Filemaker Server windows with loads of memory!

I need this for exporting data to a webiste where items are sorted based on priority (higher values first) where as the data in filemaker is sorted ascending. Also the filemaker data has one record for each item and group where as the website needs groups separated from items with parent linking to group items.

Filemaker data

1 Computers -> Mac

2 Computers -> PC

Webiste data

1 Computers

2 Mac, parent 1

3 PC, parent 1

(yes, it is poorly structured filemaker data to begin with....)

Anyway - using getnthrecord on 400 category items with up to 4 levels and 4 languages was running kind of slow. With value lists is is warp-speed

Note that a value list lists all values in the field - your previous attempt dealt with found set only. Still not sure what you are calculating here, though.

Thanks for pointing out, valuelistitem function makes them unique though. And by relating each level of the FM category string to a new instance of the table i get the child categories items only.

[comment]

Well, yes: if you cascade GetNthRecord(), it will get slower and slower and at some point (stack overflow) it will stop evaluating. I thought that would be obvious. Practically any other method would be preferable, but I still don't get what the goal is.

[Jonas Löfvenmark]

the goal is transforming something like this:

id1: "Audiogear -> Microphones -> USB-Microphones"

id2: "Audiogear -> Wireless equipment -> In Ear-monitoring -> Receivers"

id3: "Audiogear -> Wireless equipment -> In Ear-monitoring -> Senders"

id4: "Guitars -> Instruments -> Electric Guitars"

etc. for 485 records times 4 languages

to:

id1: "Audiogear", parent: root

id2: "Microphones", parent: 1

id3: "USB-Microphones", parent: 2

id4: "Wireless equipment", parent: 1

id5: "In Ear-monitoring", parent: 4

id6: "Receivers", parent: 5

id7: "Senders", parent: 5

id8: "Guitars", parent: root

id9: "Instruments", parent: 8

id10: "Electric Guitars", parent: 9

By dividing the category string into 4 sets of calculated fields (one for name, one for id) we get the different names and ids for each level.

For the first record:

level1id: 1

level1name: "Audiogear"

level2id: 2

level2name: "Microphones"

level3id: 3

level3name: "USB-Microphones"

level4id: null

level4name: null

Then for each following post we need to check if each level name has changed from the previous post using getnthrecord. If so assign a new id, else use the previous records levelid and levelname.

So far fine enough. But when we want to add an (reversed) ordering parameter to the result, like:

id1: "Audiogear", parent: root, order: 2

...

id8: "Guitars", parent: root, order: 1

We need to find out how many unique items there are for each level - from the bottom and up. Using getnthrecord on level2, 3 and 4 works fine as they change a lot. But level1 started showing "?" half way through.

Anyway - using valuelists all is good and performance is top notch!

For the fun of it I have included the file for reference ;)

categories.fp7.zip