Record Inserting

[mKirk]

Still learning here...but it's not like I didn't try to find the answer to this elsewhere, so thanks in advance for helping with a "101" question...which is...

How can I insert a record in a specific place? Meaning, say I have 10 records and I'm viewing Record5. Now I want to insert a new record right there so it becomes the new Record6, and the other record numbers (which I'm serializing and assuming I can use Get ( RecordNumber )) adjust accordingly.

Thanks again for any assistance or suggestions or FM Manual page numbers, etc.

[comment]

Why would this ever be necessary? FMP is not a spreadsheet application. There is no particular order of records. You sort them anew every time you view them.

[PeterHW]

It doesn't work like that. Like comment said, FM is not a spreadsheet. If you create a record, it automatically becomes the "last" one but the order in which they are created is (usually) totally insignificant. Depending on how you sort your records you can then put them in any order you wish.

You say you wish to insert a record, and then have the other records automatically adjust their ID number. This would actually be catastrophic to any relationships you have with or from other tables that rely on the (unique) ID of those records.

That being said, if you simply want a newly added record to appear below the current one (e.g. in list view), first sort the records, select the record after which you wish the new record to appear, and then press CTRL+N. The new record will then be placed below the record that was active when the "Create New Record" command was given, creating a semi-sort. But this is only for appearance, as soon as the records are unsorted, the new record will appear as the last in the list again.

I think you should let go of the idea that the actual order in which you create the records and the sequence in which you want them to appear are not the same thing. Simply put, create the records in any order you wish, and then sort them to get the sequence you want.

Regards,

Peter

[mKirk]

Okay, let me be more specific. I do understand the basic concepts that you both are discussing...perhaps where my knowledge is lacking is with regards to sorting. I'm building a little testing solution. I have two layouts for questions, one for True/False, and the other for Multiple Choice. The "builder" of the test can create a test using T/F or M/C in any order they wish (e.g. Question #1 T/F, #2 M/C, #3 M/C, #4 T/F...).

When a student takes the test, I have a script that "looks ahead" to determine if the next question is T/F or M/C and based on that info displays the appropriate layout. Works fine.

In making the "admin" side of this more robust, I thought it would be nice for the builder to be able to go back into a created test and edit and/or delete questions, as well as insert new questions. I figured out the edit and deleting functions. It was the insert function where I got stumpted, hence my question.

Let's say the builder wanted to add a new question "in between" question #5 and #6 (thus making the new question #6 and the old #6 becomes #7 and so on). That's why I wanted to be able to "insert" a new record as opposed to it adding on to the "end" of the record set.

Now perhaps I'm thinking about this all wrong...which wouldn't be the first time. But this is how I have it set up at the moment. I will look into Sorting a bit more closely to see if there is a way to track this that way. But if anyone has any other suggestions, etc. it would be appreciated. Thanks!

[comment]

How did question #5 become #5 in the first place? Suppose there are two tests, each with its own questions. What is the "internal" numbering mechanism?

I am asking, because there are several ways to solve this and much depends on nuances of workflow (how this is going to be presented to the user/s).

In general, to "insert" a new record after record #5, you would create a new record and asign #5 to the "internal" number. The serialID is whatever it happens to be. Then sort the records.

The sort order is first by the "internal" number, then by serialID. Thus the first-created #5 comes before the new #5. All that is left is to renumber the records, either by Replace Field Contents.. (single-user only) or looping.

[mKirk]

In what I have going, #5 becomes #5 on creation of a new record using the Auto-enter serial function.

[Zero Tolerence]

There is no 'real' way to do what your asking in FileMaker. I would highly suggest that you figured out a way to sort that would satisfy the users.

Even if its something as simple as having them put in a number for each question. ((1, 2, 3, 4, 5, 6) So that your last record in your table could be record 6, but it has a 1 in the specified field) and then sorting by that field.

[mKirk]

It's funny that you should mention that...it's what I'm trying right now in fact. I know I can create a "work around"...but thought perhaps there was a more elegant way of doing it that my limited experience was missing. :o

[comment]

#5 becomes #5 on creation of a new record using the Auto-enter serial function.

So there is only a single test that all the questions belong to?

[mKirk]

Well, it WAS Auto-enter serial...but I changed it around and have it working. Yes, all the questions to belong to one test, btw. Now don't laugh...but here is what I did to get it to work:

Copy [ Select; testBuilder::NextNum ]

Paste [ Select; testBuilder::GoToQuestion ]

New Record/Request

Paste [ Select; testBuilder::Question ]

Loop

Go to Record/Request/Page [ Next; Exit after last ]

Set Field [ testBuilder::Question; testBuilder::Question + 1 ]

End Loop

Sort Records [ Restore; No dialog ]

Go to Record/Request/Page [ No dialog; testBuilder::GoToQuestion ]

I just set the first question number to "1". NextNum simply adds one number to the previous question number to determine the next question number, etc. I also paste the value of NextNum into the global "GoToQuestion", that way I can "jump back" to the new "inserted" question. The end result to the user makes it appear as if a question (Record) has been inserted at a specific point in a series of questions.

I have no idea if this makes any sense to anyone else...or could even be helpful (no doubt there is a more elegant way of doing the same thing)...but there it is.

Thanks for all the assistance.