1 Reply Latest reply on Feb 18, 2009 8:48 AM by Orlando

    Search multiple database files



      Search multiple database files


      We have a database that tracks all jobs of one client.  This year, we changed the calculation of one of the calculation fields.  Therefore, we created a new database beginning with the year 2009.  The 2009 database is exactly the same as the first database with the exception of the one calculation. 


      While in the 2009 database, can we execute a search of all records in the 2009 database and the database with the previous years' records so that the search results might include records from both databases?  If so, how is this done?


      Thank you.

        • 1. Re: Search multiple database files

          Hi Dixie


          The only way to display search results from multiple tables is to create a join table that hold a record for each record found in each table, and has a link to the record id of the appropriate table. So if you found 10 records in 2008 and 15 record in 2009 your join will have 25 records, does this make sense? 


          Then you would have a go to button on the record that called a script that checked which source table the record is related to and then used one of multiple Go to Related Record script steps to display the appropriate record.


          Its not simple but might work for you.


          Another suggestion is relating to the fact the data is split in the first place. Was you splitting of the data purely on the basis of a single calculation? If so I would suggest a different approach.


          You could create a field the user has no access to that distinguishes the version of the calculation to run, so a 1 for version 1 of the calc and a 2 and so on, in case the calc changes again.


          The the Calculation in question woudl start with a case statement that simply read


          Case (

          CalcVersion = 1 ; [Original Calculation] ;

          CalcVersion = 2 ; [New Calculation]


          Then set the ValcVersion field to Auto-Enter the value 2.


          Would make searches and maintenance easier for you.