4 Replies Latest reply on Oct 10, 2011 1:10 PM by mgores

    merging records from querying two tables is it possible

    eyart

      Title

      merging records from querying two tables is it possible

      Post

      I have two sets of invoice information one before a certain date, the other after that date. I want to keep them separate so that the older set of data is only used for a specific query which is run only occasionally. I do not want to merge the tables. Each set of invoice information is in two tables, one contains info of the invoice, the other a table of the line items in the invoice.

      Is it possible to run a query, say find me all the invoices where x item was sold, so that the results are pulled from both the newer and older sets of data?

        • 1. Re: merging records from querying two tables is it possible
          Sorbsbuster

          The answer is 'Yes, because almost anything is possible', but the fact you are asking the question makes me want to question your assertion that you don't want to combine both sets of data into one table.  Why not?

          • 2. Re: merging records from querying two tables is it possible
            bumper

            Sorbsbuster is correct in questioning your schema. FM can handle millions of records in a table, so having all your invoices in one table makes this much easier and quicker. If your concern is users changing data in the older records then do a search in the forum for "locking a record"  The same would go for your LineItems table. Note: this is different from a multi-user situation when two users in the same record and one has effectively locked the other user out of making changes, etc. This is using Account and Privileges to make it where certain (or all) users cannot change the data in that record after a certain time or event.

            • 3. Re: merging records from querying two tables is it possible
              philmodjunk

              If you insist on keeping the records separate, You'd have to perform the same find on both tables separately, then use Import records to copy the records from the separate tables into a common "report" table--not how I'd do this due the resulting complexity. Like the other posters here, I'd use other means to control access to the records and then keep all of them in one table. Please don't hesitate to ask questions if you need assitance in applying that advice to your specific situation.

              To get a start on "record level access" control, See "Editing record access privileges" in FileMaker Help and check out this particular sub section: "Entering a formula for limiting access on a record-by-record basis".

              • 4. Re: merging records from querying two tables is it possible
                mgores

                It would be easier if both the old and current date was in the same file.

                If you perforrm find in the current file and have it go to a layout that shows the info you want, customer, qty, item, etc

                in the footer, create a portal to the older line items table (related by itemID) showing the same fields, and have the portal set to prevent modification of records.

                This should show a list of all line items with x item in the current table in the body and ones from the older table in the footer.