4 Replies Latest reply on Jun 11, 2013 2:36 AM by RhysWillis

    Current record to another table



      Current record to another table


           I have a database consisting of four tables, one of which has 650 records.

           As our salespersonel contact these 650 records, they need to be sorted into the three other tables.

           I have set up an import script to move all 650 records to the table of my choice and it then deletes the current record from the current table.

           How do I set this up so I can move a single record / the current record to a table of my choice instead of moving all of them?

           Thanks in advance.

        • 1. Re: Current record to another table

               Splitting your data into 3 separate tables does not sound like an optimum database design for FileMaker. Defining a field where you assign 1 of 3 possible values to "mark" a given record as a member of one of these three groups can accomplish much the same results but gives you much more flexibility in what you can do with this data and is simpler to implement as well.

               That said, when you import records from Table A into Table B, the Import Records Step imports Table A's found set of records. If you want to import only a single record, isolate that record in a found set of just that 1 record before importing.

          • 2. Re: Current record to another table

                 Hey Phil.

                 Totally agree with what you're saying in regards to the "mark" system. Issue is if I keep all my data together, I will eventually have - for example - 10,000 records with ten different "marks".

                 Each table of 1,000 records would be treated differently as they are a different type of data.

                 An example would be having an extensive database of "sport celebrities" and each table being a different type of sport with the main table being "unsorted". When its found out what sport the person belongs in, they would be moved to the correct table of sport that they are involved with. Do you think its still better to use the Mark method?

                 If so, how would I go about making it so I can display only a set type of information in a table for a saleswoman to call the records. She would be using the iPad app.


            • 3. Re: Current record to another table

                   Some of my tables have more than 1 million records so 10,000 doesn't seem all that many--though you do have to design scripts and layouts with care as the number of records increases. You don't want to set up situations where FIleMaker bogs down summarizing across huge numbers of records in a found set or has to temporarily index a field for a find if you can avoid doing so.

                   I still think that the "mark" method makes more sense. It makes it very easy, for example, to produce a report that lists individuals from more than one sport in the same layout--something that will be much harder to do with separate tables.

                   But then you haven't explained how they are a "different type of data"--they sound like the same type of data to me.

                   There are multiple ways that you might use to limit the set of accessible records to a specific layout dedicated to that set. A portal can be set to filter--ethier in a filter expression or at the relationship level, so that the data is all of one of the 10 sets. A script can be performed when the layout is first entered to limit the records to a given set and a script trigger using OnModeExit (browse) can automatically constrain the found set to just records of a given type--enabling a user to enter find mode and perform a find, but still only get records that are from the designated type. And custom menus can even be used to keep Show All Records and Show Omitted Only from bringing up records from another set on a given layout.

                   This should work the same whether you are using fm GO or FM Pro, but custom menus requires FileMaker Pro Advanced to set up.

              • 4. Re: Current record to another table

                     Thanks a lot Phil. For now i'll go with the Mark method as you suggest and i'll worry about how our salespeople see the information later.