2 Replies Latest reply on May 11, 2014 2:43 PM by HugoLidia

    Database design advice - for equipment



      Database design advice - for equipment


           Could somebody give me some advise on  how I structure the database, if say we are buying in 2 items and these are sent out to clients in pairs - any 1 from product A with any 1 unit from product B.

           I specifically am wishing to understand how I control stock levels.  When an item is dispatched to a client, how would I automatically flag it as dispatched.  Would it be just a case of using a flag to indicate if "S" is in stock and change to "D" for dispatched and then sum the total of all items by this flag.  Guess if "S" ( those in stock and not dispatched) is less than our designated minimum reorder level, if should trigger an e-mail or system warning that more items need to be ordered.

           The difficult part is how would I control this, if we have a main database in the office and a field engineer has stock with him.  Once the items have been installed  and a report signed by the client, the engineer needs to send an e-mail back to Head Office to confirm this.  How can those details de imported and updated into the database.  I do not want to have to host the database on a server - there are only 2 engineers in the field and I admin staff in HEad Office.

           Comments and advice would be appreciated.

           NB This is a database running on Windows 8.1 while the field engineers have iPads.

        • 1. Re: Database design advice - for equipment

               The first problem is that anyone reading your post will have only a partial understanding of your business procedures. For example, when an item is "dispatched", is it ever returned?

               If the items leave inventory and never return, you may want to use this method: Managing Inventory using a Transactions Ledger

               If you are lending and returning items, you may be able to adapt the Asset Starter solution that comes with your copy of FileMaker.

               Then you have the issue of sending out field engineers. This suggests that you track what items are in the possession of the Field Engineer and which have been distributed to clients. And your Engineer might have an iOS device such as an iPad or iPhone to record transactions. That device can "synch" back to the main database, or if you can count on a good wireless connection, link to the database as a client of that database. Lap tops and tablets that run the Windows OS are also not out of the question as a way to manage this.

               Sending an email back to the office to document the transaction may actually be harder to automate than the alternatives that I've just described.

          • 2. Re: Database design advice - for equipment

                  I was thinking of sending e-mail back with a csv generated file that maybe is imported back on the main database, if possible.

                 Yes, field engineers will have stock to install and stock may need to be returned - always a chance a unit fails!

                 Will look at the suggestions and sync back - good way to test installed ADSL unit definitely works



                 Other comments welcome