Sorting and parsing a relationship

[jshobar]

Hey all, I could use some help on this one. Have two tables related by a simple serial key. When table 1 relates to table 2 I get multiple matches for each key. I can see these matches in a portal just fine, and I can even sort the portal. My next task was to parse each one of these matches to it's own field in table one, using the calculation: GetNthRecord(); this works great except I want the parsed fields in table 1 (say Value1, Value2, Value3) to be in a sorted order. Sorting the portal doesn't help me, sorting the original table 2 doesn't help either. FM seems to use some internal key that matches the creation order of the records of table 2, so when using GetNthRecord() FM always thinks the same record is the 1st record. Any ideas on how to rectify this? Ideally I'd like to be able to sort table 2 by date and then parse all matches from the key in table one into Value1, 2, 3 in date order. Any help or ideas would be greatly appreciated.

[comment]

I don't know what benefit you expect from this process, but to get what you want you will need to sort the related records in the relationship's definition.

[jshobar]

Then end goal is to have Value1, 2, 3 in date order so I can export as a tab delimited file (the only type of file that FusionPro, a variable data design program can handle) I need each record in the tabbed delimited file to correspond to one recipient and to have values for coupons valid on certain days to be able to populate the artwork in order. When I define my relationship I tried having table two sorted according to the info field(the filed that will become Value field in table 1) but I get the same results. Can you be more specific in how I need to set up my relationship? Thanks again.

Edited January 21, 2008 by Guest

[comment]

In the relationship graph, open the relationship's definition and on the coupons' side check "Sort records". Then select the field to sort by - I am guessing that would be the date field.

Another way to do this would be exporting as XML from the coupons table, while using a style sheet to manage the delimiters. A little harder to achieve if you haven't done XML/XSLT before, but it elimimates all those redundant fields. Or you could use AppleScript to post-process the exported file - if this is for an all-Mac environment.

[jshobar]

comment, thanks for your thoughts and time. Clicking sort records in the definition of table 2 only work to sort the record when I view it through a (unsorted) portal. GetNthRecord however, still wants to go off of some sort of record creations date. With a sorted relation to table two, the portal displays exactly what I want, the parsed V1, 2, 3 does not. Processing the file after the fact seems problematic because I am unfamiliar with either XML/XSLT or Applescript (I am in an all mac environment). Any good starting points for either of those? Also, it seems like there should be a solution obtainable inside of FM. I'm pouring through books now, with no avail.

[comment]

I am quite sure GetNthRecord() follows the sort order defined for the relationship - so if you are seeing something else, you either did something wrong or didn't do something right. Are you sure you are sorting the records in the relationship's definition (i.e. within the relationship graph)?

[jshobar]

I am absolutely sure. I'm as surprised as you are, but GetNthRecord does not return a sorted result. I can send an example to you if you'd like.

[comment]

Please post your file here (best to use a copy stripped to bare essentials and no sensitive data).

[jshobar]

Here you go. If you look at the layout T1, you'll see the portal showing the sorted records from Table 2 (via sorting being checked in the relationship and not the portal) and below you'll see C1, 2, 3 being parsed as I described. Any ideas?

SortRelationship.zip

[comment]

Change your calculation fields to return a Text result. I think your file just got a little confused regarding its indexes, hopefully nothing more serious than that.

[comment]

Oops - and of course, make the calculation UNSTORED!!!

[jshobar]

Thanks! I feel stupid now. It always seems to be something simple and easy overlooked. Now it is behaving as I expected. Thanks again for your time on this issue. I'm new to the community, hopefully I can start giving back some answers soon. Thanks again.

[comment]

I didn't know you could get GetNthRecord() to be stored even though it references a related field. It should be documented in the Help, like Lookup() and LookupNext.

[David Jondreau]

I didn't know you could get GetNthRecord() to be stored even though it references a related field.

That's a new one for me too. Unfortunate.