6 Replies Latest reply on Nov 12, 2013 2:35 PM by JeremyWhitted

    Can't figure out how to get this relationship to work properly

    JeremyWhitted

      Title

      Can't figure out how to get this relationship to work properly

      Post

           I'm stumped. I thought I had it right, but apparently not. I have two tables (relationship graph attached). One table is a series of unique registrations (childlist). The other (srmr) has monthly reports as records, one report per month per child (from the first table). What I'm trying to do is show fields from srmr on the childlist layout that matches CSR_CaseID to srmrcaseid AND CSR_ReportDate to srmr_report_date (those dates should match exactly).

           Here's the problem I'm having. I have a record in the childlist table that has 3 records in srmr, each one for a different month. I want the values from one of those to appear on the layout, based on whatever month is shown in the ReportDate field. But I can't get anything other than the first record in srmr to display, regardless of the value of CSR_ReportDate.

           What am I doing wrong here? Is there something I have to do to trigger the values to update? In the past, changing the value of one field would change what I needed. But I've never tried when I'm matching two sets of fields.

           Any help would be greatly appreciated. I've been searching for a couple hours with no luck. I'm pretty sure I'm missing something simply, I just can't find it.

      Screen_Shot_2013-11-12_at_2.01.35_PM.JPG

        • 1. Re: Can't figure out how to get this relationship to work properly
          philmodjunk
               

                    But I can't get anything other than the first record in srmr to display, regardless of the value of CSR_ReportDate.

               Does that mean that you always see data from the same record no matter what value is entered/selected For CSR_Report date?

               And fields from the related table are placed directly on the childlist table? (not in a portal?)

               If so, I'd check to make sure that the fields are really from srmr and not some other occurrence of the same data source table. When you select one of the fields, the  Display Data From box in the Inspector's data tab should start with this text: srmr:: followed by the name of the field.

               I'd also check the data types for CSR_ReportDate and srmr_report_date to make sure that both are of type date, but issues with the exact match field data and match field data types would be more likely to set up a situation where you don't see any data in the fields from the related table instead of always the first record--which is typical of a relationship that matches only by the caseID fields instead of date and case ID fields.

          • 2. Re: Can't figure out how to get this relationship to work properly
            JeremyWhitted
                 

            Does that mean that you always see data from the same record no matter what value is entered/selected For CSR_Report date?

            Yep. It's always the same data regardless. It's the first of the 3 records in srmr.

                 

                      If so, I'd check to make sure that the fields are really from srmr and not some other occurrence of the same data source table. When you select one of the fields, the  Display Data From box in the Inspector's data tab should start with this text: srmr:: followed by the name of the field.

                 Double checked. It is definitely from the srmr table.

                 

                      I'd also check the data types for CSR_ReportDate and srmr_report_date to make sure that both are of type date

                 I did find that one of them wasn't a 'date' type, but changing it didn't seem to fix it.

                 

                      but issues with the exact match field data and match field data types would be more likely to set up a situation where you don't see any data in the fields from the related table instead of always the first record--which is typical of a relationship that matches only by the caseID fields instead of date and case ID fields.

                 Yeah, that's what I would have expected. I tried deleting one of the records in srmr to see if that would toggle something, but I'm just getting one of the other two records, regardless of the date match. At this point, that seems to be the sticking point. Doesn't seem to be matching the two date fields and changing the data accordingly.

            • 3. Re: Can't figure out how to get this relationship to work properly
              philmodjunk

                   Hmmm, what you describe is, in a word, impossible. Therefore, some detail has been overlooked.

                   Can you upload a screen shot of your layout, taken in layout mode, showing the fields in question? Please include the status tool bar in the screen shot and if you have "Show sample data" selected from the view menu, please deselect this option so that actual field names are showing in the screenshot.

              • 4. Re: Can't figure out how to get this relationship to work properly
                JeremyWhitted

                     I found it. With your help, so thanks! When I was double checking the field types on the date, it still wasn't working. But then I got to thinking that the CSR_ReportDate field was a calculation from another field. It was still returning a date type value, but it wasn't working. I removed the calculation and manually changed the date, and it worked properly.

                     So apparently, that match field can't be a calculation. Not a big deal. I can work around that. Thanks for the help!

                • 5. Re: Can't figure out how to get this relationship to work properly
                  philmodjunk

                       The match field can be a calculation, I use that all the time, but sometimes there can be an issue in getting the value and layout to update smoothly when editing a field from the related table referenced by the calculation field. It may be that all you needed was to commit records on your layout or do a refresh window script step after editing such a field.

                  • 6. Re: Can't figure out how to get this relationship to work properly
                    JeremyWhitted

                         That's good to know. I'll give that a shot and see if I can make it work. Thanks!