12 Replies Latest reply on Jun 20, 2014 5:07 PM by Matty_1

    Record Status and date transfer.

    Matty_1

      Title

      Record Status and date transfer.

      Post

           Hi There,

           I currently have a total of three tables involved in my inventory tracking system.  Purchases, Sales and SalesMix.  What we do is purchase a wide variety of products, create our own mix and sell them as new mixed products.  For the purchases the inventory tracking is as simple as it gets.  All the information coming in can stay within the one record which includes important information such as grade of product, date and quantity and record status.  The reason why record status is important is because I want voided records to be excluded from the inventory (obviously).

           The issue/tricky part is in the sales.  Because any given product is often comprised of a combination of various purchase products I had to build a sales table where all the main information lives and then I created a second table where the mixed products get listed in a portal.  Quantity and grade live on the SalesMix records which effect the inventory directly but the date and record status live on the sales records and I had no choice but to build a script trigger that transfers any date or record status changes over to the associated SalesMix records.  In theory that works perfectly fine but in practice for some odd reason every now and then (about .5-2% of the time) the date or record status doesn't transfer and I can't seem to figure out why. So when we pull up or inventory reports to compare against our accounting software I'm forced to go into the back end and find the ones that have different status' and dates.

           I'm wondering if there is a better way then to use scripts to make this work.  I tried designing a relationship that used the sales for the date but the grades from the SaleMix and could not make it work.

            

           Any input or ideas would be greatly appreciated!

        • 1. Re: Record Status and date transfer.
          philmodjunk

               Can you show the relationships that you've used to link these tables. Seems like you are missing some tables here. Is a "mix" a specific product sold only once to only one customer in one sales transaction? If they come back for a new "mix" is it a new record all over again?

               I keep thinking you need one table to document each standard mix in order to document what purchased products in what proportions are used to produce that mix with a related table listing each product that makes up that mix along with the amounts needed for one unit of that mix.

               That then would serve as a list of products that a customer might choose from when purchasing from you.

               But I am making a lot of assumptions that may not actually be the way your business operates.

          • 2. Re: Record Status and date transfer.
            Matty_1

                 Here's the relationship.  Ignore the bottom relationship, that's to view inventory based on the invoice dates and I didn't bother mentioning the inventory adjustment table as it's just like the purchases.  I select grade and date and quantity all on the same record.

                 You will notice that sales isn't part of the relationship at all as it simply acts as a middle man to contain the "per transaction" information.  What the client sees is the information on the sales page.  IE All they would see is Volkswagen golf and not 58 bolts, 4 doors, 1 steering wheel etc etc. the details is what the sales mix is for and is for internal use only.

                 We have no "standard mix" in our business.  We supply on a per need/availability basis.  As it stands we don't normally exceed more then a four product mix BUT I didn't want to limit myself to it so that it could be scalable so I left it wide open.  You can 1 to an infinite mix of products if you'd like.

            • 3. Re: Record Status and date transfer.
              philmodjunk

                   Instead of matching by multiple fields, you should match by some ID fields that auto-enter a serial number. That simplifies your relationships so that only a single pair of match fields are needed to match records and your date info will only reside in one table, not both so that if you need to change a date, you need only change it in one field of one record.

                   But while each "mix" may be a "one off" custom mix, I am guessing that multiple mixes might use the same product as one of the ingredients.

                   Something like:

                   Products-----<SalesMixIngredients>-----SalesMix

                   Products::__pkProductID = SalesMixIngredients::_fkProductID
                   SalesMix::__pkSalesMixID = SalesMixIngredients::_fkSalesMixID

                   You'd put a portal to SalesMixIngredients on your SalesMix layout and use it to list the products and proportions of each product used to produce the mix. If you were manufacturing a car, SalesMixIngredients would be your Bill of Materials (BOM) table for listing the parts/components that make up the car.

              • 4. Re: Record Status and date transfer.
                Matty_1
                     

                Instead of matching by multiple fields, you should match by some ID fields that auto-enter a serial number. That simplifies your relationships so that only a single pair of match fields are needed to match records and your date info will only reside in one table, not both so that if you need to change a date, you need only change it in one field of one record.

                If you're referring to the the relation ship between Sales and SalesMix then that is what I have in place.  The relationship is as follows:

                Sales::_pknID = SalesMix_Sales::_fknID

                     Sales is where resides all the information the buyer needs to see, i.e. what we're selling, the price, the amount, the date etc etc.  From the sales page we then add records via a portal using the relationship listed above and for internal purposes we mark the mix.  So the seller sees we sold him what call our "Bird Feed" and we see a list of quantities in a portal of all the various seeds used in the mix.  The various seeds are separated by grade.  On the incoming side (purchases) we see a load of seed come in and grade it a certain way i.e. 100, 200, 300 etc.  On the sales side we see a certain amount of weight used of the 100s, of the 200s etc etc.

                The problem is that what ultimately affects the inventory is whatever records are in SalesMix hence the transfer of dates and record status.

                     

                You'd put a portal to SalesMixIngredients on your SalesMix layout and use it to list the products and proportions of each product used to produce the mix. If you were manufacturing a car, SalesMixIngredients would be your Bill of Materials (BOM) table for listing the parts/components that make up the car.

                Not quite following you on this, are you suggesting we add a layout that lists out all our products and how much we have of it?  If so that is what our inventory layout is for and is included in the jpeg.

                • 5. Re: Record Status and date transfer.
                  Matty_1

                       small correction that could cause confusion, this is the relationship:

                  Sales::_pknID = SalesMix_Sales::_fknSalesID

                  • 6. Re: Record Status and date transfer.
                    philmodjunk

                         I am referring to the relationships that you showed in your second post, the links between Products and mixes. I see no need for using 3 or 4 pairs of match fields in these relationships and they unnecessarily complicate the function of your database by duplicating data into two tables that should only be stored in one.

                    • 7. Re: Record Status and date transfer.
                      Matty_1

                           But the date and record status are just as important as the grade ...

                      • 8. Re: Record Status and date transfer.
                        philmodjunk

                             Yes but they do not need to be recorded in both tables in order to link them in a relationship. It's the fact that you then want to update the date that illustrates why this then becomes a complication best avoided by not using the fields like this for matching records. When you change the date in a record of one table, you break the relationship linking that record by date to records in the other table. Such an update can be done, but it's a far better approach to not have to do such an update at all.

                             Please note that when you have two records linked in either a one to one or one to many relationship:

                             table A ----- Table B

                             or

                             Table A ----< Table B

                             Any field from Table A can be placed on a layout for Table B and it will show the correct data just as though that field were present in Table B.

                        • 9. Re: Record Status and date transfer.
                          Matty_1

                               I see, I think there is a slight confusion ... or I'm still confused but when I read your reply I get the impression you think that I'm using the date to form a relationship between my Sales table and my SalesMix.  If so, that is not the case.  I'm strictly using IDs to form the relationship.

                               The reason I need the date to link the calculation between the addition of the Purchases and the subtraction of the SalesMix is so that we can go look at inventory levels on any given day.  We continuously audit our books by comparing against FileMaker and rather then stopping everyone from working in filmmaker we chose dates in the past in both our accounting software and FileMaker and compare numbers to maker sure we haven't missed anything or made errors in entering our information.

                          • 10. Re: Record Status and date transfer.
                            philmodjunk
                                 

                                      I get the impression you think that I'm using the date to form a relationship between my Sales table and my SalesMix.  If so, that is not the case.

                                 Yet that is exactly what you show in your relationships screen shot. You have a date match field in Inventory that matches to date fields in each of the other related tables. You match by additional fields as well and that needlessly duplicates data that should stay in just one table. By using fields in this way, any time that you need to change the value in a match field, you have to carefully update the corresponding match field in the related records first or you lose the connection to related records--an issue that can be avoided by not setting up your relationships like this.

                                 

                                      The reason I need the date to link the calculation between the addition of the Purchases and the subtraction of the SalesMix is so that we can go look at inventory levels on any given day.

                                 But this can be done by other methods and those other methods will not require "stopping everyone from working in filmmaker".

                            • 11. Re: Record Status and date transfer.
                              Matty_1

                                   That picture up there was to show you how inventory needs it's relationship to calculate but what I'm looking for is help on the relationship between sales and salesMix.  Please refer to this new photo I've uploaded.

                              • 12. Re: Record Status and date transfer.
                                Matty_1

                                     You can see that all I have is the recordID and the match field in sales mix creating the relationship, plus anything marked as "keep".  If it's marked as delete it loses it's connection.