4 Replies Latest reply on Nov 29, 2011 5:22 PM by vmtc

    Create schedule from two tables

    vmtc

      Title

      Create schedule from two tables

      Post

      I have database that tracks the production and sale of books. There are separate tables for authors, titles, contracts, production, print run details etc. All of it works OK.

      From Production you can easily generate a schedule which shows what is due when. The schedule uses fields from about three tables (eg Title from BOOKS, Printer Month from PRINT RUN DETAILS, Status from PRODUCTION). All fine.

      However, I would like to be able to add in some potential publications to this schedule, without adding real records to the main database. I can easily create a separate table with these possible publications. But I don't know how to connect them. All the records I want included will share the same publication year. What I can't quite work out is how to create a schedule that grabs information from both the Production (and related) files and this Potential file, based on the publication year field, and then sort and list them by Printer month to give me a schedule. 

      I'm using FM 10 Advanced on a Mac (though DB also used on Windows)

      Thanks for any suggestions....

        • 1. Re: Create schedule from two tables
          philmodjunk

          However, I would like to be able to add in some potential publications to this schedule, without adding real records to the main database.

          Why not add them?

          I'd add the records to the existing table(s), but use an additional field to identify them as "potential". That way finds, sorts and portal filters can be used to exclude them on layouts where such records should not be visible.

          • 2. Re: Create schedule from two tables
            vmtc

            I suppose the reason why I don't want to add them is because many of them won't eventuate and it will mean creating new half-hearted records (we will only have some details) in several tables that become messy leftovers.

            Perhaps another way would be to have manual fields in Production for author title info and a calculated field that looked in both places for the schedule layout. That way at least the superfluous entries would be contained to one or two tables.

            Thanks. I was getting caught in a loop with my thinking.

            • 3. Re: Create schedule from two tables
              philmodjunk

              It's your database, but I'd still go with using one set of tables and just labeling the potential records. If you set up the correct delete options, deleting a "potential" record that doesn't pan out would set up a cascading delete that also deletes the related records. (Do this carefully or you can delete stuff that shouldn't be deleted...)

              Or you can keep all the records and just use the methods mentioned earlier to exclude potential records (and those related to them). No clutter visible to your users and if you ever need to review past "potential" entries, you still have all the data in the system. (And you might add a "canceled" status option to keep old not going to be used, potential records separate from newly added ones.)

              • 4. Re: Create schedule from two tables
                vmtc

                It's still one set of tables, just an extra field for the potential entries, for author title info (and a calculated one to display from whichever source). Author and title are the trickiest to delete because the author may have other real titles and it's also possible the title is published in another form, so a cascading delete would be complicated. I can't see the advantage of creating new author and title records that may end up as clutter.