2 Replies Latest reply on May 9, 2013 8:51 AM by BZ

    Using temporary table for found records



      Using temporary table for found records


           In the current situation I have a list with, let's say, 100 names. I put this into my find field and get all the found names including the data that belongs to those names. with an error message of the names that couldn't be found in the database. As I want to use the database for analizing data as well, it would be way better if I can get excactly the same list of 100 values as output including the data, including the names that can't be found (where I want to put "missing" in the fields that are filled in for the found names)

           I thought to use a temporary table to get this working. but I read a lot of negative advice on using a temporary table.
           So I thought, to copy the list with names to a temporary table (with an empty field value se to "missing"), perform the find to fill in all the data. Then I can export the temporary table and easely compare it to the originale list, because it's the same.

           Any suggestions what would be the best way to do this ?

        • 1. Re: Using temporary table for found records

               I don't see how it would be "way better" to export this data to another table. Can you explain what advantages you see to using that method? It may be that we can suggest an alternative approach that does not require exporting your data like this and then you have a much simpler system to manage.

               Does each "name" appear only once in the table you are searching?

               Is there a primary key field that uniquely identifies each record?

               If it really is necessary to use a temp table, it's entirely possible that all you need do is acquire a list of primary keys from your found set to export to your temp table and then a relationship can use them to access the data in your original table--keeping the duplication of dat to a minimum. This will require a bit of creativity to include the list of "missing" names, but this is possible with a bit of "sleight of hand" layout and data design.

          • 2. Re: Using temporary table for found records

                 Sorry, maybe I wasn't clear. I didn't mean it would be way better to use another table...it was actually my question if it would. With way better I meant that I wanted to get the same input list of names as output, also with missing names and in the same order.

                 Anyway, your suggestion to use the primary keys was very usefull and I used that method in a temp table to display the found data. Using a global field to store the not found names I made the missing names also appear in the list. So got it all working.

                 Thanks !