1 2 Previous Next 16 Replies Latest reply on Jan 29, 2016 12:23 PM by AlastairMcInnes

    Importing to calculation field

    AlastairMcInnes

      I am working on a new version of a database previously written in FM11.

       

      In order to get the data from the old database into the new version (fmp12 format), I have been exporting it to Excel then importing it. This is working so far as it goes, however I have a problem with a calculated field.

       

      The field, ContractID, is defined as Get(RecordID)*2468

       

      (Don't ask me why - I inherited all this stuff).

       

      Anyway, I need to import this data to the new database, keeping the same ContractIDs as in the old system, but I can't import to a calculation field and, if I make it a normal numeric field, then try to convert it to the same calculation as before, FM will delete all the existing data in the process.

       

      So, is there any way to do this?

       

      Thanks in advance,

      Alastair

        • 1. Re: Importing to calculation field
          steveromig

          I am working on a new version of a database previously written in FM11.

           

          In order to get the data from the old database into the new version (fmp12 format), I have been exporting it to Excel then importing it. This is working so far as it goes, however I have a problem with a calculated field.

           

          The field, ContractID, is defined as Get(RecordID)*2468

           

          (Don't ask me why - I inherited all this stuff).

           

          Anyway, I need to import this data to the new database, keeping the same ContractIDs as in the old system, but I can't import to a calculation field and, if I make it a normal numeric field, then try to convert it to the same calculation as before, FM will delete all the existing data in the process.

           

          So, is there any way to do this?

           

          Thanks in advance,

          Alastair

           

          Silly question...why not just convert the file from FM11 > FM12 (and beyond)?

           

          Steve Romig

          FileMaker, Inc.

          • 2. Re: Importing to calculation field
            AlastairMcInnes

            Because we're amalgamating two different databases into one new one. The old one is horribly cluttered with layouts, fields and scripts that have been added by just about anyone - I'm very keen to start with a clean page and only add what we genuinely need from each of the old systems.

            • 3. Re: Importing to calculation field
              FilmUser

              Can you import the data without the ContractID, then set up the calc field (ContractID) with the same calculation and let the new data base resume making the same calculations as before?

               

              This would mean making the record ID in the new data base just a number field without the RecordID option. Then after all records are imported, along with the RecordID's from the old data (which will recalculate the ContractID's), then change the RecordID field to resume creating new RecordIDs, beginning with the next available number, based on the highest number imported + 1.

              • 4. Re: Importing to calculation field
                AlastairMcInnes

                As I understand it, the RecordID is created internally by Filemaker when a record is created, and cannot then be changed. I don't think it's possible to export or import it.

                • 5. Re: Importing to calculation field
                  FilmUser

                  It can't be imported, but it can be exported. (I just did it this morning).

                   

                  Define your new Record ID as a number field, without the option for creating record id's.

                  Import the old ones, the new db should perform the calcs you need for the contract id's.

                   

                  Then go back into the field definition for the new record id, set up the option to create record id's from this point on, starting with the next available number.

                   

                  Sorry for the typing, I'm in a hurry.

                  • 6. Re: Importing to calculation field
                    AlastairMcInnes

                    Thanks again, but I don't see any way of exporting the RecordID - it doesn't appear as a field in the export list. Unless I'm missing something obvious, of course...

                    • 7. Re: Importing to calculation field
                      FilmUser

                      I just confirmed it again.

                       

                      One possibility is that the record id field is not present on the current layout at the moment when you go to export, go to the top of the left column and choose the entire table, which will make available all fields in the table.

                      • 8. Re: Importing to calculation field
                        AlastairMcInnes

                        I tried that - the RecordID just doesn't appear as a field. Which doesn't entirely surprise me as it's mainly used internally by Filemaker.

                        • 9. Re: Importing to calculation field
                          FilmUser

                          I don't know what to tell you. I just opened a database and exported the record id, along with some other fields. I have done this in the past, when someone who does not have FMP wants to update data in a database. I send them an excel file which includes the record id, which I hide. They make the changes to the data, and I import the changes back in using the record id as a match field.

                          Someone with a bigger number than me will have to weigh in here.

                          • 10. Re: Importing to calculation field
                            AlastairMcInnes

                            Thanks, anyway, for trying to help. There must be some setting you have that I don't that allows you to see the RecordID as a field and export it - but who knows how many settings there are behind the scenes.

                             

                            It has, however, occurred to me that I can export the Contract IDs and then create a new field in the new database and set it to be a serial number starting from whatever the highest RecordID must have been in the old one, then I can base the calculation of the ContractIDs on that field instead.

                             

                            But, thanks again.

                            • 11. Re: Importing to calculation field
                              StylisticGambit

                              Sometimes when exporting, the dialog box will present a list of fields on the current layout and not a list of fields on the current table.

                               

                              You can change this option within the dialog box, or else you can switch to a different layout which includes the RecordID field (or create a new layout with just this field), and try exporting again.

                              • 12. Re: Importing to calculation field
                                AlastairMcInnes

                                Thanks, but we tried this - the RecordID never appears as a field in my table, no matter where I look. I'm more convinced now than ever that I've got some flag set somewhere to hide them, but I don't what it is.

                                • 13. Re: Importing to calculation field
                                  StylisticGambit

                                  If it's visible within the Fields tab of Manage Database, it can be exported.

                                   

                                  Try creating a new layout with just the fields you want to export on it.

                                  • 14. Re: Importing to calculation field
                                    AlastairMcInnes

                                    It's not visible on the Fields tab of the Manage Database.

                                    1 2 Previous Next