4 Replies Latest reply on Feb 19, 2013 9:11 AM by tbcomputerguy

    Logging Database

    tbcomputerguy

      Hi there, trying to get my head around this and can't seem to do it. I have a logging (trees) database that we are setting up. It will store the bill of lading#, weight, M3 (Payment), etc. For each Bill of Lading there is a Cutter, Skidder, Delimber and a Trucker to Haul. The database as it is has a main table with a child table holding having links to cutters, skidders and delimbers names. I believe I should have a cutting table, skidding table and delimb table and hauling table as child tables. Now one company for instance can be a cutter, skidder and delimber...this happens rarely, but doing the cutting and skidding does happen alot. My real problem is I can't seem to get the Total Payments(M3) from two or all three tables to show up on one report. For instance the company AB Loggimg, does the cutting and skidding. How do i get thier total payments for each show on one report. On average there approx 100 bills of lading each period.

       

      Like so -

       

      Compamy: AB Logging

       

      Total for Cutting: 2755.55

      Total for Skidding: 2755.55

      Total for Delimbing: -

       

      Notice if you cut and skid you get the same amount as they are not different pays for each.

       

      I have attached the program as is.

       

      Any help would greatly appreciated.

       

      Dave

        • 1. Re: Logging Database
          BruceHerbach

          Dave,

           

          You may want to simplify this a bit.  A couple of things to look at.

           

          1) Instead of having individual name tables for each type of providor.  Have a single contacts table.  Include a field that indicates the services the contact provides.  This can be a check box set allowing the contact to show up in seaches for multiple services.

           

          2) Have a single payments table.  One record per payment.  The record should include the ID of the service provider,  type of service amount paid, date of service and date of payment.  You should be able to create reports based on dates that show all payments.  If you set it up as a sub sumary report sorted by service provider's ID,  you can show all of the individula payments to the providor as well as the total.

           

          3) You have tables for adjustments.  Consider adding an adjustment field,  that if populated adds or subtracts from the total.  You can also add a description field to describe the reason for the adjustment.

           

          Hope this helps. 

          Bruce

          • 2. Re: Logging Database
            tbcomputerguy

            Initially I did have one "company" table to draw the names from.  But, I ran into the problem of one company, many tables on the report.  Each company had a name, and a rate for each of the cut, skid and delimb services. (needed later for reporting)  I looked at this from the invoicing idea, one invoice, many different products.  Then report on the amount of each product they purchased. A single payment table, would that mean that I have to enter 3 seperate records for eash bill of lading? The services provided field in the company table, would that be a repeating field? Or a field that allows multipe entries.  Can I report on multiple entry fields?

             

            Dave

            • 3. Re: Logging Database
              BruceHerbach

              Dave,

               

              It sounds like you could have Contact table and a Contact rate Table.  In the rate table would be one record for each service the contact provides.  The rate table record would have a typeof service field and rate field. 

               

              As far as the bill of lading goes,  when you create one on paper,  how many items go into it?  Most invoices, have line items.   One for each item sold...  By having a single table with multiple lines it becomes very easy to report on and display related records.

               

              You might want to look at and play with the FileMaker Invoices starter solution.  It has similarities to what you want to do.  I think....

               

              Take a look at Subsummary reports.  You can have a number of specifc sections depending on how you sort the information and can display multiple fields.

               

              I'm not proposing repeating fields.  FileMaker handles fields with lists of information in them.  The field can be used in relationships,  with functions like valuecount() to determine how many items in the field, getvalue() to pull a specific value out.  These can be very handy.

               

              Bruce

              • 4. Re: Logging Database
                tbcomputerguy

                I will try.  I added two screen shots to the original post.  We currently do it in excel.  There is the recap and the final payout sheet we use.

                 

                Thanks