14 Replies Latest reply on Dec 28, 2010 7:30 AM by Kaps_1

    Lookups - again

    Kaps_1

      Title

      Lookups - again

      Post

      I am struggling with a lookup. I have two related tables "Labour Resources" and "Labour Resources on Quote" which are linked together by an index field Labour Resource Id - which is an index field in the first table. However when I try and do a look up I get a blank result. I can't see why - any thoughts. The screenshot shows the calculation that I am using. thanks

      Kaps

      lookup3.JPG

        • 1. Re: Lookups - again
          philmodjunk

          You can also use the Looked Up Value option instead of the calculation option and get the same results. With either approach, keep in mind that this will only occurr when a value is entered or changed in Labour Resource Id or when a Relookup on this field is performed.

          If that's not the issue, you might temporarily place a portal to Labor Resources on your Labor_On_Quote layout to confirm that you have a valid relationship and the right data in your match fields to get the look up value you are expecting.

          • 2. Re: Lookups - again
            Kaps_1

            Thanks. I have discovered that :-

            i)the labour resource Id on the "Labour Resource" is working correctly -  I wrote out a few values to the screen.

            ii) the Labour Resource Id on "Labour Resource on Quote" table is NOT being incremented in anyway. However I cannot see why this is happening. I have updated the screenshot in my opening post to show this (there does not seem to be an ability to add more than one screenshot). thanks

            Kaps

            • 3. Re: Lookups - again
              philmodjunk

              the Labour Resource Id on "Labour Resource on Quote" table is NOT being incremented in anyway. However I cannot see why this is happening.

              I'm afraid that statement confuses me. What you've set up copies data from a related table. It won't increment anything and won't modify the Labour Resource Id field at all, only the value of the Unit Rate field.

              BTW, you can post multiple images by uploading them to a file share site and then posting the link to the shared file in your post.

              • 4. Re: Lookups - again
                Kaps_1

                Thanks - sorry for the confusion. I have uploaded a word document with a number of labelled screenshots. The link is :-

                http://www.4shared.com/file/Z0u6mouC/forum_post_2.html

                Could you possibly have a look ?

                Kind Regards

                kaps

                • 5. Re: Lookups - again
                  philmodjunk

                  That doesn't really show me any thing new from what you included with your first post.

                  If you enter or select a value in the Labor_Resource_id field that matches the value in Labor_Resource_ID of the Labor_Resources table, the current value in Labor_Resources::Hourly_Rate_Price in this record should be copied into Labor_On_Quote::Unit_Rate.

                  Is this what is happening?

                  • 6. Re: Lookups - again
                    Kaps_1

                    when I change the the value in the labour_resource_id field nothing happens. the unit rate remains unchanged.

                    • 7. Re: Lookups - again
                      philmodjunk

                      Labor_Resource_ID of which table? (You might want to check this on your layout to be sure.)

                      • 8. Re: Lookups - again
                        Kaps_1

                        I'm getting confused. I am changing the labour resource id on the labour_on_quote ??

                        • 9. Re: Lookups - again
                          philmodjunk

                          Yes, entering/changing the ID value for Labour_On_quote::Labour_Recource_ID on a layout or portal based on Labour_On_Quote should trigger the look up or auto-entered calculation.

                          If it doesn't then something isn't as expected in either your relationship or in the values of the two id fields. Since the relationship in the relationship graph looks correct and the two fields are both of type number, I'd be inclined to check to make sure that the values in the id field truly match as expected.

                          • 10. Re: Lookups - again
                            Kaps_1

                            Thanks - I had the labour_resource on the layout rather than the labour_resource_id. Sorry about that.

                            Next problem is that if I change the id I can only see the id number on screen rather than the underlying text. e.g if the record id is 15 and the text is 'lawnmower man'  I want to see either '15 lawnmower man' or 'lawnmower man' - instead I just see 15. I have attached screenshots of my layout and the value lists associated in the following document:-

                            http://www.4shared.com/file/o5OXH6HH/forum_post_3.html

                            On the layout it is the one under screen shots.

                            Thanks again for all your help. I really appreciate it !!!

                            Regards

                            Kaps

                            • 11. Re: Lookups - again
                              philmodjunk

                              Don't need to see screen shots to answer that question. Almost every developer that properly relates data by an ID number instead of a name or description field encounters that issue.

                              Here are two different approaches to get the same resullt.

                              1. Format your field as a pop up menu instead of a drop down list. Set up the value list to only show data from column 2. This requires unique values for Labour_Resource.
                              2. Add the Name field from the related table directly to your layout next to the drop down list formatted field. When you select a value in the Id number field, the related name or description field automatically appears next to it. (Since this enables the user to edit the contents of the field from the related table, you may need to change some layout settings to prevent this possibility.)
                              • 12. Re: Lookups - again
                                Kaps_1

                                thanks - I have used approach one. However it appears that my formatting on the layout loses its border when I try a pop up box. I have tried using the inspector to put a 'border' around it but no luck - any thoughts ?

                                • 13. Re: Lookups - again
                                  philmodjunk

                                  Pop Up menus insist on "Ye Olde beveled look" whether you want it or not. You can make the borders transparent to hide the bevel and then draw a rectangle around the pop up to create a border. You can adjust the size and position of this rectangle one pixel at a time in the inspector to get it precisely aligned with the pop up menu field.

                                  • 14. Re: Lookups - again
                                    Kaps_1

                                    Thanks for that. Much appreciated. Learnt a lot !!!