14 Replies Latest reply on Feb 22, 2013 8:38 AM by philmodjunk

    Need help with calculation / relationship / value list...not even sure what to call it!

    vincel

      Title

      Need help with calculation / relationship / value list...not even sure what to call it!

      Post

           I'm trying to figure out how to make this work (if it's even possible).   I have the following fields setup as value lists that are used for data entry

           Shirt type - examples are short sleeve, tank, long sleeve, etc

           Shirt Size - L, XL, S, etc

           Number of shirts - any numeric value

           State - All the US states

           Now after entry is done I need to take this data and reference a chart (currently offline) to get an inventory value per record.   I have a different chart for each state.

           So for instance 4 short sleeve shirts that are large in size and are in stock in Arizona have an inventory value from my chart of $4.00.   Any change to any of the fields changes the value of the inventory.  There is no mathmatical equation to calculate this, the chart must be referenced for the exact value I need.

           Is there a way to have FM do this search for me?

            

        • 1. Re: Need help with calculation / relationship / value list...not even sure what to call it!
          philmodjunk

               It sounds possible, but what tables, relationships have you set up to track inventory? Or is setting up those tables and relationshps the first thing we need to do here?

          • 2. Re: Need help with calculation / relationship / value list...not even sure what to call it!
            vincel

                 I haven't setup anything yet.  I figured I'd setup a new table with these fields listed and then create a relationship between those fields in the new table and the fields in the existing data entry table.   But I haven't tried to do that yet, I was waiting to see the best way to do that based on what i'm trying to accomplish.

            • 3. Re: Need help with calculation / relationship / value list...not even sure what to call it!
              philmodjunk

                   Describe the make up of that "chart". We'll need to reproduce it via a table in your database.

              • 4. Re: Need help with calculation / relationship / value list...not even sure what to call it!
                vincel

                     When I say chart I guess I should be referencing it as a table.   I plan to create an excel file which i'll export to FMP to use as the table that will need to be referenced to look up the data.

                     It'll have a column labled "shirt type", one labled "shirt size" one labled "number of lamps" and one labled "inventory value"   Each state will have a different inventory value based on the selection of the first three selections.  So i'm not sure if this same table would have "state" columns or if each state would be a seperate table. 

                • 5. Re: Need help with calculation / relationship / value list...not even sure what to call it!
                  philmodjunk

                       Don't get too hung up on appearances for the chart. I'm just trying to determine what the structure of a single record in the table used to produce this will look like.

                       So far, I have the following fields in a single record of this table:

                       shirt type, shirt size, number of lamps (??), inventory value, State

                       What kind of values whould be entered as a "shirt type"?

                       Might there be some kind of inventoryID field that uniquely identifies either each shirt type or each unique combination of shirt size and shirt type?

                       Is "inventory value" the value of a single shirt or all the shirts in inventory for that state?

                  • 6. Re: Need help with calculation / relationship / value list...not even sure what to call it!
                    vincel

                         oops..should be number of shirts....not number of lamps.

                         "shirt type" would be for instance....short sleeve, long sleeve, tank top, button down, polo, etc

                          

                         not sure on the inventoryID field but my thiking was if I was able to create a unique "code" for each combination I would allow me to then find that "code" on a table to find the inventory value.   But I wasn't sure if that was the best or easiest way to create...nor do I know how to do that.

                         "Inventory value" is the value of a single shirt x quantity of shirts in 1 record.  That value changes based on the state it's in

                    • 7. Re: Need help with calculation / relationship / value list...not even sure what to call it!
                      philmodjunk

                           Yes, a unique code for each Shirttype--size combination is usually the simplest option to use. But what concerns me is how general your shirt type value is in your examples. Couldn't you have two completely different shirts that are both "Long Sleeve, size XL"? and if so, shouldn't they be different items on your "chart"?

                           Since the quantity changes by the location, I can see that the value would also change. But is the value of a single shirt the same in all locations?

                           Revised fields as I would understand it at this moment:

                           __pkShirtID (serial number that identifies one specific shirt type, size, etc)
                           Shirt Type
                           Shirt Size
                           Qty
                           State
                           cInvValue  (calculation field, Qty x UnitValue )

                           UnitValue would be either one more field in this table (if the value of one shirt differs from state to state) or a field from a related table (if it is the same).

                           From where will you get these values?

                           Will you simply type them in or import them from another source?

                           Or would these values be computed from various events that will change your inventory such as: Sales, Shipments recieved, Shrinkage, Return To Vendor, etc. ?

                      • 8. Re: Need help with calculation / relationship / value list...not even sure what to call it!
                        vincel

                             Yes you are correct that I could have multiple combinations that have the same data....i'll need to get more descriptive on my values so that doesn't happen.

                             The value of a single shirt is different per location

                             The values will be typed in as the inventory is taken

                             thanks for all the help!

                        • 9. Re: Need help with calculation / relationship / value list...not even sure what to call it!
                          vincel

                               How do I go about creating the __pkShirtID field?  I'm not sure how to create the function to have it look at the data entered in the other fields to create the code

                                

                          thx

                          • 10. Re: Need help with calculation / relationship / value list...not even sure what to call it!
                            philmodjunk

                                 Typically, a primary key field (often labled with __pk in my examples), is an auto-entered serial number field. In FileMaker 12, it could also be a text field with Get ( UUID ) used as the auto-enter calculation. It's purpose is to uniquely identify each record in your table.

                                 It would appear then that this is the table that will do what you have requested:

                                 __pkShirtID (serial number that identifies one specific shirt type, size, etc)
                                 Shirt Type
                                 Shirt Size
                                 Qty
                                 State
                                 cInvValue  (calculation field, Qty * UnitValue )
                                 UnitValue

                                 In a list or table view, this table will list all shirts, their quantities, the state, and the total value for that state.

                                 You may find this thread on inventory management helpful: Managing Inventory using a Transactions Ledger

                                  

                            • 11. Re: Need help with calculation / relationship / value list...not even sure what to call it!
                              vincel

                                   Thanks for the info and I'll check out the link.   I'm still having difficulty understanding how the _pkShirtID is going to be able to reference a table to lookup the UnitValue field?

                                   The UnitValue field is what will vary depending on the combination of the other fields.   

                              • 12. Re: Need help with calculation / relationship / value list...not even sure what to call it!
                                philmodjunk

                                     There are many different options. The best option depends on how you want to use this table of records.

                                     _pkShirtID is set up to be used as the match field in relationships linking this table to other tables.

                                     I would think that the main value you'd want to access is the cInvValue field.

                                     The Unitvalue records the value of one shirt in each of the records. Each record is for a different shirt, size, state combination.

                                • 13. Re: Need help with calculation / relationship / value list...not even sure what to call it!
                                  vincel

                                       It's starting to make sense in my head.   One thing i'm missing though.  

                                       If I create the _pkShirtID and have it randomly create a unique code for each combination how would that reference the correct information from the table I have setup to look for that code to get the cInvValue ?  

                                       I understand I need a code with a relationship but feel like I need codes for every combination possible up front so that I can tie those to the correct information in the lookup table.   

                                  • 14. Re: Need help with calculation / relationship / value list...not even sure what to call it!
                                    philmodjunk

                                         To repeat, the implementation details depend on how you want to use the data in this table. You have not posted that information here in this thread. So I cannot easily provide a detailed answer for you.

                                         One way is to define a value list that lists __pkShirtID as the first field in the value list and some second field from the same table is listed as the second field. That second field could be a description field or a calculation field that combines the data from several fields in your table to provide unique text that the user can use to select a value.

                                         And this can be a conditional value list where you select a state in field 1 and then field 2 lists only the shirts specified for that state.

                                         This data can also be displayed in a portal, copied into a new record of a related record, displayed in a list or table view after a find is performed...