7 Replies Latest reply on Jan 17, 2013 12:09 AM by Sorbsbuster

    Two quick FMPro12 Database questions

    MatRichardson

      Title

      Two quick FMPro12 Database questions

      Post

           Good morning/afternoon!

           I have a couple of quick questions and wondering if anyone can help.

           I have a single database that records assets, I have a copy of this database in 5 different countries, each look after their own list of assets, recording assignment, condition, auditing etc. Every month, each country emails back a copy of their database, I would like to know if it is possible for me to combine all 5 into a single central database? Each location has exactly the same layout but choses their own country location from a drop down box. Can anyone tell me if this is possible?

            

           My second question, I have 1 section of the databse that holds serial numbers, sometimes a combination of numbers and letters, sometimes just numbers. When I chose to export data to an excel spreadsheet, I don't get the serial numbers, just a jumbled mess. Is there a way of assigning this box as text and then getting it to export to excel as text so I get exactly what is in the database?

            

           Many thanks to anyone who can help!

            

           Mat

        • 1. Re: Two quick FMPro12 Database questions
          philmodjunk

               Item 1

               The design of your layouts will not determine if you can do this. The design of your data will. You can certainly set up a script to import records (You can also do it manually) to pull all records into a common file. But whether or not you can make use of the results will depend on how the data in the databases. There could be, for example records with identical ID numbers in each separate file that represent different items of data that may thus be completely mixed/impossible to separate.

               Thus, you may be able to do this right now. If not, a person who knows fileMaker and also your design can probably come up with a script that merges the data with some built in updating to produce a workable set of merged records.

               Item 2:

               What kind of "Jumbled Mess"? is this a text field or a number field? Exporting letters and numbers from a text field should export correctly. Exporting from a number field would probably export just the numeric data in the field.

          • 2. Re: Two quick FMPro12 Database questions
            MatRichardson

                 Hi

                 Thanks very much for the reply, much appreciated. I'll try and explain further..

                 The data in each database is unique, as in their are lots of items the same but they all have unique serial numbers and asset numbers. For example I have 10000 assets, 1 - 2000 in first country, 2001 - 4000 in second country etc.etc. There are no two matching serial or asset numbers. 

                 So, ideally, I have one master database with all items, every month I will update the master with information from the country offices so I know every month who is assigned which assets. Does that help at all? I could give each country a full database with all 10000 items on it and let them just work on their own country assets but I'd still have 5 databases each with a section that would need to be merged with the others. Any suggestions would be appreciated!

                 As for the second point, the serial number is in text form in the database, when I export to excel, the excel spreadsheet reads it as numbers only and either misses out the letters or adds +'s etc. How can I stipulate that on export to excel, a certain column is exported as text and not as a number? Normally, I go to print layout, then save/send layout to excel.

                 Thanks again

                 Mat

                  

                  

            • 3. Re: Two quick FMPro12 Database questions
              philmodjunk

                   "adds plusses" sounds like Excel is displaying the value in scientific notation. You should be able to select a format for the column in Excel to see it as a plain number.

                   What is the format for the values you are exporting?     ######@@@, @@@###### or ####@@@###? (# = numeric digit, @ = a letter).

              • 4. Re: Two quick FMPro12 Database questions
                MatRichardson

                     The values in the serial number are random, can be any combination of letters and numbers, like a laptop serial number for example. I have tried changing the type of cells to text but the data is no longer there so it doesn't revert to the correct number/letter combination. It appears that the database is exporting it in a different format to what is recorded. The data originally was imported from an excel spreadsheet, from a cell formatted to text and it displays correctly in the database, just doesn't when exported again!

                      

                      

                • 5. Re: Two quick FMPro12 Database questions
                  philmodjunk

                       I think you should open Manage | Database | Fields and confirm that this field is really of type text, not number.

                       The following screen shot shows sample data entered into FileMaker 12 and the results produced by using Export Records to export the data to an excel file. Save As | Excel produced identical results and both .xls and .xlsx options also produced the same reusults.

                  • 6. Re: Two quick FMPro12 Database questions
                    MatRichardson

                         Now I knew it would be something simple, no idea how I missed that as I have confirmed the field type for every single function except that one!

                         Thanks very much for the advice, very much appreciated.

                         On the first question, should I be looking at Filemaker Server in order to properly manage the 5 locations all making changes to their own set of assets? I'm guessing with server the central database will be updated live whenever one of the field office makes a change? I'm thinking this is what I need to do.

                         Thanks again for your help.

                         Mat

                    • 7. Re: Two quick FMPro12 Database questions
                      Sorbsbuster

                           You don't need FM Server for 5 guests - you only need it for 10 or more.  There are other advantages to using Server, however, although I would tend to try serving the file to your 5 companies using your FM Client.  You will be able to sort out any other technical set-up issues then and see if your (and your cpmanies) bandwidths are adequate.

                           I think there is one more thing you should do to make sure that Excel will see the data correctly: when you set up the text import Excel will default to import that field as a number if it 'guesses' from a data sample that the data looks like numbers.  You will lose all letters and leading zeros, for example.  You should force Excel to read that column as type 'text'.