3 Replies Latest reply on Jul 8, 2010 11:25 AM by eibcga

    Field in wrong table



      Field in wrong table


      In error, I put a field in the wrong table and would like to fix this.  I would like to move the Quantity ("qty") number field from the parent table ("JOURNAL") to the child table ("LEDGER") while keeping all the data therein.

      To try and fix this, I created a new field in the child table called "qty" with calculation type, formula is equal to JOURNAL::qty, and calculation result is Number and is unstored (now I have the same data in both Quantity fields of each table).  All I want to do now to the new Quantity field in the child table is to "copy, paste values" (like in Excel or Numbers to get rid of the formula, but leave the formula result behind as static), then delete the Quantity field from the parent table.  Ultimately, I will then be left with the Quantity number field in the child table with the same original data.  In affect, I have moved the Quantity filed with data from one table to the other and removed the original field.  However, I cannot change the unstored setting in the new Quantity field to stored since it is a related field!  I also cannot change the new Quantity field from calculation type to a number type as it will get rid the formula results.

      Is there a formula or steps I can do to accomplish this?  No script is required since this is a one-time fix.  This database is not used by others and is for my use only for a personal project.

      Thanks!  ERD is attached.


        • 1. Re: Field in wrong table

          Change Ledger::qty to a number field. Select field options and specify Looked up value on the auto-enter tab. Specify Journal::qty as the value to be looked up into the field.

          Dismiss Manage | Database and go to a Ledger layout that has the _kf_trans_id field on it. Select show all records, put your cursor in this field and choose "relookup" from the records menu. This will copy the data from Journal::qty to Ledger::qty.

          Once done, you can delete your qty field from journal and remove the looked up value setting from ledger::qty.

          • 2. Re: Field in wrong table

            Done.  However, after selecting "Relookup Field Contents" from the Records menu while my cursor is in the Ledger::qty field, FM gives me the following dialogue: "There are no fields that look up values based on the field "qty".

            • 3. Re: Field in wrong table

              Update: In Ledger layout, when I put my cursor in the Ledger::_kf_trans_id field instead of the Ledger::qty field, and chose "relookup" again, it worked!