7 Replies Latest reply on Jul 24, 2013 10:14 AM by philmodjunk

    Look Up not returning any value.

    Kaps_1

      Title

      Look Up not returning any value.

      Post

           I have two tables :-

           1. Discussions - which contains a list of all the discussions with a customer.

           2. Discussion Line Items - which contains all the points discussed with a customer.

           The two tables are related by :-

           Discussions:Discussion ID = Discussion Line Items: Discussion ID

           I also have an another instance of Discussions and that is called Discussions for current status. That is linked to Discussion Line Items via the relationship :-

           Discussions Line for current status :current line index = Discussion Line Items:Discussion Line Items id

           where

           Discussions Line for current status :current line index = Max(Discussion line items::discussion line items id)

           so current line index is the last discussion point on a particular conversation.

           I then have two fields "Last Action Date" and "Next Action Date" which are look up fields - they look up the values from the Discussion Line Items and are meant to return the :-

           "Last Action Date"  - i.e the last time something happened on a conversation  - which looks up the field "Last Action Date" on discussion line items

           "Next Action Date"  - i.e the next time something happened on a conversation  - which looks up the field "Next Action Date" on discussion line items

           However, these look ups return nothing.

           I have checked that :-

           Discussions Line for current status :current line index = Max(Discussion line items::discussion line items id) returns the correct value.

           and the screenshot in the next post shows the correct relationship.

           Can anybody see what I am doing wrong ? thanks

           regards

           Kaps

            

            

            

            

      BNI_CRM.png

        • 1. Re: Look Up not returning any value.
          Kaps_1

               here's the lookup relationship.

          • 2. Re: Look Up not returning any value.
            philmodjunk

                 This appears to be the problem:

                 

                      Discussions Line for current status :current line index = Discussion Line Items:Discussion Line Items id

                 

                      where

                 

                      Discussions Line for current status :current line index = Max(Discussion line items::discussion line items id)

                 I don't see any way that can work. If you add a 3rd occurrence of line items and link it in a relationship so that you can reference it in the Max calculation this  should work, but I suspect that you can also reference the desired record if, instead of using Max, you just sort the records in the relationship by discussion line items id in descending order.

            • 3. Re: Look Up not returning any value.
              Kaps_1

                   thanks. I have done the second suggestion - see attached screen shots but still get blank result.

              • 4. Re: Look Up not returning any value.
                Kaps_1
                /files/24b58454df/bni_b.png 1280x720
                • 5. Re: Look Up not returning any value.
                  philmodjunk

                       Not quite, your setup is still has a circular definition.

                       The value of current discussion line index is still dependent on the relationship between the two table occurrences in which it in turn is used as a match field.

                       From what set of records in Discussion Line Items do you want to reference the one with the largest ID? All the records in the table? or just those for a particular customer?

                  • 6. Re: Look Up not returning any value.
                    Kaps_1

                         I want to reference the one with largest ID on a particular discussion. thanks

                         regards

                         Kaps

                          

                          

                    • 7. Re: Look Up not returning any value.
                      philmodjunk

                           Then with this relationship:

                           Discussions---<DiscssionLineItems|LargestID

                           Disscussions::Disccussions id = DiscussionLineItems|LargestID::Discussion id

                           You need only double click the relationship line and specify a sort order for DiscussionLineItems|LargestID that sorts the records in order by Disscussion Line items Id in descending order.

                           THen, if you place fields from DiscussionLineItems|LargestID on a layout based on Dissucssions, you'll see data from the related record with the largest Disscussion Line items id. A portal to this table will list the most recently created record first in the portal.