6 Replies Latest reply on Apr 7, 2015 6:32 PM by fmnoob

    Auto Create a table record

    fmnoob

      Title

      Auto Create a table record

      Post

      FM Pro 13; Mac OS 10.9

      When a new record is created in table A I would like to auto create a new record in table B using some of the data from the table A record. Can someone point me in the right direction.

      Thanks,

        • 1. Re: Auto Create a table record
          philmodjunk

          You would need to script that, But why do you need to automatically create a new record in B every time you create a new record in A? That seems a bit unusual.

          If "allow creation of records via this relationship" is enabled for B in a relationship linking tables A and B, this can be done with a single set field step that sets  a field in B to receive a value from the context of a layout based on A where the newly created record is the current record.

          • 2. Re: Auto Create a table record
            fmnoob

            It is unusual and redundant in some ways. Table A and table B are many tables to the same one table. Table A is records of a particular type of item which is one of many types of items stored in table B. Table A just stores more info about a particular record in Table B.

            Example:

            Table B contains records of all expenses for a trip.

            Table A contains records of all the Hotel expenses of the trip, plus other info about the hotel stop.

            So when a Hotel expense is created in Table A it would be good if some of that data was auto entered into a new expense record in Table B.

            • 3. Re: Auto Create a table record
              philmodjunk

              Which is not really good database design.

              All expenses whether for Hotel or others should all be in the same table. Putting one subset of your records in another table creates redundant copies of your data as is evidenced by the need to copy data from a record in one table to another. Ideally, the only such data that need be copied is the value of an ID field such that data in the one table can be referenced from the context of the other table via that relationship.

              • 4. Re: Auto Create a table record
                fmnoob

                All expenses whether for Hotel or others are in Table B. Table A is used only to store additional data for the Hotel type of expense. Rather that having unused fields in Table B, I created Table A to hold the fields to store the additional data for the Hotel. Table A is used only to store the extra information. 

                • 5. Re: Auto Create a table record
                  philmodjunk

                  Then I repeat, the only value you should need to copy over is the ID need to link the two records in a relationship.

                  My first post indicated one way to do that using set field.

                  Here's another:

                  Set Variable [$ID ; ParentTable::PrimaryKey ]
                  Go to Layout ["child" (ChildTable) ]
                  new Record/Request
                  Set Field [Child::foreignKey ; $ID ]

                   

                  • 6. Re: Auto Create a table record
                    fmnoob

                    Thanks, I will proceed in that direction.