4 Replies Latest reply on Mar 31, 2011 9:35 AM by philmodjunk

    Vessel tracking, reporting and inventory setup! No idea!

    SamGray

      Title

      Vessel tracking, reporting and inventory setup! No idea!

      Post

      Hello all,

      Sorry to have absolutely no idea at all... but I'm trying to set up a database, with daily, weekly and monthly input of different information, regarding a ship's position, its environment, a report of it's catch, sorted by species, and weekly/monthly reports of inventory levels (fuel, bait, etc..)

      I can set this up fine as a single record for each ship... but I need to be able to keep the information of each individual daily/weekly/etc. update... and then combine totals of this in reports...

      HELP!!!

      My main issue obviously is how to make a single record on one table that is effectively a total of every record on a separate table...

        • 1. Re: Vessel tracking, reporting and inventory setup! No idea!
          philmodjunk

          It might help to see what tables you've selected for your preliminary design here and how you intend to related them.

          Obviously, you'll need  a ships table and a related table for reporting their catch. You'd likely need additional tables for logging ship position and for logging each time a ship leaves port to catch fish. Something like this:

          Ships-----<Trips-----<Catch
                            |
                            ^
                         TripLog

          My main issue obviously is how to make a single record on one table that is effectively a total of every record on a separate table...

          You may not even need to do this to get your totals and sub totals. A Summary report created on a layout based on the Catch table can provide a number of different reports where you get totals broken down by Ship, Trip, Species or not all by how you set up sub summary layout parts and in how you sort your Catch records.

          Here's a summary report total that may give you some clues on how to set up such a report:  Creating Filemaker Pro summary reports--Tutorial

          If you do decide to go ahead and compute a total of records from another table, here's an example of how to do that:

          Define a calculation field in Trips as: Sum ( Catch::Qty ) and it will report the total catch quantity for any given Trip record.

           

          • 2. Re: Vessel tracking, reporting and inventory setup! No idea!
            SamGray

            Thanks Phil, I'll give it a shot first thing in the morning, and post a few screencaps of what i've got to work with.

            • 3. Re: Vessel tracking, reporting and inventory setup! No idea!
              SamGray

              Just getting around to playing with the database again, and of course I have a lot of questions!

              Would I have to make a new table for each piece of data I need to work with? would I create one table per piece of data, per ship, or one for all ships?

              e.g.

              Tables:

              Reports (with fields: ship name, drop-down for report type (daily, weekly, etc) field for week no., a date calendar and an automatic timestamp)

              conditions (with fields for ship name, water temp, and text field for descriptive info)

              catch (ship name, each variety of fish)

              Incidents (ship name, injuries, supplies used, parts used, fire equip, damage)

              Bait on hand (ship name, fields for each bait type)

              Fuels on hand (ship name, quantities of fuel types)

              etc.

              Would the relational field for each be Vessel Name?

              If we decide to integrate the reports into an Inventory, what fields would change and how? would I make a separate database or separate table, with the initial quantities, and then alter the reports to ask for quantity USED rather than current total quantities?

              Thanks for anyone's continuing help on this, in a high pressure situation!

              • 4. Re: Vessel tracking, reporting and inventory setup! No idea!
                philmodjunk

                Would I have to make a new table for each piece of data I need to work with? would I create one table per piece of data, per ship, or one for all ships?

                No, you would create a new record for each new piece of data or you would enter data in a field. One record, for example could be set up in a Vessels table where you'd list the name of the ship, it's displacement, Year first launched, etc in different fields of the same record. Each time you need to record data on a different vessel, create a new record and start filling in the fields. Think of each record as a paper form where each blank on the form is a field. Put all your "forms" of one specific type together in a pile and you have your "table". If you find you need to record information on a different type of form, for repair work orders, for example, that requires a new table with a different set of define fields to describe the blanks on that form.

                Using names for relational links are not a good idea. They may not be unique, they can be entered incorrectly due to human error and the owner may choose to chance some bad luck and change the name of the vessel. Define an auto-entered serial number field in the Vessel table instead so that each Vessel record gets a unique ID number. Using the above Repair Work Order example, you'd link all repair work orders to a specific vessel by adding this same VesselID field to this table and then there are several ways to make it easy for the user to link the Work Order record by entering the matching VesselID number.

                Inventory management is a fairly complex inventory task in it's own right. Are you sure you're up to designing your own?