10 Replies Latest reply on Feb 15, 2012 4:33 PM by philmodjunk

    Creating Temporary Records



      Creating Temporary Records


      I have a need to copy portions of records to a similar temporary table, manipulate them then use the revised records to create link tables, etc.  I currently open a Layout linked to the primary table, create a found set, open a Layout linked to the temp table, import the found set into the temp table.  This works fine and is pretty fast.  Eventually, this app will have a web-based version.  It is my understanding that the Imports will not work in the web version.  Additionally, I have had issues with the Import on FileMaker Go when the FP7 file is resident on the iPad.  It works fine when it is shared by FMP 11 sharing.

      I have been able to accomplish the same thing by opening the primary table layout, creating the found set, looping through the records while capturing needed data field into $$Temp_Variables, opening the temp table layout, create a new record, copy the $$Temp_Variables to the new record, switch back to the primary table layout, next record, repeat as necessary.  This is much slower.

      Anyone have any best-practices on copying records to another table in the same database? 

        • 1. Re: Creating Temporary Records

          Not sure how much it will speed things up, if at all, but if you define a relationship between a field in the temp table and the primary key field of the original table, you only need to copy the primary key from the original table to the temp table. Then looked up value field options set up on all of the other fields can auto-enter the rest of the data.

          • 2. Re: Creating Temporary Records

            Why can't you just add fields in the record that would be considered temp fields and manipulate the data there and use it to create your links.




            When you are finished you could set the fields to empty.

            You might be able to use a calculation field and eliminate your scripting, or some of it.

            If you use the temp file to archive what you have done then this won't work for you.

            • 3. Re: Creating Temporary Records

               I had considered that option.  I am opening these records in a portal with a checkbox to select/deselect the records (like selecting all of the groups which belong to a given organization).  This is a multi-user application.  If I'm not mistaken, these records would be locked for editing during the selection process which could have a drastic effect on other users attempting to access/edit these records.  Is that a correct assumption?



              • 4. Re: Creating Temporary Records

                Addressing just the question asked, A radio button on the record would make the change rather quickly and then exit the record:

                Set field xxxxx

                Commit Record

                The record can be read only by disabling the edits in your form view and the button does the work. There will be no physical entry into the field. The record will be locked ONLY for the briefest of seconds after you click the button.

                Note that I did not do any checking for records locked but you could add that.

                Drastic effects occr when a user starts editing one record and goes to lunch without committing the record...  :)


                • 5. Re: Creating Temporary Records

                  While this minimizes one user locking out another, their selections will interfere with one another as they will see and get each other's selections.

                  A way around that is to set up your selection control as a button that performs a script to add the PrimaryKey of the clicked record to a list of values stored in either a global variable or a global field. A second click of the same button can remove the ID from the list.

                  If you use a global field, you can define a relationshp based on that global field and use Go To Related Records or a portal to view a list of the selected records. Conditional formatting on the object used as the button control can use Filtervalues to compare the value of the current record to the list of values to effect a visible change to show which records are currently selected.

                  • 6. Re: Creating Temporary Records

                    You can also check for record locking and issue a dialog if it is. 

                    If it persists you could script in the offenders computer, phone, email, etc. Sometimes they go to lunch...

                    Since this technique would only lock the record for miliseconts and simple loop like



                    exit loop if not locked

                    pause .1 seconds

                    end loop



                    Yesk this could be an infinite loop so add in a little exit thingy.

                    • 7. Re: Creating Temporary Records

                      Hi Jack,

                      Consider this: user one selects apple, orange, pear. User two select carrot, Beet, onion. They've selected on different records so no lock occurs. But now when they perform a find or use a relationship to see all selected records, both users get carrot, Beet, onion, Apple, orange, and pear.

                      Using a global variable or field enables each user to make and work with separate lists of selected records without any interference from other users.

                      • 8. Re: Creating Temporary Records

                        Selecting and viewing records is not the same as editing the record. Record locking occurs when two users insert a cursor into a record and begin modifying it or if you try to script a modification to a record that another user or window has a cursor inserted in a field and is modifying that field.

                        OK, a thousand users could grab data into a global and modify that global but only one at a time could save it to the record. What a mess that might be, huh?

                        • 9. Re: Creating Temporary Records

                           Thanks for all of this great feedback.  Based on your suggestions, here is what I ended up doing:

                          The end goal is to create a 1 to many link table by selecting (checking) one or more items from a list (linking a single organization to multiple groups/sub-groups within that organization).

                          I have a temporary version of the link table with a relationship by Group_ID to the Groups table. This table is pre-populated by the existing records in the actual link table to show the user the current links.

                          The Groups are displayed in a portal with a button that creates/deletes a record in the temp links table.  This record is created with the logged-in user's User_ID to assure that it is unique to that user and cannot be viewed/locked by anyone else.  Conditional formatting on the button displays a checkmark if the group is selected. When the user is done choosing and clicks the "Update" button, the temp link records are used to create the actual link records.

                          I'm not sure if this is clear, but it works great and completely eliminates the possibility of record locking conflicts.

                          Thanks again for all of the suggestions.


                          • 10. Re: Creating Temporary Records

                            Jack, you are correct that selecting should not be the same as editing, but many interfaces are designed so that "selecting" a record set's a value in a field to "select" it such as by clicking a check box field. That's what I am suggesting we avoid. Obviously there are many ways to avoid that issue. One is to populate a global field or variable with the serial numbers of individual records when a control on the list or portal is clicked/tapped. This can look and function exactly like clickinga check box, but avoids actually modifying the record and thus avoids record locks. It also keeps each user's list of selected records separate from those of others.