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.
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.
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.
"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).
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!
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.
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.
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'.