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 )
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.
To add to this. I use filemaker for theatrical cue sheets. Here is an example.
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.
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.
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.
@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 ]
Set Field [ YourTable::Cue ; $Cue ]
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.
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.
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.
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
Its giving me the same error as before
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
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.
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.
@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.