1 2 3 Previous Next 38 Replies Latest reply on Oct 17, 2013 9:51 AM by philmodjunk

    Copy the value by relationship

    henryhu91

      Title

      Copy the value by relationship

      Post

           Hello FM users, 

           I've one table that I'm trying to condition and set up relationship to set variable value from one field to another.

           For example, these are the fields in my database. 

           Item     Part Number     Name      Parent Item    ParentPartNumber

      1          001                 Apple       0

      1.1       002                 Orange     1                    001

      1.1.1    003                 Banana     1.1                 002

      1.2       004                 Cherry       1                   001

            

           What i'm trying to do is to have ParentPartNumber auto create and if Item=Parent Item, I would like to have Parents PartNumber auto copy the Part number. I would really appreciate your help. Thanks in advance.

           Henry

        • 1. Re: Copy the value by relationship
          philmodjunk

               You would need a self join relationship. If your table is called "Parts" it might look like this:

               Parts>----Parts|Parent

               Parts::Parent Item = Parts|Parent::Item

               Then a looked up value auto enter field option can copy the value of Parts|Parent::Part Number into Parts::Part Number.

               But since you can directly reference data in Parts|Parent by putting fields from Parts|Parent on a Parts Layout, you may not really need such a field or looked up value setting. Note that any auto-enter setting--which copy data from the related table, will not automatically update if you modify the data in the related field where if you just directly refer to Parts|Parent::Part Number, you'll always refer to the up to date, current value.

          • 2. Re: Copy the value by relationship
            henryhu91

                 HI Phil. I've the same relationship and when I look up auto enter, it shows nothing even though. I am starting table from Parts and related table from Parts|Partent:Part Number and choose Part Number. it doesn't work out. 

                 I appears just blank. seem to be not working. any thought?

            • 3. Re: Copy the value by relationship
              philmodjunk

                   Exactly what is blank?

                   If you are in Field Options and this happens while you are trying to set up the auto-enter settings, something is wrong with your relationship.

                   If you are looking at a record created before you made this change and no data is appearing in the field, that is expected behavior. Existing records do not automatically update when you add or change an auto-enter setting. You have to do a relookup or use replace field contents to update your existing records. (Relookup is available only if you use the looked up value option.)

                   But I still question whether you need to copy this data into another field at all. That seems to be an unnecessary complication for you.

              • 4. Re: Copy the value by relationship
                henryhu91

                     Can you help me take a look at these screen?

                • 5. Re: Copy the value by relationship
                  philmodjunk

                       Item is not defined correctly in the table. Check storage options for it. The "T" shown in the connecting line is the clue that alerted me to this issue.

                       It must be a stored field with indexing enabled (Indexing is automatically enabled when you make it a match field unless you turn it off.)

                       It cannot have global storage specified.

                  • 6. Re: Copy the value by relationship
                    henryhu91

                         Thank you I got it solved. I make index automatically created as needed and minimal. thank you PHil.

                    • 7. Re: Copy the value by relationship
                      henryhu91

                           Hi Phil, sorry I found an issue. In a large sets of records, Parent ID doesn't looked up some of the records even though Item=Parent Item. in some records, it does get the values and some doesn't. 

                      • 8. Re: Copy the value by relationship
                        henryhu91

                             FYI, I'm importing the csv file and if i modify the item fields and paste the same value, parents ID looked up correctly. how can I make it without me going through modifying and re committing the item field value?

                        • 9. Re: Copy the value by relationship
                          philmodjunk

                               It sounds like you didn't enable "auto enter options" during the import. If you don't click that check box, none of the auto-enter options specified in the target table evaluate during the import.

                               You can import again with the option enabled.

                               Or you can fix your existing records with either ReLookup or Replace Field Contents. (Show All Records or perform a find for an empty field first.)

                               And I still think you are better off without this field.

                          • 10. Re: Copy the value by relationship
                            henryhu91

                                 Thanks Phil. In the script steps, Import Records, Is there a way I can reuse the previous selection of file?

                                 I'm importing records to new tables and at the same time, I would like to import same file but not adding existed part numbers if there is any.

                                 now I have two importing steps in script that users have to select same file all the time for each importing process. Is there a way I can recall the same excel file from same location that users previously selected during the beginning of my script?

                            • 11. Re: Copy the value by relationship
                              philmodjunk

                                   For importing, I sometimes use a container field and Insert File to capture the file path of the file. The user get's an insert file dialog, they select a file for import and the script does the rest.

                                   Such a method could be adapted to keep that inserted file reference for a future import. Users could then import the same file from the same location without having to use the open file dialog to first select it.

                                   Here's the details of the basic method:

                                   Create a layout and place a container field on that layout.

                                   Set up your script like this:

                                   Go to Layout [Go to layout with container field ]
                                   Insert File [Specify container field and insert file dialog options here, must specify "Store a reference" option]
                                   Set Variable [$Path ; GetValue ( YourTable::YourContainerField ; ValueCount ( YourTable::YourContainerFIeld ) ]
                                   Import Records [$Path ; ....

                                   In the past, I've used a global container field. To save that location, you'd either set up a table of such container fields or store the value of $Path in a text field for future use.

                              • 12. Re: Copy the value by relationship
                                henryhu91

                                     oh I see. thanks Phil. 

                                     I've a question. what is the best way to transfer found sets records from one table to another? It could be just copy over a set of records from one table to another as adding new records in different table?

                                • 13. Re: Copy the value by relationship
                                  philmodjunk

                                       The best way is not to do it unless you have a very good reason, but you are already using a method that can transfer groups of records from one table to another--import records. This step can be used even when referring to target and source table occurrences that are both in the same file.

                                  • 14. Re: Copy the value by relationship
                                    henryhu91

                                         Do I need to export field first before importing? because there is specific import order I want to use. 

                                    1 2 3 Previous Next