Brian Dunning's FileMaker Custom Functions

Get_Row_Number ( relatedkey ; key )

Return the portal row number of a matching key in a self join relationship

  Average rating: 4.4 (34 votes) Log in to vote

Colin Keefe   Colin Keefe
IT Solutions Consulting, Inc
http://www.itsolutions-inc.com

Share on Facebook Share on Twitter

  Sample input:
Get_Row_Number ( ItemsSJ::PrimaryID , PrimaryID )
  Sample output:
3

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

When using this function with a self join relationship, it will return the number of the first row in that relationship that matches the key you're searching for.

I use it when I need to determine, from the context of a child table, what portal row that child record is seen as from the context of its parent. I want to know that the child Invoice Line Item record is portal row 3 in the parent Invoice record, for example.

To work properly for this purpose, set up a new Table Occurence and create a relationship between it and your original table. For matching keys, use your parent ID foriegn key. The relationship should now show, from the context of the child table, all child records tied to that child record's parent invoice.

There's probably a more elegant way to do this. I'm just counting the paragraph marks in a returned List() left of the matching key, and adding 1.

 

Comments

Michele Olson   Michele Olson, Grand Rapids, MN
Jul 20, 2011
Colin,

Thanks much for the custom function. It was exactly what I was looking for.

Best regards,

Michele
 

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Under construction. Email me your wish list for improvements.