1 2 Previous Next 15 Replies Latest reply on Feb 17, 2015 11:26 AM by ChrisJohnston

    Relationship Scrutiny

    ChrisJohnston

      Title

      Relationship Scrutiny

      Post

      I have recently created a solution to manage inventory for our business. I remember when I was building another solution that we heavily rely on these days, the advice I received on this forum was a wealth. We were in need of a special type of relationship and the Volunteer Community Leader suggested some things that were very helpful. In short he gave the idea about a relationship type to use with the solution, and that was the reason it succeeded. To date the solution helps us so much. At the time this was very much over my head. However it pushed me to learn and that was best part. It’s because of this advice that I have the skills now to attempt relationships for the current solution I am referring to. So I wanted to also post my new design here hoping to get the same helpful eyes of the community and the Volunteer Community Leader. This way I will feel that I have made every effort to start from a place of best practice, and like I said, the advice here has helped me learn so much.

      The main area where I think I need to look at, is the QUANTITY:: table. The way I imagine it is that, I will have each ITEM:: record (because they all have a quantity 1 or more) have a records in the QUANTITY:: table matching it’s _Pk (Primary Key). So if the quantity of a Flashlight is 20, there would be 20 QUANTITY:: records for that ITEM::_Pk. I figured it could easily be manipulated, via scripts to update the related records when an ITEM:: record has a change in quantity. We also have AUCTIONS:: this is because we sometimes put unused items on eBay. I figured that we could always be referring to the first record of an ITEM::. One thing I was wondering is would it make better sense to have one QUANTITY:: record for each ITEM:: record and just have a count, but it seemed like a better and more versatile by having them individual. Because QUANTITY:: records help us store them in our LOCATION:: table, I find individual better if some are stored in different places. Is there something obvious I am overlooking? If there is some better way of a relationship to be setup in this type of solutions I welcome the suggestion. Thanks

      relhelp.jpg

        • 1. Re: Relationship Scrutiny
          philmodjunk

          When designing a inventory management system, a basic difference is whether you choose to track each individual item separately such as the cars sold in a dealership, works of art in an art gallery or antiques sold by an antique dealer, or whether to track them in groups such as all 30 claw hammers sold in a hardware store.

          Your current design tracks each item individually. If a "flashlight" truly represents the type of items that you are selling, I'd carefully consider tracking the items in groups. A transaction log can be used to log each inventory change in your system, whether it be a sale, receipt of new goods, "shrinkage", or moving items to a different storage location (With two transaction records, one removing them from a given Tier/location and one adding them to a different tier/location.)

          For an example of such an "inventory ledger" approach, see: Managing Inventory using a Transactions Ledger

          • 2. Re: Relationship Scrutiny
            ChrisJohnston

            Once again sir, I see! A bit of time spent now to save a mountain of time later, WOW! Your expertise is such a wealth. Every transaction must be logged, I get it! I will reconstruct some things and if I get stuck will inquire here. Thanks a bunch!

            • 3. Re: Relationship Scrutiny
              ChrisJohnston

              I have added a transaction table and I works great. I would like understand now how related to it, how I can flatten (for lack of better term) the results I can see in a portal. Now in a portal I can see all the entries in the transaction table. Because I am relating it to where I am storing the ITEMS:: If I could see it only once with cumulative results it would totally serve the purpose. I have looked at this document from FileMaker, would you say this is recommended way to do it, or is there some way that makes better sense? Another thing is that, I always getting away with this in FileMaker, but can using other tables fields in a portal that reach through the relationships get you in trouble, or is it simply if it works go with it (respectively)? Here it works and has in the past. I am using my ITEM:: tables fields (image container,  name, etc.) on the ACTION:: table portal. Thanks

              • 4. Re: Relationship Scrutiny
                philmodjunk

                That is one way to do it, but with the tables you have, it's not necessary. We should be able to use an occurrence of Item to filter things down to either one row per item or one row per combination of item and Tier.

                The layout context is critical. Is this layout based on Location or some other tabe?

                • 5. Re: Relationship Scrutiny
                  ChrisJohnston

                  Yes it is based on LOCATION::

                  • 6. Re: Relationship Scrutiny
                    ChrisJohnston

                    Could ExecuteSQL as calculation to SUM the results that is the matched field in the occurrence be helpful? I hoping your answer will involve me not always relying on that, because I feel learning FileMaker at the time I did (around version 12 release), having access to that I always rely on it. I wonder if it is not letting me see the possibility of what Table Occurrences can accomplish. Thanks

                    • 7. Re: Relationship Scrutiny
                      philmodjunk

                      ExecuteSQL is another option that you could use. It's a very powerful tool that often is the best option to use provided that you can get the SQL to work for you.

                      The nonSQL method that I had in mind works like this:

                      Location-----<Tiers--X---Item|Filter---<Action

                      Location::__pkLocationID = Tiers::_fkLocationID
                      Tiers::anyField X Item|Filter::anyfield

                      Item|filter::__pkItemID = Action::_fkItemID AND
                      Item|filter::gSelectedTier = Action::_fkTierID

                      The trick to this is that gSelectedTier is a global field. You would select--either manually or via a script, the TIerID for the tier where you want to see total on hand for each item in your portal to Action. A calculation field in Item would use the Sum function to sum the cBal field in Action with Item|Filter specified as the Context table occurrence. (There's a drop down to select the context in the specify calculation dialog.)

                      But for those fluent in SQL, an ExecuteSQL calc can simplify this combination of table occurrences quite a bit.

                      • 8. Re: Relationship Scrutiny
                        ChrisJohnston

                        That’s good to know because I seem to have much better luck with ExecuteSQL rather than complex table occurrences. I have had previous experience with SQL so it helps a lot. I was thinking that making that my “go to” as a way to solve things always might not be good. Here you give me a new perspective. I appreciate you spelling out for me a way not using ExecuteSQL, that way I can learn/discover ways to use one or the other method or combine the power of both to create a solution. I had already began testing ExecuteSQL queries with the Data Viewer and it seemed like the results were working to narrow it down. The final calculation I will post here so other can benefit.

                        I don’t know if here is the place, but I would like to inquire with FileMaker as to why all there ExecuteSQL documents don’t give any real syntax examples of how it used, just the typical programmer style example and notation. There are so many differences and intricacies as to how FileMaker ExecuteSQL is written and can be written, compared to that of SQL, that they need actual examples with the proper FileMaker syntax. I sometimes take a while to get what some of the documents explain. I would like to hear what other FileMaker users have to say about that. I want to know if I should make a post here about this or should I contact FileMaker via there tech support channels? Thanks

                        • 9. Re: Relationship Scrutiny
                          ChrisJohnston

                          Once a solution like this becomes functional It has to part of your strategy to decide how long to keep your LINES:: records, correct? Is it common to try to develop some way (backup, migration etc.) to keep all of it? Thanks

                          • 10. Re: Relationship Scrutiny
                            philmodjunk

                            Yes, it's a common practice to use Something such as Import Records to transfer the records to some kind of archive table. You can replace the records thus removed with a single "starting balance" record for each inventory item that puts the Sum of cBal into the "IN" field of this starting balance record so that your running "on hand" totals remain accurate.

                            Some systems also take a hybrid approach and each time a record in "Lines" is committed, the new "OnHand" total computed in Lines is copied over to your Items table to use for faster references. This has to be designed carefully as it has to handle all events on the transactions (lines) table that might change an inventory total including creating new records, changing existing ones and deleting records.

                            • 11. Re: Relationship Scrutiny
                              ChrisJohnston

                              I was assuming it would not be a big concern for this solution because it is for storage of our assets and such. That is opposed to an inventory of a business selling a lot of products line items. But now that have thought about it some, and if we still have one flashlight from 2014 left in 2030 we will have to have that LINE:: record to be accurate. Interesting…. and good! This is just the problem we are dealing with now, that is not knowing what we have and where when why. By the way, I know I said it already, but the suggestion and the tutorage, by linking to the post that explains how to use a Transaction Ledger, is already doing wonders for this solution. Thanks

                              • 12. Re: Relationship Scrutiny
                                ChrisJohnston

                                Is it a bad practice to modify line/transaction records? I am playing with an idea where modifying certain records seems like a good way to track what happened to them regarding things our database/solution has no connection with. For example we put things on eBay and donate to charity from time to time. If I go back to that record and set the 1 in to a 0 is that a bad practice? In another table (eBay:: or Charity::)I will track just those transaction ID’s. Because we don’t have them anymore, the mechanism we build will not show them in our content but relate to ware they went. We do have a create date and a modification date of the transaction log records we can access. Thanks

                                • 13. Re: Relationship Scrutiny
                                  philmodjunk

                                  It depends on what you need to do with the transactions data. One of the significant uses of the transaction table is that you can see your inventory change over a period of time as a way to better analyze re-order levels. (If you see transactions frequently dropping to 0 or nearly so, that suggests that you need to either reset your re-order level higher or to make a one time supplementary order. If you see a large amount on hand for extended periods of time, you might reduce your re-order level or cancel an upcoming order...)

                                  You'll need to consider how such changes affect your use of that table as such an edit doesn't leave as detailed a "history" of what took place. That loss of info may not be significant or it may be a potentially serious issue. You might just need a "notes" capability where you note the change and why you made it.

                                  • 14. Re: Relationship Scrutiny
                                    ChrisJohnston

                                    The advice to use the transaction ledger gave us a big jump. I see that it is needs to be developed correctly, which is in the developers favor so much. So far I think we had done so, and it is the reason it is working out so well. To continue developing correctly I said let me inquire about our new need here. We have discovered that it would be to our benefit to have a UNIT:: table and a physical label on all our items in database. We now can set the quantity in or out for our ledger by the processing of our items via our barcode scanner. This has went well, we now have more specifics about what we have. My question is, should there be some type of handshake (via relationship or other) between the amount of items in our UNIT:: table and the quantities listed in our ledger (in our case ACTION:: ). Or is the handshake just developing this correctly and making sure thing add up and remain accurate via the tools FileMaker provides?

                                    1 2 Previous Next