10 Replies Latest reply on Feb 22, 2013 3:00 AM by NaturSalus

    Look-up not working

    NaturSalus

      Title

      Look-up not working

      Post

           Hello,

           I have tried to set up a look-up both the standard way and through scripting but with no success: My feeling is that I have made  a conceptual mistake.

           On table A, I record tasks (what ) and the name of the person who carried out each task (who).

           On table B,  I record people names

           I have the join table AB used to establish a realtionship between tables A and B.

            

      Relationships

           table A TO --< tableAB TO >-- table B TO

           table A TO x selection table B TO

            

           On a layout to table A TO I have several one row portals to table AB TO.

           There is one portal for each person that does a task.

           The portal when used without the look-up has the following fields:

             
      •           table AB TO:: Task
      •      
      •           table B TO::NameLastFirst
      •      
      •           tableB TO:: Job Title

            

           The portal shows all the values correctly when the Picker isused without the look-up setup.

            

           I use a picker based on Phil's EnhancedValueSelection demo that works smoothly to use persons' name and establish the relationships between table A and B records.

           No issues with the picker, I can add the names of the people who carried out the diffferent tasks and each person's name is shown on the one row portal that also displays the person's job title and the task name.

           For example:

           Proposed by --- John Smith ---  Plant Supervisor

            

            

           The portal when used with the look-up has the following fields:

             
      •           table AB TO:: Task
      •      
      •           table A TO::NameLastFirstForTask 
      •      
      •           tableB TO:: Job Title

            

           The portal shows all the values correctly but that of table A TO::NameLastFirst  that is empty.

            

            

            

           My goal is to keep track of who did what in table A and make sure that it is not modified by any changes made  in table B records (keeps people's names). And I thought that a lookup was the answer.

            

           Let's imagine that there are 5 different tasks and each is carried out by a person (yes it is possible that the same person does more than one task)

           Therefore my setting is:

           table A::namefortask1 field will hold the name of the person  who did task 1

           table A::namefortask2 field will hold the name  of the person who did task 2

           ....

           table A::namefortask5 field will hold the name  of the person who did task 5

            

           As mentioned before, I tried without success 3 things:

            

           My first approach is probably doomed based on how FM lookup feature works.

            I made tableA::namefortask1 field a text and  looked-up value field.

           Once I made a name selection through the picker no value was added to the tableA::task1 field and no value was shown on the portal row field: table B TO::NameLastFirst.

           My second approach was similar to first, but in this case the tableA::task1 field was made a calculation field, with the calculation = Lookup ( tableB TO::NameLastFirst) and calculation result is Text

           My third and last approach was to use scripting and add the following line:

           Set Field [table A TO::namefortask1; Lookup (table B TO::NameLastFirst)

           to the Portal Selection script used in the Picker since it is from this script that the table AB TO records are created and the table A TO to table B TO relationshipsare established.

           But I got the same result:

           no value is shown on the table A TO::namefortask1 field be it by itself or a a cell of the one row portal to the table AB TO

           So I guess, my reasoning is at fault since I am getting zero results.

           Any clues as to how to get home?

           Thanks,

           natursalus

            

        • 1. Re: Look-up not working
          philmodjunk

               Issue 1:

               

                    The portal when used with the look-up has the following fields:

               
                      
          •                table AB TO:: Task
          •           
          •                table A TO::NameLastFirstForTask 
          •           
          •                tableB TO:: Job Title
          •      
               

                    The portal shows all the values correctly but that of table A TO::NameLastFirst  that is empty.

               If you added this relationship: Table AB>------Table A TO 2
               You could use Table A TO 2::NameLastFirstForTask

               To show the name from table A in your portal to AB on a layout based on A.

               

                    My first approach is probably doomed based on how FM lookup feature works.

               Yep. The relationships you now have don't match to the specific related record, they now match to a group of records for all tasks due to the change we discussed recently. A filtered onerow portal to TableAB, however, could show that name with the portal filter filtering out all but the person assigned to this specific task.

               But you might want to consider using a layout based on Table AB instead of Table A as a way to list all tasks and the people assigned to them.

          • 2. Re: Look-up not working
            NaturSalus

                 Hello Phil,

                 Thanks for looking into my question.

                 Unfortunatelly, I still need some help. I have tried several scenarios (my wrong interpretations of your suggestion) and none worked.

                 If I got you right, one of your suggestions is using the Picker and portal filtering by task tha I was using before traying the look-up. This is the setting that is working for me when showing on the one row portal a reference to the related B record of the current A record.

                  

                 My first doubt is about the new relationship that you suggest:.

            If you added this relationship: Table AB>------Table A TO 2

                 Even if my question sounds stupid, believe me I have tried both possible options and none worked.

                  

                 a)You meant creating a new TO of table A ( table A TO2) and associate it to the TO of the table AB that I already have

                 in this case table A TO2 --< tableAB TO . Specifically,  table A TO2::__kp_tableA = table AB TO::_kf_tableA

                  

                 or 

                 b) creating new TOs for both table A and table AB. In this case:

                 table A TO2 ---< table AB TO2. Specifically, table ATO2::__kpt_ableA = table AB TO2::_kf_table A?

                  

            Relationships

                  

                 table A TO --< tableAB TO >-- table B TO (needed to establish relationships between records from table A and B)

                 table A TO x selection table B TO (needed for the Picker to work)

                 table A TO 2 ---< table AB

                 or

                  

                 table A TO --< tableAB TO >-- table B TO (needed to establish relationships between records from table A and B)

                 table A TO x selection table B TO (needed for the Picker to work)

                 table A TO2 ---< table AB TO2

                  

                 Second question is about the layout and the TO to which it relates to

                 Correct what is wrong:

                 Table A_Detail layout based on table A TO

                 or

                 Table A_Detail layout based on table A TO2?

                  

                 Third question is about TO to which the one row portal is based on

                 one row portal based on table AB TO

                 or

                 one row portal based on table AB TO2?

                  

                 Fourth question is about portal filtering

                 Each one row portalis filetred as per the task that I want to show. Two possibilities:

                  

                 table AB TO::PersonTask = "Registrar"

                 or

                 table AB TO2::PersonTask = "Registrar"?

                  

                 Next question relates to the reference of the field that shows on the one row portal the name of the person based on the look_up field create in table A.

                 The field on the one row portal should be

                 table A TO::NameLastFirstForTask

            or

                 table A TO2::NameLastFirstForTask?

                  

            Last question refers to setting up the look-up field.

            It should be based on table B TO::NameFirstLast, correct?

                  

                 I understand that many of these questions will be resolved once I now teh relationships and the TOs.

                  

                 My appologies for so many questions. But as I said before I tried some combinations out of all possible and didn't manage to make it work. Reason beeing that first I need to make it work before I can  understand the rationale behind it.

                  

                 thanks

                  

                  

            • 3. Re: Look-up not working
              philmodjunk

                   I meant the first option ( a) ). But I assumed details that were not correct here.

                   If you set up a portal to AB on a Layout based on A. If you include a field from A in the portal row, you see data based on the first record in the portal in this field in every portal row, not a different name in every row. But in this case, the relationships you have in place only match to a single Table A record anyway so you'd still get the name either way. I'm really not clear on what you wanted to accomplish by including a field from Table A in this portal.

                   The filtered one row portals would be based on AB, but can include fields from B to provide more information such as a name.

              • 4. Re: Look-up not working
                NaturSalus

                But I assumed details that were not correct here.

                     Let me restate what I have and what I want to get.

                I have

                     I do have a sistema that allows me with just one TO and one Picker display several times the same table B record on a layout based on table A.

                In other words using your Picker setting I just modified the POrtal Selection script so that it adds the task, and filtered each portal for the same task. SO that I have one row portal for each task. This way, I can assign people to different tasks and I can even show the same person (table B record) doing several tasks on a table A record.

                     I am able to display on a filtered by task one row portal to table AB TO and with fields:

                      

                       
                •           table AB TO:: Task
                •      
                •           table B TO::NameLastFirst
                •      
                •           tableB TO:: Job Title

                      

                relationships

                      

                     table A TO --< tableAB TO >-- table B TO

                     table A TO x selection table B TO

                      

                I would like to have

                     The problem of the above setting is that if the related record of table B is modified it is also on the one row portal based on table A. And this is an undesirable situation since records shouldn't be able to be modified once filled in and signed.

                     I thought that the way to achieve my purpose was to create in table A a field for each task that keeps the actual name of the person that does the task. SO instead on displayed references I am actually transferring the actual value of teh related record B into a field of record A.

                     And I though that I could achieve this with the current setting and some modifications such as converting in look-up the fields in table A that hold the names of the people carrying each task, and introducing some changes into the one row portal.

                      

                     The question is how to get where I want to be from where I am now

                      

                      

                      

                • 5. Re: Look-up not working
                  philmodjunk

                       This statement is not clear to me:

                       

                            The problem of the above setting is that if the related record of table B is modified it is also on the one row portal based on table A. And this is an undesirable situation since records shouldn't be able to be modified once filled in and signed.

                       A portal based on table A???? Where'd that come from? I thought we had a layout based on A with a portal to AB--which included fields from B.

                       I don't see why you would want changes in B to not appear on the Table A layout. I get the idea that changes to AB should not be permitted--these are the records that link different records in B to the current Table A record on your layout, but not why changes in B should not be visible in A. Can you give an example of why a change in B shouldn't affect what is shown in the Table A layout?

                       However, if that is indeed what you need, the Picker portal's script should be used to copy data from B directly into fields in A and then changes in B will not affect the data shown in A. And then I'm not sure that there's any further need for AB.

                  • 6. Re: Look-up not working
                    philmodjunk

                         Ok, a lightbulb just popped on in the back of my brain:

                         If you want to copy data from B so that changes in B do not affect what is shown in this layout based on A, put your looked up date fields in AB, not A and then the portal to AB will show the needed data.

                    • 7. Re: Look-up not working
                      NaturSalus

                      A portal based on table A???? Where'd that come from? I thought we had a layout based on A with a portal to AB--which included fields from B.

                      Don't go crazy yet. Misspeling on my part. Yes, we had a layout based on A with a portal to AB--which included fields from B

                      I don't see why you would want changes in B to not appear on the Table A layout. I get the idea that changes to AB should not be permitted--these are the records that link different records in B to the current Table A record on your layout, but not why changes in B should not be visible in A. Can you give an example of why a change in B shouldn't affect what is shown in the Table A layout?

                      Not polite on my part to answer with a question but what is the purpose of look-up fields?

                      Imagine a project in which you generate invoices and get the payer name from a table with customers. You generate your invoice sent it and keep a copy for your records. Time passes by, you update your customers table and delete names of past customers. What would happen to you invoice copy?

                      As a matter of fact, in my humble opinion, projects based on db that address real life situations should have many unmodifiable fields.

                      In my case, every time that a name and signature is put on a record it shouldn't be modifiable. This is what happens in real life situations. Records can't be modified once they have been created.

                      I am wondering if we are digressing here.

                      While I was writing this I saw your next message.

                      A pitty it is so late here and I have to pay attention to family matters now.

                      Good point. I'll try it I let you know.

                      Thanks again,

                      natursalus

                      • 8. Re: Look-up not working
                        philmodjunk

                             I think my last suggestion is the better way to go as it should allow you to have a single field or set of fields in AB that would need multiple fields in A to do the same thing.

                        • 9. Re: Look-up not working
                          NaturSalus

                               Thanks Phil,

                               Talk to you on next monday.

                                

                               Have a great weekend!

                               natursalus

                          • 10. Re: Look-up not working
                            NaturSalus

                                 Hello Phil,

                                 It worked nicely.

                                 Thanks,

                                 natursalus