1 Reply Latest reply on Sep 11, 2013 1:29 PM by philmodjunk

    Excel to File Maker conversion

    schmity

      Title

      Excel to File Maker conversion

      Post

           I've had a request to find a better solution to an excel spreadsheet we use to track parts ordered against work orders.

           I'm just not sure the best way to create it all together within file maker and was wondering if anyone had any ideas.  Im fairly new to File Maker still and databases

            

           Here's what I have right now:

           One master parts tracker excel spreadsheet.
           Each worksheet in the spreadsheet represents an individual work order.
           Each row on each worksheet represents a part that has been ordered.
           Each worksheet has up to 400 rows representing every part that is on order against that work order.

           When a new work order is created, they copy a blank master worksheet and use it for that work order.

           After the work order is closed, a copy is printed (or exported to pdf) and then deleted from the spreadsheet.

           My first question is what is the best way to set it up in file maker.  They use the worksheets for ease of viewing and not mixing work orders up. 

           Is there a way to do this so each record would represent a worksheet, then have the fields automatically created within that record as a part is ordered?  There are about 15 required fields for each part.  Or would it be better to have 1 database and each part be a record and have them use a sort function to view only that work order?

           thanks for any advice/insight you can give me.

            

            

        • 1. Re: Excel to File Maker conversion
          philmodjunk

               Your questions show that you are on the right track. Take a look at the Invoices starter solution that comes with your copy of FileMaker. (If you are trying to decide whether or not to buy FileMaker, download the free 30 day trial copy.)

               Each Invoice in that example could be a work order. And the portal to a related table could serve to list all the parts on your work order. The basic relationships (Though the table names are different in the starter solution) look like this:

               WorkOrders-----<Parts>-----Inventory

               WorkOrders::__pkWorkOrderID = Parts::_fkWorkOrderID
               Inventory::__pkInventoryID = Parts::_fkInventoryID

               You can place a portal to Parts on the WorkOrders layout to list and select a Inventory record for each given WorkOrders record. Fields from Inventory can be included in the Portal to show additional info about each selected part and the _fkInventoryID field can be set up with a value list for selecting parts from inventory by their ID field.

               And using these same tables/relationships there are also other ways to list all the parts on a work order (I did see that "up to 400" figure), that do not require a portal to do so, this is just the "get started" example