5 Replies Latest reply on Mar 20, 2012 8:26 AM by philmodjunk

    Committing records from a portal

    GregJones

      Title

      Committing records from a portal

      Post

      I need to take records from one table and copy them to an identical table conditionally.  The two tables do not have a direct relationship, but are related thru a third table.  In my layout, I can display the records I want to copy in a portal but, can't get them written to the second table.  I made the firlds in the second table Lookup fields but, not even the first matching records are written.

      Any advise would be appreciated.

       

      Greg

        • 1. Re: Committing records from a portal
          philmodjunk

          You'll need to describe the relationships between your three tables. Also tell us how your layout and portal is setup in terms of this relationship.

          • 2. Re: Committing records from a portal
            GregJones

            Thanks Phil,

            I'm attaching the DB relationships.  The idea is to draw data from 'Furniture' and 'Parts' and make copies in 'Run' and 'Run parts' where it can be modified.  The layout contains all the fields from 'Run' and a portal with the fields in 'Parts'  I made Run::desriprion a lookup field and that works fine (select a model from furniture and it's description ends up in Run) The portal displays the correct records from Parts but nothing I do makes them end up in Run parts. I tried makinf the fields in 'Run parts' lookup fields but not even the first occurence works.  I tried using a button which executed a script containing SetField's for each field in run parts but, nada.  This is my first FP project and I'm stumped.

             

            Thanks again.

             

            Greg

             

             

            • 3. Re: Committing records from a portal
              philmodjunk

              I have to wonder it you need to copy this data from table to table at all.

              It looks like you need this relationship:

              Parts::PartID = RunPartsByID::PartID  (RunPartsByID is a new occurrence of the RunParts table.)

              Then fields in RunParts can look up data from parts when you copy over the PartID (should be an auto-entered serial number) from each record in Parts to a new record in RunParts. Here's a possible script you can use with that relationship:

              #From the Furniture layout:
              Freeze Window
              If [Not IsEmpty ( Parts::Model Number ) ]
                 Go To Related Record [Show only related records; From table: Parts; Using layout: "Parts" (Parts)]
                 Set Variable[$Model ; value: Parts::Model Number]
                 Loop
                    Set Variable [$PartID ; value: Parts::PartID]
                    Go to Layout [Run Parts]
                    New Record/Request
                    Set field [Run Parts::Model Number ; $Model]
                    Set Field [Run Parts::PartID ; $PartID]
                    Go to Layout [Parts]
                    Go to Record/request/page [next ; exit after last]
                 End Loop
                 Go to layout [original layout]
              Else
                  Show custom dialog ["There are no parts listed for this model."]
              End If

              Since you can get a new Run record to work, I haven't addressed that part here, just the process of replicating the list of related parts in the RunParts table--which could very well be an unecessary part of your process. A portal to Parts placed on your Run table, for example, would list all the parts with no scripting to duplicate parts records--just to give one example.

              • 4. Re: Committing records from a portal
                GregJones

                Hi Phil,

                Thanks for the input.  The reason it is necessary to copy the records from parts to run parts is that parts contains standard configurations.  These are typically modified in some way when built.  So, make a copy, modify as needed, purge after built.  I sort-of see what you are suggesting with the partid scheme.  I don't see why the same thing could not be done using model number which is currently the primary key.  i mostly can't figure out why the lookup field designation doesn't work.  That seems like the cleanest solution.  If that can't be made to work, I'd like to be able to commit the records drawn from parts into the portal to the run parts table,   I just don't know how to do that.  

                 

                Thanks again for your help.

                 

                Greg

                 

                • 5. Re: Committing records from a portal
                  philmodjunk

                  Model number uniquely identifies the model, that's why this works for the description field in Run. It does not uniquely identify each record in Parts. An auto-entered serial number in the parts table will uniquely identify each part record. You need a one to one match between the new record in RunParts and an existing record in Parts before the looked up value field option will work. Please note that a looked up value field option would be able to copy over data via the partID based relationship once the newly created record receives a PartID value.