5 Replies Latest reply on May 22, 2012 3:32 PM by philmodjunk

    Relationship finds no records



      Relationship finds no records


      I have two tables Invoices and home. I want to have a value list for only those Invoices over a certain amount of money.

      What I have done is:

      1) created a calculation field "i_expensive" in Invoices to be 1 if the total is high and 0 if not.

      2) created a global field "g_highcost" in my home table set to be the dollar amount.

      3) defined a relationship between these fields.

      4) made a value list of "i_ID" fileds through the relationship with the home table. 

      What I don't understand is why there are no records in the value list. I've tried to create a portal to see what the issue might be but can't figure it out. I am sure I have the right table occurances. What have I missed?



        • 1. Re: Relationship finds no records

          What is the relationship?  Why did you bother to calculate the i_expensive field?  Why not just have the relationship g_HighCost < ValueDollarAmount?

          If you draw the portal on the Home layout and change the global value you should see the portal records change (you nay have to trigger a portal refresh to see the change)

          • 2. Re: Relationship finds no records

             Thanks Sorbsbuster.

            That's the confusing thing, the portal I drew has no records no matter how I change the global value or refresh the window. The only way to see any records is the change the relationship comparator from ">" to "X".

            I agree I didn't need to calculate the i_expensive field but I use it in other calculations and I'm having the smae issue with another, similar set up where the calculation field is not about the amount but about the number of related records from another table. In both cases I can't figure out why no related records are found.

            • 3. Re: Relationship finds no records

              You can't use a global value or an unstored calculation on the portal side of the relationship. Fields used to match records on the "many" side of a relationship must be indexed fields. Global and unstored fields are not indexed. (The index is what is used to locate matching records on the many side of a relationship.)

              • 4. Re: Relationship finds no records

                 Of course, thanks Phil. The sneaky checkbox for storing results!

                • 5. Re: Relationship finds no records

                  A checkbox that won't be available if your match field is a calculation that includes a reference to a global field.