1 2 3 4 Previous Next 47 Replies Latest reply on Aug 8, 2012 2:49 PM by JasonFok

    Inserting Records



      Inserting Records


      Hi All,

      Does anyone know how to make it so filemaker inserts a new record after the active record. By default, filemaker adds the new record at the bottom of the page. I want to keep the database unsorted. I want the ability to add records freely wherever I need. 

        • 1. Re: Inserting Records

          If the database is unsorted, by definition (in how FileMaker works), newly added records will appear at the bottom of the current found set. "unsorted" records in FileMaker mean that they are actually sorted in origination order.

          You would need to specify a sort order that puts new records immediately after the current record and use a script that assigns a value to the new record to put it there.

          Say you define a number field and sort your records in ascending order by this number field.

          Add a button in the body of the layout for adding a new record with this script:

          Set Variable [$SortKey ; Value: Let ( [ Rn = Get ( RecordNumber ) ;
                                                                      Sk = YourTable::SortKey ] ;
                                                                      If ( Rn = get ( FoundCount ) ; Sk + 10 ; (Sk + GetNthRecord ( YourTable::SortKey ; Rn + 1 ) ) / 2 )
                                                                  ) ]
          New Record/Request
          Set Field [ YourTable::SortKey ; $SortKey ]

          This should work with Fmp 10, 11, and, if "keep records in sorted order" was specified when sorting the records, in FMP 12.

          • 2. Re: Inserting Records

            To add to this. I use filemaker for theatrical cue sheets. Here is an example.


            Cue 2

            Cue 4

            Cue 4.1

            Cue 4.2

            Cue 6

            Cue 4x

            So as you can see the cues are in numeric order. Cue 4x is different because it represents something that tracks until after cue 6. If i sort by a defined number field, then this cue sequence is not possible in filemaker correct? If I wanted to add Cue 3, I would need the new record after Cue 2 and before Cue 4. Thanks for all your help.

            • 3. Re: Inserting Records

              Hi Phil and Jason Hope you don't mind me butting in here for learning purposes.

              Phil, By "get (FoundSet)" do you mean Get(FoundCount). If so, I think the If function is saying "If you are on the last record of the found set, add 10 to the current sort key. Why 10?

              Also, for the second part of the If statement, I understand that is slotting the new sort key between two existing sort keys, but I think it means you will end up with fractions with multiple decimal places in your sort key. This is obviously ok I'm gathering. 


              • 4. Re: Inserting Records

                Hi Chris,

                Thank you for providing your insight. The point cues are perfectly fine. It's just how i personally organize and set up my cue sheets. The whole number represents the actual cue itself, for example a piece of music. The point cues represents changes in the musics level. The "X" cues represent when the music fades out. My concern was being able to insert records wherever I wanted and that Filemaker wouldn't sort them in numerical order even if I intentional placed a recrod with a lower number set value after a record with a higher number set value.

                Thank you,


                • 5. Re: Inserting Records

                  @Chris, I did mean Get ( FoundCount ) . Thanks for the catch on my error and yes, you do understand how that script would work.

                  @Jason, the script I posted could be modified to just increment the cue number by 0.1 instead of computing the midpoint between values for the current and next records. In cases where you need to, you can then change this number after clicking the button to add the new record:

                  Set Variable [$Cue ; Value: YourTable::Cue + 0.1 ]
                  New Record/Request
                  Set Field [ YourTable::Cue ; $Cue ]

                  • 6. Re: Inserting Records

                    Hi Jason

                    Once again I apologise for interrupting your interaction with Phil. I was (selfishly) just trying to understand his calculation. My understanding from his post is that if you want such a custom sort order for your records you will need to do it manually to some degree. He has suggested a way that at least minimises how much manual work you have to do - you just have to click on the record that you want to have immediately before the new record, and use his calculation to insert the new record. Otherwise, for every new record you would need to manually insert the sort key to place the record where you needed it. 

                    And no, I don't think there is a way that you can use your cue sequence directly as the sort field given it's requirements of needing to hold letters, and have some numbers appear before others (out of order)

                    Once again I'm sorry to insert myself. I think when Phil gets back in he will be of much more help.


                    • 7. Re: Inserting Records

                      @ Phil 

                      It gave me this error. Cue Creation is the table I am working with. Forgive my ignorance with filemaker I am definatly new at learning the syntax. I hope what I am trying to achieve with my cue sequence is clear. Let me know if I can clarify anything. 

                      • 8. Re: Inserting Records

                        In set variable, you enter the variable name: $Cue in the first box and the expression Cue Creation::Cue + 0.1 in the second. You do not type in the ; value: text anywhere. fileMaker adds that for you.

                        And Chris spotted a detail I missed, that an entry such as 4x has to sort to the end of the list. That will require some more work to get them to stay at the bottom of the found set.

                        • 9. Re: Inserting Records

                          How does filemaker handle that, keeping it at the bottom of the found set. Is it even possible? Here is some clarification. Sorry I am going to change numbers, but the idea is the same.


                          Cue 1 - Preshow Announcement

                          Cue 2 - Television underscore

                          Cue 2.1 - Television underscore UP

                          Cue 3 - Car Door Slam

                          Cue 4 - Doorbell

                          Cue 2.9 or 2x (doesnt matter its the same thing to me) - Television underscore OUT

                          • 10. Re: Inserting Records

                            Its giving me the same error as before

                            • 11. Re: Inserting Records

                              Delete everything from this calculation dialog except the variable, $Cue#   and click OK

                              Back at the script step "set Field", tick  "specify target field" and pick the Cue field you want to use (is it Cue#)?

                              Double check that earlier in your script you called the variable $Cue# and not just $Cue


                              • 12. Re: Inserting Records

                                So I attached a picture of the result I got when I ran the script. As you can see the +Cue 0.1 worked! Currently, the found set is "unsorted" The only problem with this result is that I want the Cue 1.1 to be after Cue 1 without having to sort. When i ran the script from the button it still placed it at the bottom even though the active record was Cue 1. 

                                • 13. Re: Inserting Records

                                  Ok so now to sort by the relevant field, add a sort step to your script

                                  sort records, no dialogue, sort ascending.

                                  you'll probably need to make sure the sort field is a number field.

                                  • 14. Re: Inserting Records

                                    @Chris   That worked! Having the Cue 0.1 inserts it after the active record which I then can change to another whole number. :D I dont know if you say Phil's last comment, but would you know how to keep records in their position regardless of sorting, essential lock them into position.


                                    Thank you both so much for all your help. 

                                    1 2 3 4 Previous Next