10 Replies Latest reply on Oct 12, 2010 4:54 PM by remoran_1

    Copy field data into a join table field

    remoran_1

      Title

      Copy field data into a join table field

      Post

      I am having trouble trying to paste field data into an ID field in a join table. The DB works without a problem and the relationship works beautifully but I cannot paste the ID into the join table field after performing the find on the desired records. I tried Replace Contents on the found set, which seemed to work but, in actuality, did nothing. Tried set field via script manager but the calculation setup says the table cannot be found even thought the many to many relationship via the join table works perfectly in the system. I can do a control paste into the join table id field and the related record loads beautifully but doing this 6000+ times is a non starter. Any help would be great. Lastly, the join table ID field is a lookup into the source table that contains the ID. Also tried this script given to me by an FM user far more expert then I in the scripting side of things. :) Set Variable [ $id; Value:_Primary_id ] Go to Layout [ “New Layout” (YourTable) ] Enter Find Mode [ ] ...Set your find criteria Perform Find [ ] Replace Field Contents [ _foriegn_id; Replace with calculation: "$id" ] [ No dialog ] This also seem to work as per Replace Contents but it also does nothing. Any help on this would be terrific. TIA remoran

        • 1. Re: Copy field data into a join table field
          philmodjunk

          Sounds like you are having trouble referencing the correct table occurrence (this is not necessarily a table) and field within it.

          How is the current layout set up?

          Do you have a portal to the join table?

          Is the field from which you are copying information defined in the layout's table or the table on the "other side" of the join table?

          Note: copying data like this tends to send up a warning flag. I can't tell from your post, but the action to copy data from one table to another is often unecessary as their are other approaches that avoid the need for this altogether or implement it with an auto-enter option such as a Looked Up value setting.

          • 2. Re: Copy field data into a join table field
            remoran_1

            Hi Phil,

            Yes, I have a portal to the join table and the two source tables that support the join are the first and, in the case of the ID table, the only occurance ot the table in the database. The current layout is Personnel and the portal connected to Personnel layout via the join field goes to the insurance table(insuranceID and InsCompanyName). The ID field is referenced, unique and indexed. The Go to related field command (Personnel to Insurance) works without a problem via the join.

            If you have a better way to populate the Insurance ID filed, that would be great. The ID via the join is a lookup to the source field, a function that works like the way an inventory line item works in connecting invoices to product. I hope this make sense.

            Best,

            Bob

            • 3. Re: Copy field data into a join table field
              philmodjunk

              Let me recap the details to make sure I have them correct:

              Personnel---<Join>----Insurance.

              Personnel::PersID = Join::PersID // allow creation of records enabled for Join

              Insurance::InsID = Join::InsID // allow creation of records enabled for Join

              You have a Personnel layout with a portal to the Join table (not insurance). Correct? (This is a key detail)

              Now the part I'm trying to figure out: What value from what table are you trying to enter into the Join table? Go To Related Records will not work as you describe unless you already have a record in Join with the requisite PersID and InsID values. That doesn't leave anything to put in the Join table from your description.

              • 4. Re: Copy field data into a join table field
                remoran_1

                Here is the setup. Your first three entries are spot on, i.e.

                Personnel---<Join>----Insurance

                Personnel::PersID = Join::PersID // allow creation of records enabled for Join

                Insurance::InsID = Join::InsID // allow creation of records enabled for Join

                The Personnel layout, however, differs.

                Personel Layout with the Join (InsJoin) as Portal resides in the Personnel layout. In the InsJoin portal are two fields, InsJoin::InsuranceID and insJoin::ICompanyName. Both fields are lookups into the Insurance table via the InsJoin table. When I do a lookup into the Insurance table using a value list, the InsuranceID and ICompanyName loads without a problem and populates the portal. I can add other insurance fields in the poral as well as people will have more then one insurnace company covering their butts. If I do a Go to Related Record from Personnel, the correct Insurance company and ID comes up. If I do a copy paste of the contents of a given InsuranceID filed into the InsJoin::InsuranceID field, the proper information also comes up without corrupting the insurnce table which, in this case, contains 39 insurance companies.

                I hope my description helps. Thanks again for helping me.

                Best,

                Bob

                You have a Personnel layout with a portal to the Join table (not insurance). Correct? (This is a key detail)

                • 5. Re: Copy field data into a join table field
                  philmodjunk

                  Instead of having ICompanyName copy a company name from Insurance, you can actually just add the corresponding name field from Insurance directly to your portal row.

                  Given what you describe and I understand from it,

                  Set Field [InsJoin::InsuranceID ; //your expression here ]

                  will correctly enter data into a related InsJoin record. Since you may have several such records in the portal, care must be taken that the correct portal row is "current" before this step executes or a different record than what you want could be modified. I wouldn't use Replace Field Contents for this as it would put the same value in all the related InsuranceID fields instead of just one.

                  If you haven't used Set Field before, keep in mind that you use the upper Specify button to select your target field (InsJoin::InsuranceID) and then click the lower specify button to enter the expression that will be use to produce the value set field enters into the target field.

                  What I don't understand is why you would be using a script to change the contents of InsJoin::InsuranceID nor from what source you are attempting to copy this data.

                  • 6. Re: Copy field data into a join table field
                    remoran_1

                    I did the substuition as per your request so ICompanyName now comes directly from the Insurance table and not from InsJoin. For the script, I did ths.

                    Go to Layout ["Personell"(Personnel)]

                    Go to Fiels [Personnel::ICompanyName]

                    Enter Find Mode [Pause]

                    Perform Find [Restore]

                    Go to Field [InsJoin::InsuranceID]

                    Set Field [InsJoin::InsuranceID;"I2"

                    Is this correct? If this can be done without a script, awesome. Any way I can ge this done would be great.

                    • 7. Re: Copy field data into a join table field
                      philmodjunk

                      The question is: What's in the portal at the time Set Field [InsJoin::InsuranceID; "I2"] does its thing?

                      Assuming you have this relationship:

                      Personnel::PersID = Join::PersID // allow creation of records enabled for Join 

                      If there are no records showing in the portal, it will create a new record in the portal. If there are records present, the first record in the portal will have its InsuranceID field changed to "I2"-- that's two very different results depending on whether any related records are present in the portal.

                      Why are you assigning "I2" to the InsJoin::InsuranceID field?

                      Usually these ID fields are auto entered serial numbers and "I2" doesn't have the format typical for an auto-entered serial number.

                      • 8. Re: Copy field data into a join table field
                        remoran_1

                        In some respects, I wish FM was, god forbid, like excel.

                        The InsuranceTable IDs are auto entere, unique, I plus serial number elements, thus,  each insurance company has a unique ID. In this case, Aetna has the ID of I2. The insurance table remains untouched save for it providing content to the InsJoin portal via a lookup. The portal contains the two fields, InsJoin::InsuranceID and (as per your recommendation, ICompanyName. When I do a click drag using a value list using InsuranceID and ICompanyName from the Insurance Table, the InsJoin Lookup properly retrieves the InsuranceID data, in this case, I2 and places it into InsJoin::InsuranceID. When this happens, the ICompnanyName, Aetna, comes up in the Insurance table filed ICompanyName without a problem

                        This process works for every person I want to add one or more insurance companies to their record. This works exactly like Invoice/InventoryLineItem/Product as one person can have many insurance companies and one insurance company, obviously has many customers.

                        If FM was similar to Excel, I could merely do a find against Aetna and get the I2 ID. Once there, do a copy to the clipboard of I2, After that, select and higlight all the the InsJoin::Insurance ID fields in the found record set and do a paste. I can do this in Dreamweaver witout a problem but you can't do this in FM even thought the DB works witout a problem.

                        As stated before, I can do ctrl paste into the InsJoin::InsturanceID field and it works PERFECTLY. I just want to do this for the found records as the Insurance table and personnel table remain intact save that the personnel table now has 751 people using Aetna as their insurance company.

                        • 9. Re: Copy field data into a join table field
                          philmodjunk

                          The problem with your earlier script is that this does this action for just one record and may overwrite existing data in the Join table.

                          Try this script:

                          Go to Layout Personnel
                          Peform find // set this up however you need to find the correct personnel records
                          Go to Record [First ]
                          Loop
                             Go TO Portal Row [Last]// allow creation of records via this relationship must be enabled for the relationship
                             Set Field [InsJoin::InsuranceID ; "I2"]
                             Go To record [ Next ; exit after last ]
                          End Loop

                          Final note: you probably already know this, but for individual editing, you can format insJoin::InsuranceID on this layout as a two column value list with the ID in column 1 and the name in column 2.

                          • 10. Re: Copy field data into a join table field
                            remoran_1

                            It works!!!! Awesome. Thank you so much!!!

                            Best

                            Bob Moran