7 Replies Latest reply on Jun 23, 2011 11:35 AM by philmodjunk

    Lookup on a Summary Field

    techiejw

      Title

      Lookup on a Summary Field

      Post

      I'm using a lookup field to lookup a summary field on a child table - and I'm just getting blanks with no error message?

      I don't understand why it is allowing me to configure a field as such, but it just doesn't work for me - is there a suggested way of extracting data from a summery field from a related table to the current?

      Thanks so much Laughing

        • 1. Re: Lookup on a Summary Field
          philmodjunk

          What kind of summary field?

          Exactly what field options did you use to set up the look up? (Or are you using some other method to "look up" this value?)

          What relationship have you defined linking parent table to child table?

          • 2. Re: Lookup on a Summary Field
            techiejw
            Summary field is a count of companyID in the classstudent table, where company->classstudents (all together). The lookup field looks up the summary field from the company layout, current layout. Hope this is clear - Thanks!
            • 3. Re: Lookup on a Summary Field
              philmodjunk

              Not fully clear.

              Company matches to classStudents by what fields in the two tables?

              Is this an auto-entry looked up value field option? (Such settings do not look up from layouts. They look up from related records in a specified table occurrence.)

              If this is a looked up value auto-enter option, keep in mind that the looked up values are only looked up when you enter  or change the value in a field used as a key in the relationship specified in the look up. Subsequent changes to the records being summarized will not update this "looked up value". There are other options that will update automatically when a value is changed in the related table.

              • 4. Re: Lookup on a Summary Field
                techiejw

                OK - I made a mistake, so sorry. Let me start over :) K... You have helped me with this database before and you may remember:

                company->order->classstudent<-students where company->students

                In the order layout contains a portal (classstudent) which a user chooses the students from a list of current company employees.

                I am using a summary field in the classstudent table to count the number of students, count of companyID, that are assigned to the class from the list. The order table contains a lookup field which looks up the classstudent::summary field for the total number of assigned students to generate the total cost.

                I've tried just using a calculation field in order table using: (order::price * classstudent::summary field)

                without any success... also, I do not get any error messages but only blank fields.

                Need expertise - thanks!

                • 5. Re: Lookup on a Summary Field
                  philmodjunk

                  Don't think you want a look up field for this at all.

                  To show the count of registered students for this order, just use the field tool to place the summary field from classStudent directly on your layout.

                  To compute the total cost, use this expression:

                  Order::Price * count ( ClassStudent::NonblankField )

                  In place of "NonblankField" specify any field in ClassStudent that is never empty.

                  • 6. Re: Lookup on a Summary Field
                    techiejw

                    I've got it going, what I endup doing was adding the total cost field in the classStudent table using unstored calculation: Number of Students * order::class price, where number of students is a summary field of count = orderID for all.

                    Thank you for your advice. It did lead me to the right direction.

                    • 7. Re: Lookup on a Summary Field
                      philmodjunk

                      What you've done is essentially a mirror image of my suggested calculation and should produce the same result.