1 2 Previous Next 15 Replies Latest reply on Apr 13, 2016 7:53 AM by CharlieS

    Creating multiple lookups to Static Reference table for values to be calculated.

    CharlieS

      Hello,

       

      I'm fairly new to FileMaker. I have been charged with becoming the resident expert and updating several systems from FileMaker 5 to 12/13.

       

      Here is my problem.

       

      We have one table "Operations". In it are 10 fields  (Additional Operation1, 2, ...). They currently use a value list to an unrelated table "List_Operations" to choose from 73 choices (records). Currently, all it does is fill the field and there are calculations that have to be entered manually. I want to be able to add the static numbers to the "List_Operations" table, and then get/lookup the corresponding field(s) there to automate those calculations (Additional setup hours, machine hours, etc.). These values would not change. So I don't think a join table between them is the right solution as the table on one side remains constant and is only used for reference.

       

      Does anyone have a similar setup or some help?

       

      Thank you,

       

      Charlie

        • 1. Re: Creating multiple lookups to Static Reference table for values to be calculated.
          keywords

          If a table exists purely for the purpose of generating a Value List it is not necessary to set up a relationship in order to be able to use that list elsewhere in the database.

          The attached demo file is set up according to your description, as near as I can make out. Perhaps it will help to point the way.

          • 2. Re: Creating multiple lookups to Static Reference table for values to be calculated.
            CharlieS

            That is basically how it is set up right now. What I want to do is add other static fields to the List Table so I can automatically calculate totals based on these selections.

            • 3. Re: Creating multiple lookups to Static Reference table for values to be calculated.
              erolst

              This is where you will have to create a relationship between Operations and List_Operations.

               

              Then you'll be able to define auto-enter options for the individual fields in Operations, which could be simply a reference to the respective field in List_Operations, or even a calculation based on that field (or any calculation, for that matter – which is a great advantage compared to the older Lookup feature that only lets you copy the related value or a static fallback).

               

              Make sure to set the “Do not change …” option to the setting that reflects the desired behaviour.

              • 4. Re: Creating multiple lookups to Static Reference table for values to be calculated.
                CharlieS

                I thought about creating a relationship, but what field(s) do I use to link? Should I link all ten AdditionalOperations to the field in List_Operations? That doesn't seem like a good idea.

                • 5. Re: Creating multiple lookups to Static Reference table for values to be calculated.
                  erolst

                  CharlieS wrote:

                  That doesn't seem like a good idea.

                   

                  That's because it isn't. Since you want to set these fields via a relationship, they cannot possibly be included in the definition of that relationship.

                   

                  Every record in the List_Operations (L_O) table should have a primary key, i.e. a unique, auto-entered serial that serves as a unique identifier (as should almost every table, unless it is intended as a 1-record control, dashboard, utility etc. table)

                   

                  Try this:

                   

                  Create a value list “using field values” with 1. field: L_O::primary key, 2. field: L_O::name (any human-readable name; this also must be unique, because a value list cannot display duplicates, and the next step will make this field the display field); check the option: Show second field only.

                   

                  In Operations, create a field that has the same data type as the primary key in L_O. Format it as a popover control* with the new value list. This will hold the foreign key which will make an O record a child of a L_O record (one L_O record, many O records.)

                   

                  [*For “technical reasons”, you cannot use a dropdown control; there are workarounds, though.]

                   

                  Create the relationship between these two fields. Selecting a list name from the new field will actually set the foreign key to a primary key from L_O, point at the matching record from L_O (since the key is unique in L_O, this can only be a single record), and the auto-enter options can read the values from the specified fields in that L_O record.

                  • 6. Re: Creating multiple lookups to Static Reference table for values to be calculated.
                    CharlieS

                    I still do not see how this will allow me to refer to multiple records in L_O from each O record. I still have to be able to lookup the other values for each of the 10 AdditionalOperations. Each record in L_O contains the info for one AdditionalOperation.

                    • 7. Re: Creating multiple lookups to Static Reference table for values to be calculated.
                      CharlieS

                      Maybe I'm not explaining my setup sufficiently.

                       

                      Think of Operations as a Grocery Bag of sorts and List_Operations as a list of all available groceries.

                      You have the option of selecting up to 10 grocery items to put in the bag.

                       

                      I need to know the price from List_Operations of each grocery item selected.

                      • 8. Re: Creating multiple lookups to Static Reference table for values to be calculated.
                        erolst

                        CharlieS wrote:

                        I still have to be able to lookup the other values for each of the 10 AdditionalOperations. Each record in L_O contains the info for one AdditionalOperation.

                         

                        Why, how nice of you to reveal that little detail … until now (at least) I thought that each record in L_O was a set of fields that matches the 10 fields in O.

                         

                        Then, I'm afraid to say, your data model is flawed (non-normalized), for which names like of field_1, field_2 are usually a dead giveaway …)

                         

                        What you should have is a setup of

                         

                        Operations --< AdditionalOperations -- Operations_List

                         

                        where each of your 10 additionalOperations fields now is a single record in AdditionalOperations, and you would match them by something like operationType and #addition (which you store in the lookup and can calculate for the data table).

                         

                        btw, you could still do it with your existing setup. In order for this scheme to make sense in the first place, the lookup (L_O) records need to have some group. list etc. identifier (every 10 of them; btw, how is 73 a non-decimal multiple of 10?) and an individual identifier (option#1, 2 etc.)

                         

                        So you need a value list of group identifiers, and each field in O needs to know its identity. If you set the match field in O to the group number, and sort the L_O side of the relationship by individual identifier, you could use

                         

                        GetValue (

                          List ( LO_byGroup::valueToBeLookedUp ) ;

                          GetAsNumber ( GetFieldName ( self ) )

                        )

                         

                        Or use this approach which doesn't need any relationship and is robust against gaps / missing values:

                         

                        ExecuteSQL ( "

                        SELECT valueToBeLookedUp

                        FROM List_Operations

                        WHERE

                            groupID = ? AND

                            optionIdentifier = ?

                          " ; "" ; "" ; Operations::groupID /* the popup field */ ; GetAsNumber ( GetFieldName ( self ) )

                        )

                         

                        All this is a bit awkward and relies on fields having proper names etc.; all in all, a proper, normalized structure is the better long-term solution.

                        • 9. Re: Creating multiple lookups to Static Reference table for values to be calculated.
                          CharlieS

                          73 doesn't have to be a multiple of 10. They can choose any 10 from the list of 73 possible operations. They don't have to choose 10 additional operations. They don't have to choose any.

                           

                          Are you saying I would need a unique identifier for each possible group of 10? That's over 700 billion possibilities!

                          • 10. Re: Creating multiple lookups to Static Reference table for values to be calculated.
                            erolst

                            CharlieS wrote:

                            73 doesn't have to be a multiple of 10. They can choose any 10 from the list of 73 possible operations. They don't have to choose 10 additional operations. They don't have to choose any.

                            Are you saying I would need a unique identifier for each possible group of 10? That's over 700 billion possibilities!

                             

                            Nope; but if this is a free-for-all, without any notions of groups or sets, then you really need one relationship per field (and one additional field per data field to hold a key) – which means a better structure is the way to go.

                             

                            Have a look into the attached example … The Ugly (no offence) is your field-based structure, The Good is the record-based structure.

                             

                            A set is defined as 10 add. operations, but you could program this to simply allow a user to add an operation (a new related record) on demand (1 or a user-specified number)

                            • 11. Re: Creating multiple lookups to Static Reference table for values to be calculated.
                              CharlieS

                              I think I understand, but I haven't delved that deep into portals. How would I then refer to the values for use in my calculations within Operations?

                              • 12. Re: Creating multiple lookups to Static Reference table for values to be calculated.
                                erolst

                                Depends. I don't know what you do now, and how you do it.

                                 

                                But you can address every single related record, e.g. using GetNthRecord(), and you can use summary functions to aggregate them, or use summary fields within the related table.

                                 

                                And it's much easier than with individual fields: compare

                                 

                                Sum ( numberField1 ; numberField2 ; … ) with

                                 

                                Sum ( RelatedTable::numberField )

                                 

                                which will work regardless of the number of related records and doesn't have to be adjusted to accommodate new numberFields.

                                 

                                Note that reporting is also much easier.

                                 

                                I suggest you delve into relationships, because if you don't use them and structure your data model accordingly, you might as well store your data in an Excel worksheet. (Nothing against Excel, but it isn't a relational database system.)

                                • 13. Re: Creating multiple lookups to Static Reference table for values to be calculated.
                                  CharlieS

                                  Thank you very much for your help.

                                   

                                  I do use many relationships in the database to great effect. It's just that certain complex many-to-many relationships puzzle me as to how to set them up. I guess I just haven't done enough of them yet.

                                  • 14. Re: Creating multiple lookups to Static Reference table for values to be calculated.
                                    BruceRobertson

                                    Don't perpetuate the mess. Don't ask us to spend time understanding or adapting to or jawing about your deficient structure. Normalize first, THEN use relationships.

                                    1 2 Previous Next