7 Replies Latest reply on Aug 24, 2012 1:56 PM by philmodjunk

    Lookup values vs. using table field

    stevaroni

      Title

      Lookup values vs. using table field

      Post

      I'm new and working on my first FM12 database. As I understand it, I can lookup a value from a related table if I don't want a value to change when the related table is changed, or I can simply use the fields from the related table in a layout for another table if I want the values to be dynamic. Here is my question: If I use fields from another table to display on a layout, how will the information work upon export? I want the related data to be part of the export, so I'm assuming I need to do a lookup so that data from the related table actually resides in the table I am exporting.

        • 1. Re: Lookup values vs. using table field
          philmodjunk

          When you export your data you can still refer to fields of a related table. There is no need to copy the data via a look up just to be able to export it.

          • 2. Re: Lookup values vs. using table field
            stevaroni

            So then I assume that in the export process, the database looks at the relationship to match up the correct data from the related field? That makes things easier than I thought. That being the case, then the ONLY reason to use lookups would be if you do not want dynamic data from a related table--right?

            • 3. Re: Lookup values vs. using table field
              philmodjunk

              That's the main reason. Sometimes, one copies data from a related record so that they get faster searches and sorts. This is usually not necessary unless you have really large data sets or very slow network connections or some other extenuating circumstances.

              • 4. Re: Lookup values vs. using table field
                stevaroni

                So, I made the changes--but now my drop downs using conditional value lists are not working. I'm guessing there's something I'm not understanding about how tables work. Using an Excel spreadsheet for example, I'm supposing a table is like an entirely separate Excel spreadsheet. You outline a relationship so the current table knows how to relate to and pull data from the other spreadsheet. Is that the idea?

                • 5. Re: Lookup values vs. using table field
                  philmodjunk

                  That's the starting point, but "context" is also important.

                  The layout you are on establishes a "table occurrence context" that controls what data is accessible via relationships.

                  In Layout Setup..., you'll find that a table occurrence is selected in the "show records from" drop down. A table occurrence is one of the "boxes" found in manage | Database | relationships and each such "box" refers to one specific data source table. Each table occurrence may then be linked to any number of other table occurrences via relationships.

                  Conditional value lists usually rely on the "include only related values, starting from..." setting in manage | value lists. So they do rely on a relationship to control what values appear in the value list and most often, the layouts table occurrence is the one selected in the "starting from" drop down. Thus a value lists that works on one layout, may fail on a layout based on a different table occurrence due to the resulting change in context as that can change how the relationship on which the conditional value list is based functions.

                  • 6. Re: Lookup values vs. using table field
                    stevaroni

                    I'm learning. Thanks for the help!

                     

                    So I found that if I take a field that has a drop down list based upon a simple value list (that works properly) and put that field on a layout from another table, it does not work. Are value lists somehow "table occurrence context" related also?

                    • 7. Re: Lookup values vs. using table field
                      philmodjunk

                      It's not a problem with the value list, more likely, the field's table occurrence won't work in the context of the layout's table occurrence.

                      When you select a field in layout mode and check what is listed in the "display values from" box in the inspector's data tab, the text to the left of the :: is the field's table occurrence name. To understand the results you've gotten when you then copy and paste that field object onto a layout based on another layout, you'd have to find the layout's table occurrence and the field's table occurrence in Manage | Database | Relationships and see how they are linked in a relationship, if they are linked at all.