13 Replies Latest reply on Feb 1, 2017 10:36 AM by philmodjunk

    Question regarding "Relookup Field Contents" Script Step

    andrewma

      I have one Database with two tables.  Table A and Table B.

      Each table has its own layout, and each table will have a different number of records.

       

      I have a field in each table that I have created a relationship with, lets call it Field 1.

       

      So in Table A    I have Field 2, that is set to be a lookup of its corresponding Field 2 in Table B.

       

       

      I have a simple one line script that says the following:

      Relookup Field Contents [With Dialog: off; Table A: : Field 1]

       

      What I am expecting to happen is to match Field 1 contents from Table A, to the matching record in Table B, then the contents in Field 2 from Table B to be put into Field 2 from Table A.

       

      That is not happening,

        • 1. Re: Question regarding "Relookup Field Contents" Script Step
          ninja

          #1:  Does the original Lookup behave as expected? (ie. is it the relookup that is the problem, or your lookup config in the first place?)

           

          #2:  Make it a two line script

          -  GoToField (your matchfield as defined in your lookup)

          - Relookup Field Contents

           

          An overriding question:

          What is the point of duplicating the data in the first place?  Just put a field from Table B on the Layout of Table A.

          What is the need here for duplication of data?

          • 2. Re: Question regarding "Relookup Field Contents" Script Step
            andrewma

            Let me explain why I'm using two tables, maybe someone can see an easier way:

             

            I have an excel sheet lets say with a random number of rows of data.

            I have a script that imports the data in the rows into Filemaker, (Table B)

            During the import into Table B it creates as many records as there are rows of data in excel

             

            The data in the excel sheet can change often so i import regularly into Filemaker and it replaces  the data properly.

             

            In Table A, I have a specific set of records which rarely changes, but only a very few of them will need the data from the excel sheet.  So If I imported the data from Excel into Table A, it would create new records I assume and i Can not have that.

             

            Instead I want the new records created in Table B so Table A will not be effected in anyway.

            • 3. Re: Question regarding "Relookup Field Contents" Script Step
              ninja

              Makes sense...right up until the lookup.

               

              If Table A has a record...and Table B has a newly imported record that matches...the value is "looked Up
              into Table A.

               

              Then you delete all records from Table B and reimport new ones....but there is now no match to the Table A record...

              Do you want the value from back when there WAS a matching record?  Or would you rather have a blank?

               

              If you want blank becuase there is no matching record, simply put a field on the Table A layout...that field being from Table B.

               

              If you would rather have the Last known value still showing...stay with your lookup....but make sure your lookup settings leave the existing value alone...

              • 4. Re: Question regarding "Relookup Field Contents" Script Step
                andrewma

                And In the above 2 Line script would it look like this?

                 

                Go To Field [Select/perform; Table A: : Field 1]

                Relookup Field Contents [With Dialog: off; Table A: : Field 1]

                 

                In this script nowhere do I mention the actual field I want to replace the data in, is that correct ?

                Filemaker I assume checks the layout for all fields that are a Lookup and refreshes them all ?

                 

                 

                Field 1 is my Matching Field

                Field 2 is the one that is the lookup11.JPG22.JPG

                 

                I have attached the pictures of how the lookup is configured

                 

                • 5. Re: Question regarding "Relookup Field Contents" Script Step
                  andrewma

                  I think my issues is the word "New", I am not deleting the records in Table B and importing new ones.  The import simply changes the data in the field,  I think Filemaker is ignoring the new data because the record was not newly created, the data in it was changed, , that is why I want to use the script to Refresh the data.

                  • 6. Re: Question regarding "Relookup Field Contents" Script Step
                    ninja

                    In the generic terms we are using (Table A, Field 1, etc) it looks right.

                     

                    Does it work as intended?

                    I assume the answer is that you want data to persist even if you no longer have a matching record in Table B.

                     

                    Not sure what the prohibition of mod during data entry is doing for you though...but thats just detail.

                    • 7. Re: Question regarding "Relookup Field Contents" Script Step
                      andrewma

                      I want the data to not change in Table A even if all the records in Table B are deleted.

                      When I run that Macro I want it to then and Only then look for the matching record in Table B and copy over the data.

                       

                       

                      Now having said that, if the Data in Table A changed every time the Data in table B did, its not the end of the world, I could work with that,, my problem is right now the Data in Table A is ignoring the Data in Table B

                      • 8. Re: Question regarding "Relookup Field Contents" Script Step
                        ninja

                        Now that you've made the changes...does it work as intended?

                         

                        The two step script (rather than one step) simply puts focus on your match field before the Relookup runs.

                         

                        From the onboard help:

                        Select Go to target field or click Specify to specify the field that is the target of the relookup operation. FileMaker Pro moves the cursor to the field you specify. This must be the match field for the relationship upon which the lookup is based, not the Lookup source or target field. If no field is selected, Relookup Field Contents returns an error code that can be captured with the Get(LastError) function.

                         

                        I simply do both (go to field AND relookup)...never had an issue.

                        • 9. Re: Question regarding "Relookup Field Contents" Script Step
                          andrewma

                          Ok, something doesn't seem right, let me explain:

                          Table A, I do a find, It shows 12 records, These are the records I want the data in Field 2 to be looked up from Table B.

                           

                          I run the script, it goes to Field 1 on whatever  record I happen to be on, fails to do the lookup and seemingly does nothing but put the cursor in Field 1

                           

                          using this script:

                          Go To Field [Select/perform; Table A: : Field 1]

                          Relookup Field Contents [With Dialog: off; Table A: : Field 1]

                           

                          As I understand it, relookup is supposed to do the lookup for a found set, is it not?

                           

                           

                           

                          When I turn Dialog On in relookup,  I get this message:

                           

                          In the 12 records that you are browsing, copy new values for every field that has a lookup based on this field "Field 1".

                           

                          I click ok, and nothing happens.

                           

                          Here is one other Odd thing,   if I MANUALLY change the data in Field 2, on table A, and then run the relookup script, it changes it back to some previously held data from table B, but NOT what is currently being stored in Table B.

                           

                          • 10. Re: Question regarding "Relookup Field Contents" Script Step
                            ninja

                            Yup...that doesn't seem right.

                             

                            Revert back to some previously held value?  Where is it getting the info?

                             

                            Time to test:

                            1. Put a portal on your Table A layout, based on Table B, and put Table B Field 2 in it.

                                 How many related records are there for the 12 records you want? 

                                 The point:  Are you trying to lookup one value when you have a bunch of values in Table B?

                                 (thus my comment about deleting table B records earlier)

                             

                            2. Assuming it is only one record each from Table B...put Table B Field 2 on your Table A layout....

                                  Run your script

                                 The values should now match....do they?

                             

                            3. Manually create a couple records in Table 2, and enter values in Field 2...they should come over when the script is run...do they?

                             

                            It would be easy if you can post your file...

                            1 of 1 people found this helpful
                            • 11. Re: Question regarding "Relookup Field Contents" Script Step
                              philmodjunk
                              it changes it back to some previously held data from table B, but NOT what is currently being stored in Table B.

                               

                              That sounds like there is more than one record in Table B that matches to the same record in Table A. Given the periodic imports, that's very easy to have happen here. Importing records creates a found set of the imported records so you might not even notice that your imports are producing duplicates.

                               

                              Select a value from field 1 and copy it to the clipboard. Go to a layout for Table B, enter find mode and paste the value in the match field of table B and perform a find. My guess is that you'll see more than one record with that value in Table B.

                              • 12. Re: Question regarding "Relookup Field Contents" Script Step
                                andrewma

                                So, I got it to work, the end result was, I HAD to make my import script delete all the records on Table B then re-import the data.   Importing the data without deleting the records  first does get the correct data, but it seems Filemaker ignored it unless it was from a freshly created new record,  AND I then ran the Relookup script.

                                • 13. Re: Question regarding "Relookup Field Contents" Script Step
                                  philmodjunk

                                  It doesn't ignore it. your imports are generating duplicate records.

                                   

                                  If you look into the "Match found set" option and set it up correctly, you can avoid generating duplicate records and not need to delete all records every time.