8 Replies Latest reply on May 9, 2011 10:49 AM by DavidErickson

    Simple relational question

    DavidErickson

      Title

      Simple relational question

      Post

      I thought this could be done without a problem, but perhaps not.  I have an orders table that has date fields for ENTERED DATE and DUE DATE.  I'm trying to build a calendar to view these dates on a daily basis (i.e. what was entered today and what is due today) .  I should be able to relate a single date field in a calendar table to two different date fields in the orders table, no?

        • 1. Re: Simple relational question
          philmodjunk

          Possibly not, but then I don't know what you want to see in your calendar.

          Do you want to see both entered dates and due dates in the same box in a single list?

          You may need to create a secondary table of dates where one record is either an "entered" date or a "Due" date for a given record in your orders table. When you enter a date on your orders layout a pair of relationships, one for each file can be set up so that you are actually creating a related record in this table where you then enter the date. Since this is now a unified table of date records, you can then display these dates in portals where each portal displays all the date records for that day, both "entered" and "due" dates combined.

          If you are interested in examining a simplified calendar demo file:

          http://www.4shared.com/file/6UAOV_RZ/Calendar.html

          • 2. Re: Simple relational question
            DavidErickson

            Thank you, good question.  I do not need to see them on the same list, but I do need to see them on the same day.  My calendar view will most likely be a single day (as opposed to a month).  On that day record  I need only to see a list for those entered today and a list for those due today.  Does this scenario require a separate table for each date type?

            • 3. Re: Simple relational question
              philmodjunk

              It depends on what you want to see. To get a list that looks like the following, you'd need a common table:

              Order #:  Type
              1234       Entered
              345         Due
              3456       Entered
              8764       Entered
              9999       Due

              I think, though, you'd want this type of layout setup:

              Orders Entered:

              1234
              3456
              8764

              Orders Due:

              345
              9999

              That's really two separate lists for the same date. With that layout design, you could place two portals on your layout, that each use different relationships to link to your orders table. One relationship would link by the "entered" date field and the other by the "Date due" field.

              • 4. Re: Simple relational question
                DavidErickson

                I do prefer the latter.  Here's where I'm having trouble.  I currently have a dates table and it auto populates each day to add the current day to the table.  This table relates back to the orders table with a relationhiop of ENTERED DATE --> CALENDAR DATE.  This relationship works fine and I can view records and summarize data for the ENTERED DATE.  When I tried though to add second relationship for the field DUE DATE, it allowed me to do so (I had to create the second....I guess it's a dummy table called "Calendar 2"), but no records appear in the portal.

                Perhaps I have the portal configured incorrectly?  Currently it is set to view records from the ORDERS table with the filter defined as ORDERS::DUE DATE = CALENDAR2::DATE

                • 5. Re: Simple relational question
                  philmodjunk

                  That "dummy table" is what we call a "table occurrence". Wink

                  You need to turn things around here, since I assume your layout is based on Calendar. You need one ocurrence for calendar and two for Orders so that you can link two the two date fields in Orders from the same record in Calendar.

                  Calendar::Date = OrdersByDateEntered::DateEntered

                  Calendar::Date = OrdersByDateDue::DateDue

                  You can then place portals to OrdersByDateEntered and to OrdersByDateDue on your Calendar layout to display the two lists of records from the Orders table. To rename a table occurrence from Orders 2 to OrdersByDateEntered, double click it. (Note: If you need to add fields from tables related to orders inside your portal, you'll need to create additional table occurrences for them and link separate occurrences of them to both OrdersByDateEntered and OrdersByDateDue.)

                  If Table Occurrence is a new  concept, you might want to read this tutorial: 

                  Tutorial: What are Table Occurrences?

                  • 6. Re: Simple relational question
                    DavidErickson

                    wow, thank you Phil.  That was damned easy.  The portal is working fine now.  Why is it that I had to flip it like that?

                    • 7. Re: Simple relational question
                      philmodjunk

                      Portals work from the values of the current record of your layout and that current record is controlled by the Table Occurrence, Calendar, that you specified for your layout in Layout Setup|Show Records From. Since you needed two lists of orders records for the same record in Calendar, your relationships needed to start with a common record in Calendar and then link to different sets of records in each portal--which in turn needed to be different table occurrences of orders.

                      You may find that easier to understand after playing around with the tutorial I suggested. It's designed to both explain Table Occurrences and also to illustrate how they, in turn control the function of nearly every asplect of your database. There's also a few "tricks of the trade" demo'd along the way...

                      • 8. Re: Simple relational question
                        DavidErickson

                        Thank you again Phil.  You've been most helpful.