4 Replies Latest reply on Jul 3, 2015 7:34 AM by philmodjunk

    Link to tables dependent on choice?

    weedonpaul

      Title

      Link to tables dependent on choice?

      Post

      I don't know if the following is possible:

      We currently run reports called "status tables" this is a list of projects that we are working on for our clients. these might be articles, events, press releases, awards, social media, etc. We have separate tables for all these things. at the moment if I want to add an event to a clients status table I would add it to the status table  and separately add it to the events table. the same with articles etc.

      As I am rebuilding the database I would like to have a table called "status table " and a table for each of the others: Events, press releases etc, but I want them to link so that for example I would put a new entry into the status table and when I select events from the dropdown menu it then gives me a list of upcoming events to choose from or create a new event, but if I choose press release it gives me a list from current press releases or lets me create a new one.

      but I want the information in the status table to show information from the relevant table. e.g. a details field that will get details from events record if it's an event, or get details from press releases if it's a press release. 

      I haven't started building this part yet so would love some guidance/ideas

        • 1. Re: Link to tables dependent on choice?
          philmodjunk

          You might want to keep in mind the basic principal behind relational database design: Except for IDs used to link to other records, never store the same data in more than one place. Instead, you link to it and use the link to access the data in the location where it is stored.

          So generally speaking, you wouldn't use one table to store a copy of data from another, you'd use a relationship link to access the data from the table where the specified data is stored.

          What you describe appears to be a classic design issue that we all have to deal with. There are no concrete "always do it this way" answers as the details of your design requirements as well as your personal preferences will determine the final choices you make. The issue is whether to use different tables for similar but different types of data or to use a single unified table for all of the data.

          There are two basic approaches possible with a "compromise" approach that splits the difference between the two.

          a) Use a single unified table. This basically treats your entities such as "articles", "press releases", "awards" all as different kinds of events. If one of these requires that you record distinctly different data, you simply add the extra fields needed and any one type of record only uses a subset of the total number of fields available. Different layouts or different panels of a tab control can use different arrangements of these fields to present data from the different types to the user. This simplifies your data model and makes many forms of reporting much simpler, but you can end up with a lot of fields in one table with only a few used the same way in every record. This won't affect the user, but can make your work designing and maintaining the database a bit of a challenge.

          b) Use separate tables for each. This gives smaller simpler lists of fields and layouts that need no extra assistance from a script to limit a layout to just records for a specific category of your data. The main downside is if you need a layout or report to list records from multiple tables all in the same view or report, you will have a lot of trouble doing so.

          c) This is the "compromise" between a and b. You carefully analyze your different entities ("article", "award", "event") to determine which fields are always used in all entities. In your case, I'd guess that you'd need at least a descriptive name, at least one date and a status field. You may or may not have other fields. Then you use separate tables for the fields that are unique to just one entity and link them in relationships to your central table. Your central table then consists of that list of common fields, a "type" field plus one foreign key field for each "detail" table.

          I would guess that you are headed for "C" here. But when you look at the fields you need for each entity, if you find that all or nearly all of the same fields will be needed for each, then "A" starts making more sense.

          That's the data model side of the issue. The other half is how you design your layout to work with this data. There are many different options here--especially if you are using FileMaker 13 or newer as it adds popovers and slide controls to your list of design options. (You might have a popover with a slide control inside it with one panel for each type of entity used to collect data for a new related record in that entity's table.)

          One option would be an Unstored Calculation field set up in your central table with this kind of calculation:

          Case ( Type = "event" ; Events::EventDetails ;
                     Type = "Article" ; List ( Articles::ArticleSource ; Articles::Author ; Articles::Synopsis ) ;
                     and so forth...
                    )

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Link to tables dependent on choice?
            weedonpaul

            wow, that must have taken ages to write. Thank you so much. I will sit down tonight with a pencil and a sheet of A3 paper and plan I think option 3 is the way forward, but I'll keep an open mind.

            again, thank you

            • 3. Re: Link to tables dependent on choice?
              weedonpaul

              As certain events have more than one client I decided I would have many to many relationships Option C was better, I will deffinatly use the case function in the status table.

              is there a way to have a dynamic list that depends on choices?

              so an example would be: in status table user chooses press release, user then gets another list to select the press release from a list of current press releases. but if they choose events they would get a list of current events to choose from. Once they had made their choice the details can be displayed using the case function.

               

               

              • 4. Re: Link to tables dependent on choice?
                philmodjunk

                Such a dynamic list can be a list of records in a "selection portal" or a list of values in a value list. The latter option is called a "condtional value list".

                To learn about conditional value lists, see:

                "Adventures in FileMaking #1 - Conditional Value Lists".

                To learn about selection portals and other options for helping a user pick values from a list see:

                "Adventures in FileMaking #2 - Enhanced Value Selection"

                These are exploration files with both fully working examples of these methods combined with detailed descriptions of how they are set up and how they work.

                Caulkins Consulting, Home of Adventures In FileMaking