4 Replies Latest reply on Oct 9, 2014 1:33 PM by philmodjunk

    Table structure question

    WF7A

      Title

      Table structure question

      Post

      Greets, all:

      I'm a little perplexed so I need your help; please refer to the attachment.

      I have four "domains" that I want to create a parent-child relationship since, as you can see, each domain has multiple fields which will be used in summary/aggregate reports later. (Each red square is a field where users would enter a number from 1 to 4; they're named D1a, D1b, D1c...D3c, D3d, D3e.) Since there's a one-to-many relationship I need to create parent-child tables.

      Here's the question: Should I create a separate child table for each Domain, e.g., Domain1 would store only the Domain 1 data, Domain 2 for only Domain 2 data, etc., or should I have one child table to store all the data, it broken out by data field and Domain number?

      TIA for your input!

      Rich

      Screen_Shot_2014-10-07_at_2.02.03_PM.png

        • 1. Re: Table structure question
          philmodjunk

          Either option can be made to work and much depends on what you want to do with this data after it is recorded before one option clearly is the better option than the other.

          My best guess is that the data for all four domains should be recorded in the same table, with a "domain" field used to distinguish Domain 1 data from Domain 2 data and so on. Such a Domain field could be used with portal filters or as an added match field to limit each domain portal to records from just one Domain.

          • 2. Re: Table structure question
            WF7A

            Thanks!

            • 3. Re: Table structure question
              WF7A

              Okay, I'm stuck after trying a few times to get this to work via a portal. How do I "push" the data into the child table (which, aside from the requisite ID fields, contains three fields to record the users' entries: Data (used to store the numerical value entered in a box), HeadingNumber (1a, 1b, 1c, etc.) and DomainNumber which is a calc field that automatically inserts the Domain number based on the Heading number. Do I assign a script trigger (OnObjectModify?) on the Data box so it auto-enters the correct HeadingNumber into a new record, or...?

              TIA for your assistance!

              • 4. Re: Table structure question
                philmodjunk

                One option is to use a script that generates the portal records each time you create a new parent record on this layout. That's the easiest to explain.

                I've also been able to set this up so that the values listed in the portal come from a different table than the responses recorded. This requires a more complex relationship and some scripting to set up a match field that "reaches through" the table of response labels to the table of response data so that response data records link to a specific response label record and also to a specific parent record on the layout shown in your first post.