2 Replies Latest reply on Jan 20, 2011 9:05 AM by FrankScales

    Submitting records into a 2nd table



      Submitting records into a 2nd table


      Forgive the question as I know it's been posted before - I wanted to pose it with my solution in mind.

      I've developed a small set of records in a table, for a specific type of order - to be used by a very small number of users who will never need access to the 2nd table. It's basically a simple order form.  What is the best method to submit the order data from the 1st table, usually just one record (the current record) into my larger 2nd table and have a new record created and populated with the info of the 1st table record?

      Thanks for your insight.


        • 1. Re: Submitting records into a 2nd table

          Are you sure you don't have more tables involved in this? Most ordering systems take two tables--One for the invoice or purchase order and one for the line items that itemize the order by listing each type of item purchased in a different, related record.

          In any case, you have two options and developers argue over which is "best".

          You can use import records to copy data from one table to another--even if both tables are in the same file. This approach can move data from multiple records and multiple fields all in one go. There is, however, a bug in FileMaker's Import Records process that has to be allowed for if you use this method. See the link at the bottom for more details.

          You can also write a script that creates a new record in the second table and then moves the data from the other table one field at a time for one record at a time. To move data from multiple records, the script can use a loop to step through the records. This, obviously takes more work to create and maintain, but if completely avoids the chance that the import records bug could total scramble the data in your new record(s).

          A third option is not to move data from one table to another at all. You can set up a "preset order" in the same table as your main table and your script uses duplicate record to duplicate each record that's part of this "preset order", updating only those fields (Such as a field linking line items to the main order record) needed to maintain correct relationships and current prices in the new records.

          For More Information see:    Data loss bug : Spontaneous and erroneous import matching of new fields in specified imports !

          This is one of many acknowledged bugs that can be found in the Known Bug List here in the Report an Issue section of the forum.

          It can also be downloaded as a database file from:   http://www.4shared.com/file/8orL8apk/FMP_Bugs.html

          • 2. Re: Submitting records into a 2nd table


            Thanks. I'll give one of these a try.  To answer your question, it's only one table on the order entry.  I use value lists in my 1st table to mirror some of the input data that the 2nd table is expecting.