10 Replies Latest reply on Mar 14, 2013 3:09 PM by philmodjunk

    Suggestions for my data base 2.0



      Suggestions for my data base 2.0



           I currently have a somewhat well working data base.  When I first started building it, my knowledge of FileMaker Pro was minimal but over time and with the help of this wonderful forum (mostly Phil, not discredit the help of others though!) I was able to build a fairly strong system for our company.

           That being said, I feel it is now time to create a 2.0 version of my current database to increase it's performance/longevity and remove all the mistakes I made in the early days.

           So I'd like to start this thread to get some food for thought before I dig into the somewhat monstrous task and toss out a few ideas to see if I will be setting myself down the right path this time.  Most of these ideas come from things mentioned in passing on other threads I've posted and instead of digging up the old ones I'd like to bring them up here and have them all under the same roof.

           I think the biggest issue with the current database is the lack of archiving records.  I have two main example that demonstrate why it's becoming more and more important to have this ability to increase the data bases performance.  Currently our inventory must calculate thousands upon thousands of purchase and sales records dating back to day 1 to tell us what we have on hand.  This is achieved by having a ledger table between the purchase and sales tables calling upon the products purchased and sold and calculating the difference.  Loading this page can take sometime but is still performing relatively well.  Second,  we have a table that we use to forecast our sales and purchases based on our current contracts.  This one was a little trickier to build and was without question macgyvered to make work with the knowledge and design I had to work with at the time.  The biggest kicker in this forecasting tool was that our buyers needed to see, in the same window, a list of all the purchase and sales contract in order that they were created and the ability to get averaged prices based on the selected number of records to "go back on" my solution was somewhat messy, with script triggers creating new records in this table that copied over some information and have some calculation fields pulling over other information and this has become terribly cumbersome in it's performance.  I had no choice but to do this because the purchase and sales contracts are in two separate tables.  My solution (please feel free to suggest other ideas) would be to implement archiving and consolidating purchase and sales contract into one table allowing for easy listing and completely eliminating the scripts that copied over all the information.  I've also played with the idea of doing the same for the individual purchase and sales transactions (not sure about that one)

           Phil if you're reading this, I know at one point way back in the day you mentioned that you have a nightly script that runs and archives all of the days invoices copying over all the pertinent information (like amount of parts sold etc etc.) into master records for each inventory item that acts as a ledger for quicker inventory calculations.  My question is: If I'm to do this, am I simply changing the status of the transactions to archived and running the script to run through and find all the information that needs to go to a master record and of course have the inventory only calculate open tickets or am I actually moving these archived transactions to another table?  I've currently removed the ability to edit a record once it's been printed but we have had to go back and tweak some information on records as old as a month or two on a regular basis ..... I'm guessing I would put a script in place that picks up any changes made to the archived records and update the master ledger records if needed?

           Once we get this out of the way I would like to touch on a data base design that consists of having all the layouts in one file and data stored in another (I think this would be extremely beneficial to us --  Phil I know at some point you has given me a few links on this but I'd be digging for a while if I tried to find the post again) and touch on look-ups. 


           As always, appreciate the help!

        • 1. Re: Suggestions for my data base 2.0

                    Phil if you're reading this, I know at one point way back in the day you mentioned that you have a nightly script that runs and archives all...

               That's not quite what we do. We purchase scrap metal an redeem used beverage containers from the general public. Our "invoices" are really purchase orders that document each time we purchase materials from a customer. The results aren't used for inventory purposes as a truckload of loose metal that may or may not be compressed into a bale for future sale doesn't really lend itself to "inventory control" like you would in a retail sales environment.

               Thus, the table into which we summarize data is a special purpose table used solely for storing partially summarized data so that our reports comparing quantiites and costs for different months, quarters, years..... produce the needed figures from a much smaller number of records than if we pulled it directly from our lineitems tables.

               But the concept could be applied to your inventory transactions (or did we call it "ledger") table.

               Imagine what you would do with a hardcopy check book register when you run out of pages and have to start over with a new register. You take the last balance of your old register and use it as the first "beginning balance" entry in your new register. A script can do that kind of update of your transactions ledger by computing the total on hand for a product, putting that value in a variable, deleting the transaction records for that product and then creating a single new transactions record with the current total on hand--copying that variable's value into the "in" field of this new transaction record. The script can loop to automatically update the transaction ledger for each product in your inventory--paring down your data to just one record per product.

               This is definitely something to implement and test on a copy of your file before taking it live in your production copy of your database.

               The final issue will be to decide what to do to back up your transactions data before condensing your transactions table. You can set up a an Archive file and use import records to copy the data into the archive just before you condense your transactions file or you can just save a copy of your database file. Saving a copy of your file is simpler to do. Setting up a an archive file allows you to examine a complete, continuous set of your data--which may prove to have advantages from time to time should you want to review past trends in your inventory levels as they fluctuate up and down.

          • 2. Re: Suggestions for my data base 2.0

                 Thank you Phil, so the only way to have an automated import to one data base and purging of another would be with a copy of FileMaker sever correct?

                 Otherwise I'll have to run these scripts manually correct?

            • 3. Re: Suggestions for my data base 2.0

                   Server is not needed and, in fact, server scheduled scripts cannot import records directly from one FileMaker FIle into another.

                   I use a Robot FileMaker File to perform the needed script on a schedule created in Windows Task Manager. This method wiill also work if you do not use FileMaker server.

                   The robot file is set to auto-enter a password for an account/password that matches an account in the main file with sufficient permissions for the script in the main file to do what it needs to do. It is then set to run a very short script each time that it is opened:

                   Perform Script [//specifies a script in the main file that archives the records]
                   Quit application

                   I use settings in File Options to specify the automatic account name and password as well as the script to be performed each time the file is opened.

              • 4. Re: Suggestions for my data base 2.0

                     Very cool, any idea where I can do this on a mac?  I cracked open the automator and did not find File Maker in the list.

                • 5. Re: Suggestions for my data base 2.0

                       I've also done a little more digging and I think I found out why the forecasting tool has become so cumbersome, I failed to recall that not only did the buyers want to see a list of all the purchases and sales in secquancial order with a summary field sowing how far ahead or behind we are.  They also wanted to ability to see a running balance as the contracts came in.  This script finds the requested set of records sets a balance value of zero then starts at the bottom and adds in the first value be it positive or negative, then on to the next record etc etc.  This outlines the balance at each entry.  The problem with this is that the value needs to change with different found sets .... is there a better way of doing this to avoid long waits as the script loops through to populate the balance field?

                  • 6. Re: Suggestions for my data base 2.0

                         Not being a mac user, I can't give you details. I've seen references to mac users doing this from Kronos (Cronos?).

                    • 7. Re: Suggestions for my data base 2.0

                           Phil, could you please send me a few links or an example of a data base that has all the layouts in one file and the data stored in a second file?  Thank you!

                      • 8. Re: Suggestions for my data base 2.0

                             I do not have such a demo file at hand. But an existing single file solution can easily be "split" into interface and data versions of the same file:

                        Convert to Seperation Model

                        • 9. Re: Suggestions for my data base 2.0

                               Wonderful, that's incredibly easy, thank you.


                               Were you able to have a look at my post about the balance script?  I'm generally struggling how to come up with the better version of my cumbersome forecast table design .... I'm not 100% sure if it would be ideal to amalgamate our purchase and sales contracts but at the same time if I don't I will be stuck with all these calculation fields that call upon other calculation fields therefore there is nearly no indexing.  We have manageable performance for the time being but not long term.

                               One example of this is the following:  Because each record in the table has a potential relationship to either sides (purchase and sales contract), each record needs to verify a fair bit of information.  The ultimate goal of the forecasting tool is to compare how many metric tonnes we have purchased of a particular product versus whats been sold.  If the sum is positive we need to sell, if the sum is negative we need to buy.  In each record there is a field that create a relationship to either a purchase contract or sales contract.  Once that is established, a calculation field calls upon another calculation field in which ever contract it is related to. The calculation field in the contracts takes the contracted tonnage when open and actual tonnage picked up or delivered when closed (sometimes we over or under deliver which is the reason for this)  I then have a summary field that calculates all the positives a second summary field that calculates all the negatives and a third calculation field that compares the two.  Over and above that I have the balance looped script so that whatever found set has the correct balance progression.

                               Is there a better way to do this!?

                          • 10. Re: Suggestions for my data base 2.0

                                 I don't recognize the thread to which you are referring. I don't think I see that info in your original post to this thread. I'd prefer to discuss this there or in a new thread. And in that thread, you'll need to carefully spell out the tables and relationships that you have. I really can't tell much just from your most recent post made here.