5 Replies Latest reply on Aug 6, 2009 2:52 PM by BobSchwenkler

    Relationship problem- date match fields

    BobSchwenkler

      Title

      Relationship problem- date match fields

      Post

      Hi,

       

      I'm having an issue getting a couple match fields to work properly and I'm wondering if there's just something simple that I haven't learned about that would keep this from working.

       

      I'm joining a chart of accounts with a line items list and summing debit and credit totals for the accounts. First match field is the account number. Works fine. In this configuration the grand totals for the accounts display.

       

      In the chart of accounts table I've also created global dateStart and dateEnd fields so that I can summarize data from within specified time periods. Match fields on the other side are calculation fields that copy over the date from its parent record. Any sort of additional match that I create using date fields damages this relationship such that no summary is acquired.

       

      Is there any potential issue in relating calculated dates to global date fields? Some other thing I might be missing? Thanks!

        • 1. Re: Relationship problem- date match fields
          philmodjunk
            

          I think this is what you are asking for:

           

          Create a new table occurrence (use the button with two overlapping green plus signs) and set up your relationship as

           

          Table1::datestart < newtableTO::date AND Table1::dateened > newtableTO::date AND Table1::AccountNumber = newtableTO::AccountNumber.

           

          I'm suggesting the new TO because that may be your problem. If you have one relationship between the two tables based on account # only, then you need a new table occurrence for the date range relationship.

          • 2. Re: Relationship problem- date match fields
            BobSchwenkler
              

            So the description of the relationship you've made is the same as I've tried to do with no success.

             

            I've created brand new TOs for this layout that I'm working on, so there is no other previous functionality that can be damaged by changing the relationship criteria, if that's at all what you're inferring might be the case.

             

            Simply put, the above described relationship is not working for me. When I add date into the picture it ceases to function.

            • 3. Re: Relationship problem- date match fields
              philmodjunk
                

              Then it's time to check more pesky but vital details to see why it doesn't work:

               

              Are the date fields on both sides of the relationship of type "date"? If one is a text field, the relationship won't work.

               

              is the non-global date field a stored, indexed field? You can check this by looking at it's field definition.

               

              If all that seems to be correct, try posting a screen shot of your relationship graph and the relationship options dialog, maybe there's something there. (Screen shots have to be posted to a different site and then you put a link to it here in your post.)

               

              If all else fails, I'll be glad to take a look at your file and see if I can spot the problem.

               

              Check your private messages for a message from me with an email address you can send a clone of your file to.

              • 4. Re: Relationship problem- date match fields
                comment_1
                  

                Bob Schwenkler wrote:

                Match fields on the other side are calculation fields that copy over the date from its parent record.


                You cannot use an unstored calculation field as the matchfield on the child side of a relationship. Your overall structure is not entirely clear to me (which table has the account number, and which the date?), but perhaps you could use the parent table in the relationship chain.


                • 5. Re: Relationship problem- date match fields
                  BobSchwenkler
                     Ok, unstored calculation field was the problem. I thought it might be some such thing. Thanks for the help!