Newest functions Upload a function Leave a tip My account

There are 2,572 functions in this library.

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 IT Solutions Consulting, Inc http://www.itsolutions-inc.com

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

Let( [ /* You should point relatedkey to a self join relationship, using whatever key is appropriate. For example, you could use this in a line item table to determine the portal row number of a given line item from the parent record's context, by setting up a self join using the parent record foriegn key as the match field. Basically, we're counting the number of paragraph returns prior to key instance, and adding 1, since the paragraph return just after the key instance has been trimmed. */ $ItemList = List(relatedkey); $ItemPosition = Position($ItemList; key;1;1); $TrimmedTextToLeft = Left($ItemList;$ItemPosition); $Result = PatternCount($TrimmedTextToLeft;"¶")+1 ]; $Result ) Click to copy

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

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 21 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: