8 Replies Latest reply on Jan 22, 2013 10:56 AM by Mike_Mitchell

    Get Related Record Number?

    flybynight

      I'm looking for a way to get the position (number) of a realted record, within the set of related records to one parent record.

      For instance, line items on an invoice - I want to be able to have a field that says which line that particular line item is.

      The relationship is set up to sort by line item ID number, so it will always be in creation order.

      I can accomplish this with an auto-enter calculation of Get (RecordNumber). However, if one of the line items is deleted, it does not re-calculate. For instance, if there were 5 line items, but then line item #3 was deleted, #4 should become #3, and #5 should become #4.

      I don't want to make it just a calculation field, because sometimes we will perform a search that will return different line items from multiple parent records, and I don't want it to change the Record Number based on that found set.

       

      I am making a concantenated field that combines the Invoice Number with the line item number. So, like item #2 of Invoice # 1234 will have a LineItemNumber of 1234-2.

       

      I'm sure I'm missing something and this is much easier than I am making it out to be… ? Or at least I hope so.

       

      Thanks for any help!

      -Shawn

        • 1. Re: Get Related Record Number?
          Mike_Mitchell

          Shawn -

           

          Have a look at the GetNthRecord ( ) function. Might be what you need.

           

          Mike

          • 2. Re: Get Related Record Number?
            flybynight

            Mike -

            I don't think that will do it. GetNthRecord will return the contents of a field in a record number that I specify in the parameters. I don't want to tell it which record number. I want it to tell me which record number each one is, as it relates to the parent record.

            -Shawn

            • 3. Re: Get Related Record Number?
              Mike_Mitchell

              What about:

               

              1) Go To Related Record.

              2) Sort on Record Number

              3) Loop over records, using Set Field to incrementing ID. (trap for record collision in case someone's editing a record at that time)

               

              Execute based on a Script Trigger or whenever you need to ensure the IDs are updated. You could use Replace, but it's a lot harder to trap for errors that way.

               

              Mike

              • 4. Re: Get Related Record Number?
                usbc

                Shawn - You don't really say what exactly you will do with the result. But if it is just a visual cue for printing etc, the answer could be as simple as adding two @ symbols in the portal row.  @@

                • 5. Re: Get Related Record Number?
                  flybynight

                  usbc -

                  It needs to be a part of the record. That way, if someone is searching through line items and looking at a list of them from multiple invoices, they will see that LineItemNumber 1234-2 and they would be able to go find the physical invoice file and know where to look for that item. Also, some digital files are filed on the server by that item number and could be looked up in a similar fashion.

                  It's a matter of taking an existing convention, done on paper, and re-creating it in FMP to bring them into the 20th century. Trying not to rock the boat too much, and keep what I can familiar. You know how people are with change…  

                  -Shawn

                  • 6. Re: Get Related Record Number?
                    flybynight

                    Mike -

                    I think that could work. I'll have to play with it. Since what I have now works while creating the related records (line items), I think the only circumstance that I would need to reset the numbers would be if/when something is deleted. That could just be part of my delete LineItem script.

                    I was just hoping there might be a function or calculation that I didn't know about, that would return the related record number.

                    Any suggestions for how to trap for record collision? I think it would be pretty rare that multiple people are in the same record, but better safe, right?

                    Thanks!

                    -Shawn

                    1 of 1 people found this helpful
                    • 7. Re: Get Related Record Number?
                      usbc

                      Understood. Then I think you are down to using privileges to  restrict the deletion of a lineitem to be done only via a script.

                      Then have that same script re-number them.

                      • 8. Re: Get Related Record Number?
                        Mike_Mitchell

                        Shawn -

                         

                        Before you use the Set Field command, make sure the error capture is turned on (Set Error Capture [ On ]). Then, when the Set Field is executed, FileMaker will return an error if someone else has the record locked (I believe it's error code 301). It doesn't really matter what the error code is too much; if you get an error, it means your record didn't get updated. In such a case, you can handle it in a variety of ways:

                         

                        Perhaps you store the actual record ID (the key field) in one variable (I suggest a return-delimited list) and the value you tried to store in another. Then you can repeat the loop later until you don't get an error. This method and the first are a little fragile; any interruption in the script and you're left hanging (and you're creating opportunities for failure because you're deliberately waiting).

                         

                        Or, alternatively, you can store the erroring records (along with the desired values) in another table somewhere (one that only the database has access to) and run a batch script on some frequency to correct the issue.

                         

                        Depending on how involved you are personally, you can also write the error to a log and come back later to fix it yourself. Depends on your workflow, how many users you have, how often you try this exercise, etc.

                         

                        Mike