7 Replies Latest reply on Jan 13, 2012 2:49 PM by philmodjunk

    Copy table records to a temporary table??



      Copy table records to a temporary table??



      I copy a found set of records to a temporary table tagged to the logged-in user's ID. The user can then select records and add data in a portal. These modified records are then used to build links in a many-to-many link table. Currently I create a found set in Table1_Layout then switch to Table1_Temp_Layout and import the records into the temp table. This works great, but the FMPro documentation indicates that this will not work in a web solution. I need to have desktop, web and FMGo access. I have done the same thing by looping on the found set and capturing the needed fields to global variables, adding records to the temp table then setting the temp table fields from the global variables. This also works fine, but seems very cumbersome. Does anyone know of a cleaner way to accomplish this? Any help would be greatly appreciated.

        • 1. Re: Copy table records to a temporary table??

          Why do you copy the records to a temporary table in the first place? What problem does that solve or avoid?

          Depending one what you are trying to achieve, it may be possible to do what you need without copying any records to a temporary table.

          • 2. Re: Copy table records to a temporary table??

            Thanks for the quick response.

            This application will end up being server based (probably in a data separation model) and be accessed by several hundred users.  Let me try to give you an example of one of the uses of the temp table:

            * A found set from the Categories table is copied to the Categories_Temp table which has 4 additional fields beyond the Categories table (Category_Select, Category_Value, Value_Type and User_ID).  Each new record is tagged with the logged-in user's ID.

            * The logged-in user selects the categories they want (checkbox), provides a value and value type (drop-down list). The selection portal for the temp table showing only records tagged with their User ID so they cannot be locked by anyone esle in the system.

            * These entries and values are used to create records in a link table to link multiple Categories to a Service table record.

            The primary reason for using the temp table tagged to a specific user is to avoid locking records in the Categories table during the selection process. Several users could be performing similar actions at the same time.

            I am certainly open to any suggestions to simplify this process and make sure it will work on the web and FMGo.

            • 3. Re: Copy table records to a temporary table??

              I'm just not seeing where edit locks on the records will be an issue if you are using selectd records to create new records in your link table. That does not require opening any category records for editing.

              That said, one way to streamline copying data from one table to another is to establish a relationship and looked up value field options on the fields so that you simply copy over one value--which enables the new records to match to the correct record via the relationship, and then lookups copy the data into all the other fields. Much like entering a productID number into a line items record causes pricing data to be copied into the price field(s) from the products table.

              • 4. Re: Copy table records to a temporary table??

                 I thought it would be tough for me to explain.  Let me try this:

                For sake of this discussion, let's say that there are 5 categories (records) that each user can select to attach to the Services.  I believe that the records would be in edit mode (locked?) when the users are checking the Category_Select checkbox and entering numerical values in the Category_Value field.  Potentially (not really likely), 500 users could be trying to select the same categories at the same time.  If they were all trying to select the 5 categories from the same table, wouldn't they all try to lock the records?

                From what I can figure, when I use the Import script step, I have to select a file to import from (even though it is in the same database file).  Is there a way to select from the same database without having to specify a file?  I believe that I read that this import function will not work in the web version.  Is that correct?

                If I truly need to create the temp records and the import won't work in the web version, is my only other option to loop and add records?  Is there a way to copy an entire record and not field by field?

                Sorry to be so slow in the updake.  I really appreciate your expertise and willingness to guide me through this.

                • 5. Re: Copy table records to a temporary table??

                  Selecting the category can be done without locking the category record if you use a button and some careful scripting to modify a global field listing all selected categories instead of a field in the category table. Specifying a value to go with it is more of a challenge.

                  One option is to click the button to select the category then enter the value directly into a value field of the new link created by the mouse click record instead of the category field.

                  • 6. Re: Copy table records to a temporary table??

                     Thanks for the tips.  How about using import in the web application?

                    • 7. Re: Copy table records to a temporary table??

                      The import records script step is not web compatible. You can open the script editor and select "web publishing" in the compatibility drop down to see which script steps are web compatible.