3 Replies Latest reply on Nov 15, 2011 8:12 AM by philmodjunk

    few complications with a stock database



      few complications with a stock database


      i'm creating a stock database for my company.

      i have my main layouts: aircraft, parts and work orders. i can create a work order for specific aircraft and also add used parts on it.

      i have come across with few complications:

      1) for example on one day there comes in a set of 200 screws by a part number 555 and i give it a batch number like 12345 when adding them to stock. on a second day there comes a new set of 300 screws with the same part number and i give them a batch number 54321. now there are two records with different batch numbers but with same part numbers. how can i make a list of parts where the parts with same part numbers are shown as one record and their quantities have been sum'd up? so i could have a perfect up-to-date overview of the parts and their quantities that i have in my stock.

      2) aircraft. this is where all the information about a certain aircraft is held – registration number, serial number, type, year, engine, propeller, etc. for example if i make a work order for aircraft X, it is a usual 50 flight hour inspection. all the same information about the aircraft appears on the work order. this is good. but what if something happens to the aircraft and the engine needs to be replaced. well i create new work order about engine replacement. but now information about the aircraft changes – new engine with new PN and SN. I can change this info via work order or aircraft, no problem. but now the problem comes up with the first work order – the information about the engine has updated there also and i don't want that to happen because when that work order was made it still had the old engine. so how can i manage to keep the information in old work orders unchanged?

      3) this thing i don't know if it's possible but i'm going to ask anyway. let's say i finished up with the work order. the work order displays info about the aircraft, work needed to be done, work done, list of used parts with pdf files of their certificates in their container fields. is it possible to write a script that takes this work order and all the certificates and adds them together as a single pdf file and save it?

      if i can manage at least with the first two problems it would be great.

      p.s. english is my second language so sorry about that! Smile

        • 1. Re: few complications with a stock database

          Everything you describe can be done, but you have some work ahead of you to make it happen.

          1) Two options can make this happen:

          A) Create a summary report on a layout based on this table with no body layout part, but change it into a sub summary part "when sorted by" the part number field. Define a summary field that computes the "total of" the quantity field. Sort your records by part number and you'll see one line for each part with the total quantity shown for that part. You can even add "in" and "out" quantities to log the reception and consumption of your layout parts and use a summary field to display the current balance of a calcualtion field that computes In - Out.

          B) Create a new Table with one record for each part number and relate it to your current table by part number. The same summary field I described can be placed on a layout based on this table to show the current count on that part

          2) You need a work order table with a line items table to list the work done on that work order that is separate by related to your Aircraft table. Since each Work order is it's own record linked to it's own set of lineItems, changes made to one will not modify the data shown in a previous work order.


          Aircraft::AircraftID = WorkOrders::AirCraftID
          WorkOrders::WOID = LineItems::WoID

          3) If all of the info to be PDF'd can be generated from FileMaker reports, then you can use the Save as PDF option to save the report as a PDF. Theres an "Append" option that allows the scritp to append the PDF being saved to an existing PDF file so the script can merge several PDF'd reports into a single PDF file.

          • 2. Re: few complications with a stock database

            Thank you very much! You have been very helpful. Although I have few further questions.

            1.A) I used this and it works. I also made two reports: amount in; amount out for calculating the current balance. It works but only until I add parts or take them away, then it stops working for some reason.

            2) I think I was a little bit unclear with this problem. What I have is layout "Aircraft" where, let's say fields like "engine part number" and "engine flight hours". The second layout is "Work Orders". When i have to do a maintenance on this aircraft I create a work order (where this same information about the aircraft appears from the layout "Aircraft") and update the "engine flight hours" because the aircraft has flown since the last maintenance. Now with the next maintenance we have to change the engine. I go to "Aircraft" layout and fill in the new information about the same aircraft: new engine part number and new engine flight hours. If I do this, the same info appears on the previous work orders. I dont' want this to happen. :)

            3) I haven't tried this yet but it's good to know it's possible :)

            4) Now new problem.

            I have WorkOrders----<LineItems>----Parts

            As I said in my first post (in point 1) I can have same parts with same part numbers but with different batch numbers. When I want to add a part on a work order it automatically chooses the (first?) batch number of the same parts. But I want it to give me an opportunity to choose from which batch I took this part? How can I manage this?

            Thank you again if you can help me :) (and sorry again for my english)

            • 3. Re: few complications with a stock database

              A) What exactly are you doing when you "add parts or take them away"? In what way exactly, does it stop working? (You may need to resort your database as the sub summary parts are only visible when the records are correctly sorted.)

              2) You shouldn't have this "engine" type data as a field in the "Aircraft" table. Instead it should be in a related table so that you can document changes to the engine with new records--each with dates so that you can see both the current equipment and what it replaced. Filters or sorting can be used when you just want to see the current parts list for that aircraft.

              4) I suggest a conditional value list where you specify a batch number in one drop down list and then select a specific part by ID from a second.

              Here are some links that provide both info and a demo file on conditional value lists:

              Forum Tutorial: Custom Value List?

              Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

              Demo File: http://www.4shared.com/file/f8NsU2DJ/ConditionalValueListDemo.html