1 2 Previous Next 16 Replies Latest reply on Oct 10, 2013 8:49 AM by coherentkris

    Get(RecordNumber) across relationship


      Is it possible to get the record number of the first child record from the context of a parent record for use in a script when their is no portal showing the child records?

      If possible then how?



        • 1. Re: Get(RecordNumber) across relationship



          The value returned is determined by the relative place of the record in the found set, and it changes depending on the find criteria and the sort order.


          That function is only relative to your current found set, not related records. It is designed to find where you are, location-wise, in the current found set.


          Your related records, regardless of portal or not, do not have any context to use get(recordnumber). From what you describe for your usage, your result would always be 1 (for the first record), which doesn't seem useful.


          What are you trying to do?

          • 2. Re: Get(RecordNumber) across relationship

            This might be something I would solve with an ExecuteSQL call. 

            • 3. Re: Get(RecordNumber) across relationship


              I am trying to remove a find section of a navigation script without changing to GTRR by using go to record number. My script would... get the target child record number (if any then first child record number), land on the appropariate child context layout, and go to record number.



              I tried several permutations of ExecuteSQL and could not retrieve a value other than 1 from a Get(RecordNumber) calculated field in the child table. If you have any ideas i would love to hear details.

              • 4. Re: Get(RecordNumber) across relationship

                Do you want the RecordNumber or the Record ID of the child table?


                If you want the first record ID of the child I would do this: GetValue ( List ( childTable::primarykey ) ; 1 )

                • 5. Re: Get(RecordNumber) across relationship

                  Record number



                  Sent from my iPhone

                  • 6. Re: Get(RecordNumber) across relationship

                    Ok, so are you basing the sort order of the child table on the creation date/time?

                    Because it's true what Mike said about RecordNumber depending on the found set and sort order.


                    Then you could potentially use ExecuteSQL to sort the related child table into a list, and then get the first value.


                    But I am also curious of what you are trying to do.

                    • 7. Re: Get(RecordNumber) across relationship

                      Kris --


                      If what you want is to allow a user to go to the set child records (without clicking on a GTRR button on a specific portal row) and land on a particular record there are a couple of ways to go about this.


                      If you use a simple GTRR step, you will end up on the 'first' related record.  If you would have had a sort in your portal that is different from the sort in your relationship, you won't end up on the record you desire.


                      1) You can still use your original GTRR from parent to child, followed by a sort step and a Go To Record (First) step.  If you expand upon this by capturing the ID of the child record you want to land on before you leave the parent context, you can add a loop with Go To Record (next) until you land on the record with that ID number.  Not elegant, but it works.


                      2) The List () function can return a return-delimited list of values from the child records, so folks usually use the child record's unique ID.  But, List() uses the Relationship and not any Portal Sort to get those IDs, so the order may still be wrong. (e.g. IDs are 1,2,3,4,5 but item descriptions are pear, apple, coconut, banana, raspberry and you want to get to apple)


                      You can do some tricks with either calculated fields in the child records or custom functions to put together a list of values that you can sort into some order (with custom functions) For example: pear_1, apple_2, coconut_3, banana_4, raspberry_5   becomes apple_2, banana_4, coconut_3, pear_1, raspberry_5) and then get the new list of record IDs (2,4,3,1,5) and use that in a relationship with a global field on one side that is NOT sorted and then use GTRR.




                      I've used both methods, and they both work.  Neither uses the Get (RecordNumber), since that is an unstable value.  (Although if you really want to try, you can use it in a Go To Record (Specify by Number) step right after your GTRR step and see if that works for you.)


                      Remember also that FileMaker's (relatively) new feature of maintaining the sorted order for records can be a help or a hindrance depending on what you are trying to display to the users.


                      I can probably come up with a couple more ways to go from a parent to child records (probably displayed  in list view) that have a particular sorted order and you are sent to a specific child that is not necessarily the first.


                      Let us know a little more about what you are trying to achieve, and we can give better direction.


                      -- Drew Tenenholz

                      • 8. Re: Get(RecordNumber) across relationship



                        There are so many things undefined here that I know I can't provide an answer.


                        1. How are you determining the first related record?

                        2. Is it a sorted relationship?

                        3. Since you don't have a portal how is the "user" designating the correct child record? Is it using a Filtered Relationship?

                        4. Does the "appropariate child context layout" have a found set?

                        5. Is the final display supposed to be all records with the 'desired' child record highlighted or the single desired record?

                        6. Why are you against using the GTRR?


                        You say you are trying to remove a "Find" section from a Navigation script. Does this mean you already have something that works? If so what is it doing? Why do you want to remove it? Maybe we can come up with a way to achieve the same results if we know more about what you want.

                        • 9. Re: Get(RecordNumber) across relationship



                          "Record Number" only has meaning in the context of a found set. So the "first" record number accross a relationship is a little confusing. The "first" record linked to a parent is the first one that would show up in a portal, if there were a portal. If the relationship isn't sorted, then it would be the first related record by creation order. If the relationship is sorted in the graph, it would be the first one based upon the sort.


                          You mention in your first post that you want the FIRST record, if that's the case, the "record number" of the first record is, well, 1, in the context of the found set of records.


                          But in your second post it sounds like you want to navigate to that specific record only. You could do this easily by getting the record ID across the relationship (which will the ID of the only the first record as defined by the relationship), and then use this result to do a find on that record ID in the child table.


                          Hope this helps,

                          Bob Gossom

                          • 10. Re: Get(RecordNumber) across relationship

                            Hi Kris,


                            Have you tried putting a summery field in the child table? Then when your scripts runs on the parent table, use the related child "summery field" to see the 'Record Number'. 


                            This might be possible (or not) through the summery field in the child table.



                            • 11. Re: Get(RecordNumber) across relationship



                              against GTRR for this because the navigation script is abstracted so that you put in params and can go to any context in the system. GTRR script step is dependent on destination TO context. Yes we have something that works but enacting a scripted find destroys and user generated found set and sort order that cant be easily reconstructed. Go to record number by calc would be perfect if Get record number was not dependent on local context

                              • 12. Re: Get(RecordNumber) across relationship

                                If horses had nickels...or something like that.


                                It sounds like you want to create a abstracted script to navigate from one record in a table to the found set of related table and then navigate to a specific record. That's certainly doable. Trying to use Get ( RecordNumber ) isn't going to work though. Instead you need to use script parameters on your button.


                                You need to pass two parameters:


                                1) The full name of the child Primary Key.  If the TO of the final layout is different from the TO of the portal, you'll need that field name too.

                                2) The layout name you want to end on

                                3) If you've got a sort order on your portal and you want the found set to be sorted the same way, then some flag of which sort order you want that will then hard code that with If, Else If script steps


                                Good luck.


                                • 13. Re: Get(RecordNumber) across relationship


                                  I am already passing in any possible parameters to the script.. the sticking point is when I want to navigate to a specific related record. In that case I am passing in field name, field value, and layout name. The only way I can seem to get it to work is go to layout enter find mode and find the field value in the field name.



                                  I appreciate the thoughtful comments thusfar. This is proving to be a valuable conversation.

                                  • 14. Re: Get(RecordNumber) across relationship

                                    You could go to the layout with the child records and, if you don't want to do a find, you can search through it by looping through and comparing the record number or whatever you are looking for and exit the loop when you find the matching one. 


                                    I still am very fuzzy on what you are trying to do. 

                                    1 2 Previous Next